From lindah@epocrates.com Thu, 15 Nov 2001 14:29:19 -0800 From: "Hagedorn, Linda" Date: Thu, 15 Nov 2001 14:29:19 -0800 Subject: RE: PL/SQL question Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: PL/SQL question Thanks very much!  My use of EXECUTE IMMEDIATE is passing the parser.        Linda -----Original Message-----From: Djordje Jankovic [mailto:djankovic@corp.attcanada.ca]Sent: Thursday, November 15, 2001 2:03 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 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:lindah@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 '[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc