Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    8

    Unanswered: What's wrong with this procedure?

    Hello again, i'm trying tu create and run a procedure in pl/sql and i'm getting some errors that can resolve. I need to create a procedure wich fetch all tables of the schema and then insert the data of each table into a global data table wich contains all the data of the other ones.

    CREATE OR REPLACE PROCEDURE DM_DATA IS
    CURSOR all_tables IS
    SELECT *
    FROM all_tables t
    WHERE t.owner ='DM' AND table_name like 'RESP_TEST_%';
    BEGIN
    LOOP
    INSERT INTO DM.all_broadcast_responses SELECT * FROM TABLE DM.RESP_TEST_01;

    END LOOP;

    EXCEPTION
    WHEN OTHERS THEN ROLLBACK;

    END DM_DATA;

    ----
    And i'm getting: Error(9,6): PL/SQL: SQL Statement ignored
    Error(9,70): PL/SQL: ORA-00906: left parentesis missing

    What's wrong? I'm in the right way?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What's wrong?
    Invalid syntax.
    What exactly are you LOOPing on?
    When does LOOP terminate?
    Solution can be done with only plain SQL & no PL/SQL

    >I'm in the right way?
    No
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2012
    Posts
    8
    OK, i understand here is the problem:

    FOR rec IN all_tables
    LOOP
    INSERT INTO all_broadcast_responses SELECT * FROM TABLE (how can i refer to the table i'm on?);

    END LOOP;


    How can you do with plain sql? Can you give me an example or guide me?

    Thankyou very much

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    by the way the design is poor, unprofessional, & not normalized.
    You should NEVER have multiple tables with exact same columns!

    spool load_abr.sql
    SELECT 'INSERT INTO DM.all_broadcast_responses SELECT * FROM ' || table_name || ';'
    FROM all_tables t
    WHERE t.owner ='DM' AND table_name like 'RESP_TEST_%';
    spool off
    @load_abr.sql
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2012
    Posts
    8
    I know, not my design or rules. I'm just a newbie at oracle creating an etl. Thanks for the reply, i'll try to run this.

  6. #6
    Join Date
    Jan 2012
    Posts
    8
    Works very well.
    Thanks!!!

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    this technique is called "writing SQL to write SQL"
    It comes in quite handy at times!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •