PL/SQL Syntax... w/ Example 1

[cwinans]

Hey, I need to create a temporary table that is populated with the results of a SELECT statement.

Could anyone give me some sample or pseudo- code that accomplishes this?

It's for a stored procedure that I'm revamping for a project I am working on for my company's intranet. Thanks. I hope this helped! ;-)
- Casey Winans

 

[hacketta]

If it is not possible to use CREATE TABLE...AS SELECT then
use DBMS_SQL to create a table. The following procedure will execute a ddl statement as per the SQL string you pass it:

PROCEDURE any_ddl (sql_string IN VARCHAR2)
IS
c1 INTEGER;
BEGIN
c1 := dbms_sql.open_cursor;
dbms_sql.parse(c1, sql_string, dbms_sql.native);
dbms_sql.close_cursor(c1);
END;

You can then declare a cursor to SELECT the records you want and then INSERT them into the temporary table you created e.g.

DECLARE
CURSOR c1 IS
SELECT x, y, z
FROM mytable
WHERE mycolumn = 'A';
BEGIN
FOR c1_rec IN c1
LOOP
INSERT INTO temp_table(col_a,col_b, col_c)
VALUES (c1_rec.x, c1_rec.y, c1_rec.z);
END LOOP;

COMMIT;
END;

Hope this helps.