CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Sapo121
Sapo121

Reputation: 71

obtaining columns dynamically from dynamic table

i have a two tables that i need to access one using cursor c1 and the other using cursor c2 , they both store columns containing table names that i need to access to perform operations like insert or delete.

CURSOR c1 IS
      SELECT ID_BCK_CFG_TAB,
             Nome_tab,
             pref_tab_bck,
             max_reg_bck,
             criado_em
        FROM sii_bck_cfg_tab
       WHERE desativado_em IS NULL 
          OR desativado_em<=SYSDATE
         AND n_dias_reten>0
    ORDER BY criado_em; 

CURSOR c2 IS 
    SELECT sii_bck_tab.ID_BCK_CFG_TAB,
           sii_bck_tab.nome_tab
      FROM sii_bck_tab,
           sii_bck_cfg_tab
     WHERE sii_bck_cfg_tab.id_bck_cfg_tab = sii_bck_tab.id_bck_cfg_tab
       AND dt_fecho is NULL;

when i loop through them i need to insert and delete certain rows, how can i dynamically get certain columns like for example criado_em from my cursor c1.

i need to perform operations like 

stmt_ins:= 'INSERT into ' || n_tab2 || ' SELECT * FROM ' || n_tab || ' where ''' || dt_criado  || '< :dt';

EXECUTE IMMEDIATE stmt_ins USING sysdate;

but i cant because i dont know how to return the variable dt_criado as a column value of criado_em from the dynamic table im consulting.

Any help would be much appreciated.

Upvotes: 1

Views: 63

Answers (1)

kara
kara

Reputation: 3455

open c1;

loop
    fetch c1 into tmp; -- get current row
    exit when c1%notfound; -- check if more rows exist

    IF tmp.myDateCol >= SYSDATE THEN 
        execute immediate 'INSERT INTO ' || tmp.colname  || ' VALUES (1,2)';
    END IF;
end loop;

Upvotes: 1

Related Questions