Re: dbms_sql & dynamic sql problem

But if your column is max(id) - you get PLS-00204: function or psuedo_column 'MAX' may be used inside a sql statement only.
on the line you do the define_column.
And if you make an alias for it, it says PLS-00201 Identifier (whatever the alias was) must be declared.

Doesn't solve problem.
- Dc.

Iurie Jigalov wrote:

> 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 - 15:29:23 CST