Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)?

#1

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?

#2

On Feb 13, 9:37 pm, dana... (AT) yahoo (DOT) com wrote:

Quote:

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")

Quote:

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.

Quote:

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.

Quote:

Any other suggestions?

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

HTH,
Ed

#3

On Feb 13, 9:37 pm, dana... (AT) yahoo (DOT) com wrote:

Quote:

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")

Quote:

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.

Quote:

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.

Quote:

Any other suggestions?

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

HTH,
Ed

#4

On Feb 13, 9:37 pm, dana... (AT) yahoo (DOT) com wrote:

Quote:

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")

Quote:

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.

Quote:

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.

Quote:

Any other suggestions?

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

HTH,
Ed

#5

On Feb 13, 9:37 pm, dana... (AT) yahoo (DOT) com wrote:

Quote:

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")

Quote:

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.

Quote:

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.

Quote:

Any other suggestions?

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

HTH,
Ed

#6

Thanks Ed. See below.

On Feb 13, 9:39 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

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.

#7

Thanks Ed. See below.

On Feb 13, 9:39 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

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.

#8

Thanks Ed. See below.

On Feb 13, 9:39 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

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.

#9

Thanks Ed. See below.

On Feb 13, 9:39 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

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.

#10

Are you sure PL/SQL Tables (now known as collections?) will do the
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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)?

comp.databases.oracle.misc comp.databases.oracle.misc

Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-13-2008 , 03:37 PM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-13-2008 , 08:39 PM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-13-2008 , 08:39 PM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-13-2008 , 08:39 PM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-13-2008 , 08:39 PM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-14-2008 , 02:54 AM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-14-2008 , 02:54 AM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-14-2008 , 02:54 AM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-14-2008 , 02:54 AM

Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? - 02-14-2008 , 08:58 AM