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

On Feb 13, 9:37 pm, dana..._at_yahoo.com wrote:
> 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?

If you really want temp tables, have the DBA create some for you. The second question is written poorly. Why would you want to update the temp rows? ("those rows")
>
> 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.

If you want TEMP tables have the DBA create them. Talk to your DBA about GLOBAL TEMPORARY TABLES. Then you don't need CREATE TABLE privilege.

>
> 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.

That's true.

>
> Any other suggestions?

Well if you cannot get the DBA's help, then what about using a PL/SQL tables?

  HTH,
Ed Received on Wed Feb 13 2008 - 20:39:54 CST