Temporary tables in memory using PL/SQL (as a workaround for not having CREATE TABLE privs)?

From: <dananrg_at_yahoo.com>
Date: Wed, 13 Feb 2008 13:37:33 -0800 (PST)
Message-ID: <bdb83b6b-c523-4342-9982-476b641067d8@s37g2000prg.googlegroups.com>

Is there any way of creating temporary tables (in memory) using PL/SQL (Oracle 9i)? Then update values in those rows from those in-memory temp tables for re-insertion into schema tables?

On a particular schema, I don't have CREATE TABLE (to make my own temp tables using CTSA) or ALTER TABLE (to temporarily disable referential integrity constraints) system privileges. I do have SELECT, INSERT, UPDATE, and DELETE object privileges on the tables I need to manipulate.

I need to delete records from various child tables (enabling me to update a record in a parent table), first copying those records to memory so I don't lose them. Then I need to update a value in those records and re-insert them into schema tables.

Is there some simple way to do this in PL/SQL that I'm missing? One way to do it would be to open a cursor(s), write INSERT records (with a few values updated, being reflected in the INSERT statements), to a text file and run that separately. Seems like a lousy way to do it though.

Any other suggestions? Received on Wed Feb 13 2008 - 15:37:33 CST

Original text of this message