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

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Thu, 14 Feb 2008 20:44:31 +0100
Message-ID: <fp25h8$ice$1@online.de>

dananrg_at_yahoo.com schrieb:
> 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?

Why not just insert the records and delete the old ones afterwards?

SQL> create table parent( x number(10,0) primary key, remark varchar2(20)); SQL> create table child( y number(10,0) primary key, x number(10,0), remark varchar2(20));
SQL> alter table child add constraint fkcp foreign key (x) references parent(x);

SQL>
SQL> insert into parent values(1, 'No. two');
SQL> insert into child values(5, 1, 'No. five ref No. two');
SQL> commit;
SQL> select * from parent, child where child.x = parent.x;

          X REMARK                        Y          X REMARK
---------- -------------------- ---------- ---------- --------------------
          1 No. two                       5          1 No. five ref No. two
SQL>
SQL> -- Insert and delete rather than update
SQL> insert into parent select x + 1, remark from parent where x = 1;
SQL> update child set x = x + 1 where x = 1;
SQL> delete from parent where x = 1;

SQL> commit;
SQL> select * from parent, child where child.x = parent.x;

          X REMARK                        Y          X REMARK
---------- -------------------- ---------- ---------- --------------------
          2 No. two                       5          2 No. five ref No. two

But if you insist in temporary tables - you already have them: the rollback segment:

SQL> -- Delete, then insert from rollback segment SQL> declare

   2     cursor cs
   3     is
   4     select p.x as p_x, p.remark as p_rem, c.y as c_y, c.x as c_x, 
c.remark as c_rem
   5       from parent p, child c
   6      where c.x = p.x;
   7
   8     csRec cs%rowtype;
   9  begin
  10     open cs;
  11     delete from child;
  12     delete from parent; -- Do not commit here!
  13
  14     <<loop_cs>>
  15     loop
  16        fetch cs into csRec;
  17        exit loop_cs when cs%notfound;
  18
  19        csRec.p_x := csRec.p_x * 10; -- change primary key col
  20        csRec.c_x := csRec.c_x * 10;
  21
  22        insert into parent
  23        values(csRec.p_x, csRec.p_rem);
  24
  25        insert into child
  26        values(csRec.c_y, csRec.c_x, csRec.c_rem);
  27     end loop loop_cs;

  28 end;
  29 /
SQL>
SQL> select * from parent, child where child.x = parent.x;

          X REMARK                        Y          X REMARK
---------- -------------------- ---------- ---------- --------------------
         20 No. two                       5         20 No. five ref No. two

HtH
Urs Metzger Received on Thu Feb 14 2008 - 13:44:31 CST