1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

EXECUTE IMMEDIATE with Bulk collect

Discussion in 'SQL PL/SQL' started by Angappan, Aug 27, 2009.

  1. Angappan

    Angappan Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    I would like to use a bulk collect in the EXECUTE IMMEDIATE statement. Cursor doesn't supports EXECUTE IMMEDIATE. so I thought it shall be better.
    But getting error here.

    create or replace procedure "OASIS".bulk_sample(source_schema VARCHAR2,sourcetable VARCHAR2,day1 VARCHAR2,month1 VARCHAR2,year1 VARCHAR2)

    AS
    TYPE date_string is TABLE of VARCHAR(1000);
    date_obj date_string;
    BEGIN

    EXECUTE IMMEDIATE 'SELECT to_char('''||day1||'-'||month1||'-'||year1||''') bulk collect into date_obj FROM '||source_schema||'.'|| sourcetable ||' ';

    for indx in date_obj.first..date_obj.last loop
    dbms_output.put_line(
    date_obj(indx));
    end loop;

    DBMS_OUTPUT.PUT_LINE('Sample output');
    END;




    Error:

    SQL> execute bulk_sample('OASIS','TEST_TABLE','DAY','MONTH','YEAR');
    BEGIN bulk_sample('OASIS','TEST_TABLE','DAY','MONTH','YEAR'); END;

    *
    ERROR at line 1:
    ORA-03001: unimplemented feature
    ORA-06512: at "OASIS.BULK_SAMPLE", line 8
    ORA-06512: at line 1




    I want to store the dates into bulk collect and i shall use it for further operations with loop.
    Above is the sample try for bulk operations.

    My scenario is to archive records at only once in month ( sysdate-30). In my table i have date as hr,day,month and year as separate columns.

    procedure parameters are ( source schema,stable,target,schema,targettable,no_of_days)

    how can I use the date fields with sysdate. and how can I store here in a bulk collect.
    :):)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Bulk collect is a feature of EXECUTE IMMEDIATE, not the query string submitted to it. Your statement should written as shown in the example below:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE bulk_sample(source_schema VARCHAR2,sourcetable VARCHAR2,day1 VARCHAR2,month1 VARCHAR2,year1 VARCHAR2)
      2
      3  AS
      4   TYPE date_string IS TABLE OF VARCHAR(1000);
      5      date_obj date_string;
      6  BEGIN
      7
      8      EXECUTE IMMEDIATE 'SELECT to_char('''||day1||'-'||month1||'-'||year1||''')  FROM  '||source_schema||'.'|| sourcetable ||'' bulk collect INTO
    date_obj;
      9
     10                   FOR indx IN date_obj.FIRST..date_obj.LAST loop
     11                           dbms_output.put_line(
     12                          date_obj(indx));
     13                 END loop;
     14
     15    DBMS_OUTPUT.PUT_LINE('Sample output');
     16  END;
     17
     18  /

    PROCEDURE created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> EXEC bulk_sample('BING','EMP','31','03','2009')
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    31-03-2009
    Sample output

    PL/SQL PROCEDURE successfully completed.

    SQL>
    I do not understand your 'sysdate' question. Please clarify.
     
  3. Angappan

    Angappan Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Yes. Very thanks. I got it. I have another one doubt here, its little slow when using bulk collections.

    I have a logic error in my code,here i have to archive the records if older than 30days from production to storage.

    ---- If the records doesn't match the condition no need to enter the loop.

    ---- can i directly store the records matching date<=sysdate -30 condition into bulk.

    Main logic problem storing all the records and deleting all the records.......

    Code:


    create or replace procedure archive_data (source_schema VARCHAR2, source_table VARCHAR2, target_schema VARCHAR2,
    target_table VARCHAR2,no_of_days NUMBER) is

    status VARCHAR2(50);
    number_of_rows NUMBER;

    TYPE date_string IS TABLE OF VARCHAR (1000);
    date_obj date_string;

    BEGIN

    status:='before Insertion';

    EXECUTE IMMEDIATE 'SELECT to_char(day)|| to_char(''-'') || to_char(month) || to_char(''-'') || to_char(year) FROM '|| source_schema|| '.' ||source_table
    BULK COLLECT INTO date_obj;


    FOR indx IN date_obj.FIRST .. date_obj.LAST

    LOOP
    EXECUTE IMMEDIATE 'INSERT INTO '||target_schema||'. '|| target_table || '
    (SELECT * FROM '||source_schema||'.'|| source_table ||' WHERE (to_date('''||date_obj(indx)||''',''DD-MM-YYYY'')) <= sysdate - '||no_of_days|| ')';

    EXECUTE IMMEDIATE 'DELETE FROM '||source_schema||'.' ||source_table|| ' WHERE (to_date('''||date_obj(indx)||''',''DD-MM-YYYY'')) <= sysdate -'||no_of_days ;

    DBMS_OUTPUT.put_line (date_obj (indx));

    COMMIT;

    END LOOP;


    END archive_data;



    sample data

    create stable(day number,month number,year number);
    insert into stable values(09,09,2009);
    insert into stable values(09,08,2009);
    insert into stable values(09,07,2009);
    create ttable(day number,month number,year number);
    commit;


    I want only records older than 30days from today to be moved to storage and remove it from production.
    If no records in matching condition no need to go in looping.
    schema and tablename and no_of_days (30 or 60) to be dynamic.
    In my table records will be in day month and year format separately.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I would stop where you are because attempting to write some 'universal' archiving procedure is going to produce performance problems. You need to decide which tables need to be archived and write specific code to perform that function. This code you've written will not scale, it won't perform well under load and it's simply an effort in futility. A simple BEFORE DELETE trigger on each table, along with an additional archive table for each base table which needs archiving, makes this so much siimpler:


    Code (SQL):
    SQL>
    SQL> --
    SQL> -- Create test table
    SQL> --
    SQL> CREATE TABLE test(
      2          smyrtle NUMBER,
      3          quarnop varchar2(40),
      4          yingpo varchar2(8),
      5          znurp   NUMBER,
      6          rooptar DATE
      7  );

    TABLE created.

    SQL>
    SQL> CREATE INDEX test_rooptar_idx
      2  ON test(rooptar);

    INDEX created.

    SQL>
    SQL> --
    SQL> -- Create archive table
    SQL> --
    SQL> CREATE TABLE test_archive(
      2          smyrtle NUMBER,
      3          quarnop varchar2(40),
      4          yingpo varchar2(8),
      5          znurp   NUMBER,
      6          rooptar DATE,
      7          arch_dt DATE,
      8          arch_user varchar2(35)
      9  );

    TABLE created.

    SQL>
    SQL> --
    SQL> -- Create trigger to populate archive table
    SQL> -- when records are deleted from source
    SQL> --
    SQL> CREATE OR REPLACE TRIGGER arch_test_trg
      2  BEFORE DELETE ON test
      3  FOR each ROW
      4  BEGIN
      5          INSERT INTO test_archive
      6          (smyrtle,
      7          quarnop,
      8          yingpo,
      9          znurp,
     10          rooptar,
     11          arch_dt,
     12          arch_user)
     13          VALUES
     14          (:OLD.smyrtle,
     15          :OLD.quarnop,
     16          :OLD.yingpo,
     17          :OLD.znurp,
     18          :OLD.rooptar,
     19          sysdate,
     20          sys_context('USERENV','CURRENT_USER'));
     21  END;
     22  /

    TRIGGER created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> --
    SQL> -- Populate test table
    SQL> --
    SQL> BEGIN
      2          FOR i IN 1..100000 loop
      3              INSERT INTO test
      4              VALUES(i, 'Record '||i, 'Yertz'||MOD(i, 43), MOD(i, 79), sysdate - MOD(i,87));
      5          END loop;
      6
      7          commit;
      8
      9  END;
     10  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> SET linesize 180
    SQL>
    SQL> --
    SQL> -- Total test records
    SQL> --
    SQL> SELECT COUNT(*)
      2  FROM test;

      COUNT(*)
    ----------
        100000

    SQL>
    SQL> --
    SQL> -- Total archived records
    SQL> --
    SQL> SELECT COUNT(*)
      2  FROM test_archive;

      COUNT(*)
    ----------
             0

    SQL>
    SQL> --
    SQL> -- Clean up the test table
    SQL> --
    SQL> -- Trigger automatically archives deleted data
    SQL> --
    SQL> SET timing ON
    SQL>
    SQL> DELETE FROM test
      2  WHERE rooptar <= sysdate - 30;

    65501 ROWS deleted.

     
    Elapsed: 00:00:06.81
    Code (SQL):

    SQL>
    SQL> commit;

    Commit complete.

    Elapsed: 00:00:00.03
    SQL>
    SQL> SET timing off
    SQL>
    SQL> --
    SQL> -- Test record count
    SQL> --
    SQL> SELECT COUNT(*)
      2  FROM test;

      COUNT(*)
    ----------
         34499

    SQL>
    SQL> --
    SQL> -- Archived record count
    SQL> --
    SQL> SELECT COUNT(*)
      2  FROM test_archive;

      COUNT(*)
    ----------
         65501

    SQL>
    SQL> --
    SQL> -- Sampl of archived data
    SQL> --
    SQL> SELECT *
      2  FROM test_archive
      3  WHERE rownum < 20;

       SMYRTLE QUARNOP                                  YINGPO        ZNURP ROOPTAR              ARCH_DT              ARCH_USER
    ---------- ---------------------------------------- -------- ---------- -------------------- -------------------- -----------------------------------
            30 Record 30                                Yertz30          30 28-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            31 Record 31                                Yertz31          31 27-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            32 Record 32                                Yertz32          32 26-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            33 Record 33                                Yertz33          33 25-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            34 Record 34                                Yertz34          34 24-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            35 Record 35                                Yertz35          35 23-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            36 Record 36                                Yertz36          36 22-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            37 Record 37                                Yertz37          37 21-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            38 Record 38                                Yertz38          38 20-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            39 Record 39                                Yertz39          39 19-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            40 Record 40                                Yertz40          40 18-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING

       SMYRTLE QUARNOP                                  YINGPO        ZNURP ROOPTAR              ARCH_DT              ARCH_USER
    ---------- ---------------------------------------- -------- ---------- -------------------- -------------------- -----------------------------------
            41 Record 41                                Yertz41          41 17-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            42 Record 42                                Yertz42          42 16-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            43 Record 43                                Yertz0           43 15-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            44 Record 44                                Yertz1           44 14-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            45 Record 45                                Yertz2           45 13-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            46 Record 46                                Yertz3           46 12-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            47 Record 47                                Yertz4           47 11-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING
            48 Record 48                                Yertz5           48 10-JUL-2009 08:48:42 27-AUG-2009 08:48:50 BING

    19 ROWS selected.

    SQL>
     
    Note that in less than seven seconds the trigger archived over 65,000 records.