Re: dbms_sql & dynamic sql problem

Thank you very much.. That worked great!! I guess I didn't realize the variable in the column call didn't have to match the
select clause column by name.
Thanks again,

• Dc.

James Lorenzen wrote:

> Reply inline.
>
> In article <3857F023.D2A7278F_at_nospambigfoot.com>,
> Doug Cowles <dcowles_at_nospambigfoot.com> wrote:
> > This is a multi-part message in MIME format.
> > --------------66F9F0226F4C7E8A6C6CC111
> > Content-Type: text/plain; charset=us-ascii
> > Content-Transfer-Encoding: 7bit
> >
> > 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)
> >
> The "max(id)" is a number being returned, so the above syntax should be:
> dbms_sql.define_column(v_cursorid,1, v_id)
> where v_idr is a defined number for the return.
>
> After this then you need to execute the query, since you are only
> getting the MAX value, the execute_and_fetch will work fine.
> dbms_sql.execute_and_fetch(v_cursorid) ;
>
> Then you need to get the column value.
> dbms_sql.column_value(v_cursorid,1, v_id) ;
>
> > 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.
> >
> > --------------66F9F0226F4C7E8A6C6CC111
> > Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
> > Content-Transfer-Encoding: 7bit
> > Content-Description: Card for Doug Cowles
> > Content-Disposition: attachment; filename="vcard.vcf"
> >
> > begin: vcard
> > fn: Doug Cowles
> > n: Cowles;Doug
> > org: IBM
> > email;internet: dcowles_at_nospambigfoot.com
> > title: DBA
> > note: Please remove nospam from e-mail to reply.
> > x-mozilla-cpt: ;0
> > x-mozilla-html: FALSE
> > version: 2.1
> > end: vcard
> >
> > --------------66F9F0226F4C7E8A6C6CC111--
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Received on Wed Dec 15 1999 - 16:20:59 CST