dbms_sql & dynamic sql problem

From: Doug Cowles <dcowles_at_nospambigfoot.com>
Date: Wed, 15 Dec 1999 14:46:44 -0500
Message-ID: <3857F023.D2A7278F@nospambigfoot.com>

I have a series of tables all with an ID column. They are all associated with sequences in
the form tablename_id. Sometimes during development, the sequences are out of synch
due to whatever the developers are doing (they're supposed to be primary key values).
I'm trying to write a little procedure that will take a table as an argument, then get the max
id from that table and re-create the sequence with a value that is one greater than the
maximum value that is already in the table. I don't see a way to do it with cursor variables,
(am I wrong?) So I'm trying to use the dbms_sql package like this...

procedure fixsequences (v_table_in IN VARCHAR2) is

v_parsestring VARCHAR2(100);
v_table_name VARCHAR2(100);
v_id             NUMBER;
v_cursorid   NUMBER;
retcode       NUMBER;

begin

v_table_name := UPPER(v_table_in);
v_cursorid:=dbms_sql.open_cursor;
v_parsestring:= 'select max(id) into v_id from '||v_table_name;

dbms_sql.parse(v_cursorid, v_parsestring,DBMS_SQL.NATIVE); retcode:= dbms_sql.execute(v_cursorid); dbms_output.put_line(v_id); -- For now end;

This generates the following:
.....

ORA-00905: missing keyword
ORA-06512: at SYS.DBMS_SYS_SQL, line 487
ORA-6512: at SYS.DBMS_SQL line 32

....

This appears to be caused by the select INTO v_id portion of the statement. If I place
very simple statements in there, they work ok, so the general syntax seems to be all
right.

I thought maybe using
dbms_sql.define_column could be used with a fetch if I took the INTO out so that it went
like
v_parsestring:= 'select max(id) from '||v_table_name; dbms_sql.parse(v_cursorid, v_parsestring,DBMS_SQL.NATIVE); dbms_sql.define_column(v_cursorid,1, max(id),100)

but it objects to the "MAX", and objects as well if I put a psuedo-column on it saying
that it needs to be defined..

Any advice?

Received on Wed Dec 15 1999 - 13:46:44 CST