| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/SQL question
<FONT face="Courier New" color=#0000ff
size=2>Thanks very much! My use of EXECUTE IMMEDIATE is passing
the parser.
<FONT face="Courier New" color=#0000ff
size=2>
<FONT face="Courier New" color=#0000ff
size=2>Linda
<FONT face="Times New Roman"
size=2>-----Original Message-----From: Djordje Jankovic
[mailto:djankovic_at_corp.attcanada.ca]Sent: Thursday, November 15,
2001 2:03 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: PL/SQL question
Hi
Linda,
<SPAN
class=203485521-15112001>
You
cannot put a variable instead of an object name (where by object here I
mean owner, table_name, column_name). You have few options:
-
generate a sql hat you would run, e.g. do select 'select
max('||v_column_name||') from ' ||
v_owner||'.'||v_table_name||';'
-
use dbm_sql package, i.e. create the statement in a similar way as above and
run it;
-
use native dynamic sql (see for example <A
href="http://www.oracle.com/oramag/oracle/00-nov/index.html?o60sql.html">http://www.oracle.com/oramag/oracle/00-nov/index.html?o60sql.html).
<SPAN
class=203485521-15112001>
<SPAN
class=203485521-15112001>Djordje
<BLOCKQUOTE
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<FONT face=Tahoma
size=2>-----Original Message-----From: Hagedorn, Linda
[mailto:lindah_at_epocrates.com]Sent: Thursday, November 15, 2001
4:30 PMTo: Multiple recipients of list
ORACLE-LSubject: PL/SQL question
Can anyone can tell me how to use PL/SQL declared variables
in a select statement where a . has to be between the owner and table name,
and the owner and table name are variables, I'd be most appreciative.
The answer is probably obvious... Feel free to point it out.
On import, we occasionally have sequences that are out of
sync with the data. To remedy this, I'm creating a master table that
will match owner/table/column to owner/sequence, and a PL/SQL procedure that
will increment sequences which are found to be lower than the max value in
the associated owner/table/column.
Matching_Table:
Table_owner Table_name
Table_column Sequence_owner
Sequence_name Create_dt
Last_mod_dt
Pseudo code:
Declarations variables, output report file, counters.
Read dba_sequences in cursor Select
table_owner, table_name, table_column into v_table_owner, v_table_name,
v_table_column from matching_table a where sequence_owner = a.sequence_owner
and sequence_name = a.sequence_name ; (sequence_owner is from loop,
reading dba_sequences)
if row is found
then
 |
 |