To make such things you need dynamic SQL either execute immediate
(8.1.something and above) or dbms_sql (more clumsy)
Here is example using execute immediate
qaqa is table of one column col1, max (col1) = 17
qaqa_seq is sequence
gints@> create table qaqa (col1 number);
Table created.
gints@> insert into qaqa values (1);
1 row created.
gints@> insert into qaqa values (17);
1 row created.
gints@> create sequence qaqa_seq;
Sequence created.
gints@> create table matching_table (table_owner varchar2(40), table_name
varchar2(40), table_column varchar2(40)
2 , sequence_owner varchar2(40), sequence_name varchar2(40));
Table created.
gints@> insert into matching_table values ('GINTS', 'QAQA', 'COL1',
'GINTS', 'QAQA_SEQ');
1 row created.
gints@> commit;
DECLARE
v_table_owner varchar2(40);
v_table_name varchar2(40);
v_table_column varchar2(40);
v_select_statement VARCHAR2(1000);
v_seq_statement VARCHAR2(1000);
v_max_result number;
v_seq_result number;
BEGIN
FOR i IN (SELECT sequence_name, sequence_owner
FROM dba_sequences
WHERE sequence_name = 'QAQA_SEQ'
AND sequence_owner = 'GINTS')
LOOP
BEGIN
SELECT table_owner, table_name, table_column
INTO v_table_owner, v_table_name, v_table_column
FROM matching_table a
WHERE i.sequence_owner = a.sequence_owner
AND i.sequence_name = a.sequence_name ;
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
v_select_statement := 'SELECT max(' || v_table_column || ') ' ||
'FROM ' || v_table_owner || '.' || v_table_name;
EXECUTE IMMEDIATE v_select_statement INTO v_max_result;
dbms_output.put_line('SELECT statement is: ' || v_select_statement);
dbms_output.put_line('MAX number of ' || v_table_owner || '.' ||
v_table_name || '.' || v_table_column || ' is ' || v_max_result);
v_seq_statement := 'SELECT ' || i.sequence_owner || '.' ||
i.sequence_name || '.nextval FROM dual';
EXECUTE IMMEDIATE v_seq_statement INTO v_seq_result;
dbms_output.put_line('Select sequence nextval stetement is: ' ||
v_seq_statement);
dbms_output.put_line('Next sequence value is: ' || v_seq_result);
END LOOP;
END;
/
output result is following
SELECT statement is: SELECT max(COL1) FROM GINTS.QAQA
MAX number of GINTS.QAQA.COL1 is 17
Select sequence nextval stetement is: SELECT GINTS.QAQA_SEQ.nextval FROM
dual
Next sequence value is: 1
How to increment sequence appropriate times I'll leave to you as an
excersise ;))))))
Gints Plivna
IT Sist�mas, Mer�e�a 13, LV1050 R�ga
http://www.itsystems.lv/gints/
"Hagedorn,
Linda" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<lindah@epocra cc:
tes.com> Subject: PL/SQL question
Sent by:
[EMAIL PROTECTED]
om
2001.11.15
23:30
Please respond
to ORACLE-L
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
