Re: dbms_sql & dynamic sql problem

From: <michael_bialik_at_my-deja.com>
Date: Wed, 15 Dec 1999 21:35:17 GMT
Message-ID: <8391il$lea$1@nnrp1.deja.com>

Hi.

 Try :

CREATE OR REPLACE 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;
 maxid            NUMBER;

BEGIN

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

 dbms_sql.parse(v_cursorid, v_parsestring,DBMS_SQL.NATIVE);  dbms_sql.define_column (v_cursorid, 1, maxid);  retcode:= dbms_sql.execute_and_fetch (v_cursorid);  dbms_sql.COLUMN_VALUE (v_cursorid, 1, maxid);  dbms_output.put_line(v_id);
END;   HTH. Michael.

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)
>
> 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:35:17 CST