Declare Table Variable in Oracle Procedure

Question

I'm having a heck of a time trying to find an example of this being done. I have a procedure, and as part of that procedure I want to store the results of a SELECT statement so that I can work against that set, and then use it as a reference to update the original records when it's all done.

The difficulty I'm having is in declaring the temporary table variable. Here's an example of what I'm trying to do:

PROCEDURE my_procedure
IS
  output_text clob;
  temp_table IS TABLE OF MY_TABLE%ROWTYPE; -- Error on this line
BEGIN
  SELECT * BULK COLLECT INTO temp_table FROM MY_TABLE WHERE SOME_DATE IS NULL;

  -- Correlate results into the clob for sending to email (working)
  -- Set the SOME_DATE value of the original record set where record is in temp_table

I get an error on the second occurrence of IS, saying that it is an unexpected symbol. This suggests to me that my table variable declaration is either wrong, or in the wrong place. I've tried putting it into a DECLARE block after BEGIN, but I just get another error.

Where should this declaration go? Alternatively, if there is a better solution I'll take that too!

Answer 1

CREATE OR REPLACE PROCEDURE PROCEDURE1 AS 
  output_text clob;
  type temp_table_type IS TABLE OF MY_TABLE%ROWTYPE; 
  temp_table temp_table_type;
BEGIN
  SELECT * BULK COLLECT INTO temp_table FROM MY_TABLE;
END PROCEDURE1;

or

CREATE OR REPLACE PROCEDURE PROCEDURE1  ( output_text OUT clob ) IS 
  type temp_table_type IS TABLE OF MY_TABLE%ROWTYPE
    INDEX BY BINARY_INTEGER; 
  temp_table temp_table_type;
BEGIN
  SELECT * BULK COLLECT INTO temp_table FROM MY_TABLE;

  FOR indx IN 1 .. temp_table.COUNT 
   LOOP
       something := temp_table(indx).col_name;
   END LOOP;


END PROCEDURE1;

Answer 2

I had a similiar problem and found this: Selecting Values from Oracle Table Variable / Array?

The global temporary table can be used like a regular table, but its content is only temporary (deleted at end of session/transaction) and each session has its own table content. If you don't need dynamic SQL this can be used as good solution:

CREATE GLOBAL TEMPORARY TABLE temp_table
   (
       column1   NUMBER,
       column2   NUMBER
   )
   ON COMMIT DELETE ROWS;

PROCEDURE my_procedure
IS
  output_text clob;
BEGIN
  -- Clear temporary table for this session (to be sure)
  DELETE FROM temp_table;

  -- Insert data into temporary table (only for this session)
  INSERT INTO temp_table SELECT * FROM MY_TABLE WHERE SOME_DATE IS NULL;

  -- ...
END;

The only disadvantages are, in my opinion, that you got another table and that the temporary table is not dynamic.