RE: PL/SQL question

From: Djordje Jankovic <djankovic_at_corp.attcanada.ca>
Date: Thu, 15 Nov 2001 14:14:18 -0800
Message-ID: <F001.003C6CD7.20011115140248@fatcity.com>

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="BORDER-LEFT: #0000ff 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px">   <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                            

    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 <FONT
  size=2>'[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc    Received on Thu Nov 15 2001 - 16:14:18 CST