PLSQL: Insert data form one table in may envs to another table while keeping the from_table name dynamic

Question

Need to insert data into a table from a table present in different environments. The env list is obtained from my_env table. For my case the number of envs can vary so the env name would be dynamic hence i wrote as under:

DECLARE
WRK_STS VARCHAR2(2)    := 'PP';
MY_ENV VARCHAR2(50)   := '';
WRK_ENV NUMBER(6)      := 0;
BEGIN
  FOR t in (SELECT DISTINCT(envid) FROM MY_ENV ORDER BY       ENVID ASC)
  LOOP
  WRK_ENV := WRK_ENV+1;
  MY_ENV := 'ENV_' || t.envid || '.table002'; 
  INSERT INTO MYTAB101(DATE,STS, MYENV, nAME, DESCR, MYTYPE)
  SELECT null, ' || WRK_STS|| ',' || WRK_ENV || ',NAME, DESCR,MYTYPE from ' || MY_ENV;
  END LOOP;
EXCEPTION 
 /*Handle exception*/
END;
/
COMMIT;

But i am getting error :

ORA-06550: line 15, column 61: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 12, column 3: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: committed.

If i directly type the env name it works like 'from ENV368.table002'

can someone suggest what is wrong here?

Thanks!!!

Answer 1

Because there is underscore also in 'ENV_' string, have a look below:

MY_ENV := 'ENV_' || t.envid || '.table002'; 

I think for your case it should be:

MY_ENV := 'ENV' || t.envid || '.table002'; 

Answer 2

The main idea here is to use Dynamic SQL and EXECUTE Immediate to suffice your requirement. I have rectified your block a bit. I have not tested it as i dont have workspace with me. Hope it helps.

DECLARE
  WRK_STS VARCHAR2(2)  := 'PP';
  MY_ENV  VARCHAR2(50) := '';
  WRK_ENV NUMBER(6)    := 0;
TYPE ENV
IS
  TABLE OF VARCHAR2(100);
  env_tab env;
BEGIN
  EXECUTE IMMEDIATE 'SELECT DISTINCT(envid) FROM '||MY_ENV||'  ORDER BY ENVID' BULK COLLECT INTO env_tab;
  FOR i IN env_tab.FIRST..env_tab.LAST
  LOOP
    WRK_ENV := WRK_ENV+1;
    MY_ENV  := 'ENV_' || ENV_TAB(I) || '.table002';
    EXECUTE IMMEDIATE ' INSERT INTO MYTAB101      
     (date,STS, MYENV, nAME, DESCR, MYTYPE      
      )    
        SELECT NULL,      
        WRK_STS,      
        wrk_env,      
       ''NAME'',      
       ''DESCR'',      
       ''MYTYPE''    
FROM ' || MY_ENV;
  END LOOP;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  NULL; --when others should not be used
END;
/

Answer 3

Untested, but I would expect the general format to be something like this:

declare
    l_wrk_stats varchar2(2) := 'PP';
    l_my_env    varchar2(50);
    l_wrk_env   number(6) := 0;
    l_sql       varchar2(500);
begin
    for r in (
        select distinct envid
        from   my_env
        order by envid
    )
    loop
        l_wrk_env := l_wrk_env + 1;
        l_my_env  := 'ENV_' || r.envid || '.table002';
        l_sql :=
            'insert into mytab101 (date, sts, myenv, name, descr, mytype)' || chr(10) ||
            'select null, :b1, :b2, name, descr, mytype from ' || l_my_env;

        execute immediate l_sql using l_wrk_stats, l_wrk_env;
    end loop;

exception
    when others then
        raise_application_error(-20000, 'Command failed: ' || l_sql, true); 
end;