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

Thanks Ed. See below.

On Feb 13, 9:39 pm, Ed Prochak <edproc..._at_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 reinsert  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. Received on Thu Feb 14 2008 - 02:54:21 CST