Temporary tables in memory using PL/SQL (as a workaround for not having CREATE TABLE privs)?
Date: Wed, 13 Feb 2008 13:37:33 -0800 (PST)
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