CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqltemp-tables
neee
neee

Reputation: 33

Oracle stored procedure using temp tables

The main idea: I want create 3 tables (as temp tables) with some selects, take 1 and 2 and compare.

Result insert to 3 table.

My procedure looks like:

create or replace PROCEDURE CHECK_PARAMS
AS 
TYPE row IS RECORD (id NUMBER,         
                    count_id NUMBER ); 
TYPE my_table_type IS TABLE OF row;

first my_table_type;
second my_table_type;
third my_table_type;    
BEGIN
  SELECT id, count_id bulk collect -- GOOD SELECT
  INTO first
  FROM 
    (SELECT test1.A_ID id, 
            COUNT(test1.A_ID) count_id
     FROM MY_OTHER_TABLE1 test1, 
          MY_OTHER_TABLE2 test2
     WHERE test1.A_ID = test2.A_ID
     GROUP BY test1.A_ID);

  SELECT id, count_id bulk collect -- GOOD SELECT
  INTO first
  FROM 
    (SELECT test1.A_ID id, 
            COUNT(test1.A_ID) count_id
     FROM MY_OTHER_TABLE3 test1, 
          MY_OTHER_TABLE4 test2
     WHERE test1.A_ID = test2.A_ID
     GROUP BY test1.A_ID);

  SELECT id, count_id bulk collect -- ORACLE CAN'T FIND MY TEMP TABLES
  INTO third
  FROM frist, second; -- **HERE I GOT ERROR: ORA-00942 table or view does not exist**

What's wrong?

Upvotes: 1

Views: 105

Answers (1)

a.j. tawleed
a.j. tawleed

Reputation: 904

You have to define the types outside of the procedure

create TYPE t_row as object  (id NUMBER,         
                    count_id NUMBER ); 

create TYPE my_table_type IS TABLE OF t_row;

and than you have to use table

SELECT id, count_id bulk collect -- ORACLE CAN'T FIND MY TEMP TABLES
  INTO third
  FROM table(first), table(second);

Upvotes: 1

Related Questions