RE: PL/SQL question

[Djordje Jankovic]

Title: PL/SQL question

Hi Linda,

 

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 http://www.oracle.com/oramag/oracle/00-nov/index.html?o60sql.html).

 

Djordje

-----Original Message-----
From: Hagedorn, Linda [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 15, 2001 4:30 PM
To: Multiple recipients of list ORACLE-L
Subject: 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                           
  Select max(v_table_column) from v_table_owner.v_table_name ;

For the life of me I can't get this syntax right.  The parser is complaining because v_table_owner.v_table_name isn't declared.  I've tried || (concatenation), commas, single quotes, double quotes, colon, etc.  

If you can see the error, I'd be very happy for a reply. 

Thanks, Linda
echo
'[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc