Temporary tables in memory using PL/SQL (as a workaround for not having CREATE TABLE privs)?
dan...@yahoo.com
(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?
Ed Prochak
> 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
dan...@yahoo.com
On Feb 13, 9:39 pm, Ed Prochak <edproc...@gmail.com> wrote:
> If you really want temptables, have the DBAcreatesome for you.
> The second question is written poorly. Why would you want to update
> the temp rows? ("those rows")
The problem is a mistakenly entered record in a parent table, upon
which many child tables depend on for referential integrity. The
"quick" fix, given the circumstances, would be to disable the
particular RI constraints, update the columns for the records in the
child tables, then update the parent record in the parent table. But I
can't disable the constraints.
I would update the temp rows first before re-insertion, because I
would have created them using something like a CREATE TABLE AS SELECT
statement to retrieve them. Update to get the values I want, then re-
insert into the child tables. Unless there's a better way of doing it.
I'm assuming no DBA intervention--the DBAs are busy and I don't want
to bug them. Besides, depending on the data fix I need to perform,
there may be 8 child table dependents, or 8 + any number of grandchild
dependent tables. It all depends on the data issue. With something
like 50+ tables in the schema, I'm not sure I want to ask to create
50+ temp tables. Would rather do what I need to do dynamically.
> > On a particular schema, I don't haveCREATETABLE(to make my own temp
> >tablesusingCTSA) or ALTERTABLE(to temporarily disable referential
> > integrity constraints) system privileges. I do have SELECT, INSERT,
> > UPDATE, and DELETE object privileges on thetablesI need to
> > manipulate.
>
> If you want TEMPtableshave the DBAcreatethem. Talk to your DBA
> about GLOBALTEMPORARYTABLES. Then you don't needCREATETABLE
> privilege.
> > I need to delete records from various childtables(enabling me to
> > update a record in a parenttable), first copying those records to
> >memoryso I don't lose them. Then I need to update a value in those
> > records and re-insert them into schematables.
>
> Well if you cannot get the DBA's help, then what aboutusingaPL/SQLtables?
Aren't PL/SQL tables a 10g feature? I'm using 9i. Migrating to 10g in
the next few months, but that doesn't help me today.
Thanks for the suggestions.
dan...@yahoo.com
job? I thought collections were single-value lists, indexed by an
integer, or key-value pairs.
Imagine I have a table with 50 columns (don't imagine whether that's
good or bad, just imagine it exists and I need to use it). A single-
column collection won't work. Cursoring through rows to generate
INSERT statements, having to specify vcur_TABLE.ROW for each of the 50
columns in a DBMS_OUTPUT.PUT_LINE or UTL_FILE would be tedious.
Or am I wrong about PL/SQL tables/collections in Oracle 9i? Are they
not merely lists / associative arrays?
Urs Metzger
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
dan...@yahoo.com
> Why not just insert the records and delete the old ones afterwards?
Thanks Urs. Some of the columns in the child tables I need to update
are part of composite primary keys. In your first example, you update
a foreign key value.
For some reason, I thought it wasn't possible to update values that
were part of a primary key. I didn't think it was allowed. Using
JavaBeans, I've been told, it isn't allowed. Is it bad practice to
update values that are part of a primary key? Was it ever not allowed
in any version of Oracle? Not sure where I got that idea from, but
your first example works fine.
Anyone care to comment on the goodness or badness of updating values
in a composite primary key rather than making copies of rows, deleting
the old rows, then re-inserting updated versions of the copied rows?
I did come up with a way of doing what I needed using "collections of
records" in PL/SQL before I saw your reply. That seems to work. I have
not tried your example #2 yet. I can post it if anyone is interested.
Urs Metzger
> On Feb 14, 2:44 pm, Urs Metzger <u...@ursmetzger.de> wrote:
>> Why not just insert the records and delete the old ones afterwards?
>
> Thanks Urs. Some of the columns in the child tables I need to update
> are part of composite primary keys. In your first example, you update
> a foreign key value.
>
> For some reason, I thought it wasn't possible to update values that
> were part of a primary key. I didn't think it was allowed. Using
> JavaBeans, I've been told, it isn't allowed. Is it bad practice to
> update values that are part of a primary key? Was it ever not allowed
> in any version of Oracle? Not sure where I got that idea from, but
> your first example works fine.
>
composite or not) and foreign key values if you don't violate
constraints. You can
- update a primary key, if it has no children
- update a foreign key, if it's new parents are there. That's what I
did in my firts example.
There is yet another way to update primary keys and foreign keys in
one transaction: deferrable constraints, which means that integrity will
be checked only at commit time. Search the manuals...
Hth Urs Metzger