Cursor loop example 1: until end of table

From Robs_Wiki
Revision as of 16:18, 5 March 2020 by Qadmin wiki (talk | contribs) (Example 3: create public synonyms in a loop)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Example 1 : create copy tables

set serveroutput on

DECLARE

sql_stmt VARCHAR2(200);
v_errm VARCHAR2(255);
v_scn NUMBER;

-- the cursor below defines the set of tables that will be copied
CURSOR cursor1 is select table_name from ggadmin.gg_tbl_dl_sources_rob order by table_name;

BEGIN

-- set the default tablespace for user ggadmin to the temporary store
execute immediate 'alter user ggadmin default tablespace gg_temp_store';

-- get the current scn
select current_scn into v_scn from v_$database;
-- dbms_output.put_line(v_scn);

for c in cursor1
  loop
     begin
        -- create a copy of the table
        sql_stmt := 'create table GGADMIN.FB_'||c.table_name||' parallel 8 as select /*+parallel('||c.table_name||' 8) */  * from GGADMIN.'||c.table_name||' as of scn '||v_scn;
        -- DBMS_OUTPUT.PUT_LINE(sql_stmt);

        execute immediate sql_stmt;

     exception
     when others then
        v_errm := SUBSTR(SQLERRM, 1, 255);
        -- insert into ggadmin.gg_errors (error_date, sql_stmt, sql_errm, table_name) values (sysdate, sql_stmt, v_errm, c.table_name);
        -- commit;
        DBMS_OUTPUT.PUT_LINE('Error with table '||c.table_name||' review logfile ggadmin.gg_errors for details');

     end;
  end loop;

-- reset the default tablespace for user ggadmin
execute immediate 'alter user ggadmin default tablespace gg_data';
END;
/

Example 2 delete all database links and triggers in a loop

BEGIN
for x in (select * from dba_db_links)
  loop
     -- execute immediate 'drop database link '||x.owner||'.'||x.db_link;
         -- to avoid error ORA-02024: database link not found run the statement below instead:
         delete sys.link$ where name=x.db_link;
         commit;
   end loop;
END;
/

BEGIN
for x in (select * from dba_triggers where owner = 'IBIS')
  loop
     execute immediate 'drop trigger IBIS.'||x.trigger_name;
end loop;
END;
/

BEGIN
for x in (select table_name from dba_tables where owner = 'IBIS' and table_name like '%AUDIT%')
  loop
     execute immediate 'truncate table IBIS.'||x.table_name;
end loop;
END;
/

Example 3: create public synonyms in a loop

-- this example expects a table (pilot_tables) with table_name and owner columns
declare
   var_statement varchar2(256);
begin
   for i in (select table_name, owner from prd_user.pilot_tables)
   loop
     var_statement := 'create or replace synonym robla.'||i.table_name||' for '||i.owner||'.'||i.table_name;
     -- dbms_output.put_line(i.owner||'.'||i.table_name);
     dbms_output.put_line(var_statement);
     execute immediate var_statement;
   end loop;
 end;
/

PL/SQL procedure successfully completed.

Example 4: drop tables in a loop

spool /home/oracle/dbadir/rob/magweghoor/drop_OUTBOUND.log
DECLARE
  var_statement VARCHAR2(256);
  var_records NUMBER;
  CURSOR cur
  IS
    SELECT table_name from dba_tables where table_name like 'OUT_%' and table_name not like 'OUTBOUND%' and owner = 'PROD';
BEGIN
 FOR i IN cur
  LOOP
    var_statement := 'drop table PROD.'||i.table_name||' cascade constraints';
    dbms_output.put_line(var_statement);
    execute immediate var_statement;
  END LOOP;
END;
/
spool off