Temporary table processing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Temporary table processing

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Temporary table processing

    Good day,
    Stuck here...

    Why does this work

    set serveroutput on
    declare my_count INT;
    begin
    select count(table_name) INTO MY_COUNT from all_tables where table_name = 'MY_TABLE';
    dbms_output.put_line(my_count);
    IF my_count = 0 then
    execute immediate ('create global temporary table my_table(name varchar2(10))');
    END IF;
    end;
    /

    BUT then I try and populate the tempoary table from the same procedure i get errors like below

    SQL> set serveroutput on
    SQL> declare my_count INT;
    2 begin
    3 select count(table_name) INTO MY_COUNT from all_tables where table_name = 'MY_TABLE';
    4 dbms_output.put_line(my_count);
    5 IF my_count = 0 then
    6 execute immediate ('create global temporary table my_table(name varchar2(10))');
    7 commit;
    8 END IF;
    9 insert into my_table values ('Steve');
    10 insert into my_table values ('Mike');
    11 insert into my_table values ('Gary');
    12 FOR i in (select name from my_table)
    13 loop
    14 dbms_output.put_line(i.name);
    15 end loop;
    16 end;
    17 /
    insert into my_table values ('Steve');
    *
    ERROR at line 9:
    ORA-06550: line 9, column 13:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 9, column 1:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 10, column 13:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 10, column 1:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 11, column 13:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 11, column 1:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 12, column 27:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 12, column 10:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 14, column 22:
    PLS-00364: loop index variable 'I' use is invalid
    ORA-06550: line 14, column 1:
    PL/SQL: Statement ignored


    Thanks

    Steve
    I'm stmontgo and I approve of this message

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The PL/SQL is being parsed, and is failing because at the time of parsing the temporary table does not exist ... to get this to work you'll either have to pre-create the temp table prior to the insert statements being parsed, or make the sql statements dynamic so that they are not parsed until execution time.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2001
    Posts
    267
    I think you are going out of scope when you are using tmp table..
    Raghu

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Have you thought about using PL/SQL collections instead?

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    thls dave for the explanation and gandolf989 for the suggestion

    regards

    steve
    I'm stmontgo and I approve of this message

Posting Permissions

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



Click Here to Expand Forum to Full Width