Re: dbms_sql & dynamic sql problem

Hi,

This example shows how to get result from dynamic sql.

• prepare a cursor to select from the source table source_cursor := dbms_sql.open_cursor; DBMS_SQL.PARSE(source_cursor, 'SELECT id, name, birthdate FROM ' || source, DBMS_SQL); DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id); DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name, 30); DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate); ignore := DBMS_SQL.EXECUTE(source_cursor);

Yours, Iurie
TV Guide, Ca, Oracle DBA.

Doug Cowles wrote in message <3857F023.D2A7278F_at_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?
>
>- Dc.
>
Received on Wed Dec 15 1999 - 14:46:56 CST