Re: dbms_sql & dynamic sql problem

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 - 15:39:50 CST