How to use dynamic table of every month in the insert statement while one static value we are passing in one column

Question

In one Stored proc, I used insert into statement i declare one variable as v_tbl_name which is dynamic. I am going to use this dynamic table in insert into block while we are passing one static value in one column but getting error. Here is the sample code-

declare
v_tbl_name varchar2(5) := NULL;
v_gen_tbl constant varchar2(50) := 'arch_tbl_mon';   --table name
v_sql varchar2(10000);

begin
 v_tbl_name := v_gen_tbl || '_' ||  TO_CHAR(SYSDATE, 'MON');
v_sql := 'insert into tbl                 --- another table going to insert
 (select ''abc'' as col1, ........
  from '||v_tbl_name||');'
execute immediate v_sql;
commit;
end;

Here 'abc' static value as column. After executing the above code, It is generating an err like Encountered the symbol "Execute".

Answer 1

Do it this way:

declare
v_tbl_name varchar2(5) := NULL;
v_gen_tbl constant varchar2(50) := 'arch_tbl_mon';   --table name
v_sql varchar2(1000);
begin
 v_tbl_name := v_gen_tbl || '_' ||  TO_CHAR(SYSDATE, 'MON');
 v_sql:= 'insert into tbl                 --- another table going to insert
  select 'abc' as col1, ........
  from  '||v_tbl_name;
  execute immediate v_sql;
  commit;
end;

Demo:

declare
v_tbl_name varchar2(5) := NULL;
v_gen_tbl constant varchar2(50) := 'arch_tbl_mon';   --table 
v_sql varchar2(1000);
begin

 v_tbl_name := 'DEPT';
 v_sql :='insert into tbl   select *  from '||v_tbl_name;
 execute immediate v_sql;  
end;

Exec:

SQL> select * from tbl;
     DEPTNO DNAME                LOC
     ---------- -------------------- --------------------
        1      XXX                 YYY

Edit:

Here 'abc' static value as column so that's why not able to execute the stored proc. It is generating an err like Encountered the symbol ' abc '.

See below demo:

Table tbl is being populated with static value abc from select statement:

SQL> select * from tbl;
    DEPTNO DNAME                LOC                  B
---------- -------------------- -------------------- ---

After Execution:

 declare
    v_tbl_name varchar2(5) := NULL;
    v_gen_tbl constant varchar2(50) := 'arch_tbl_mon';   --table 
    v_sql varchar2(1000);
    begin

     v_tbl_name := 'DEPT';
     v_sql :='insert into tbl (deptno,dname,loc,b) select a.*, ''abc''  from '||v_tbl_name||' a'; 
     execute immediate v_sql;  
     Commit;
    end;

OUTPUT

SQL> SELECT * FROM TBL;
    DEPTNO DNAME                LOC                  B
---------- -------------------- -------------------- ---
       100 Executive            USA                  abc

Final Edit:

You didnot follow what I showed to you hence you landed into the problem. Follow my inline comments. Please replace the column name where mentioned and it would be resolved.

declare
v_tbl_name varchar2(5) := NULL;
v_gen_tbl constant varchar2(50) := 'arch_tbl_mon';   --table name
v_sql varchar2(10000);

begin
 v_tbl_name := v_gen_tbl || '_' ||  TO_CHAR(SYSDATE, 'MON');
 v_sql := 'insert into tbl(col1,.....<all columns>)                 --- another table going to insert
  select ''abc'' as col1, a.col2........<other columns a.<columns>
  from '||v_tbl_name||' a';
execute immediate v_sql;
commit;
end;