Re: Problem with DBMS_SQL

From: <bonanos_at_yahoo.com>
Date: Wed, 15 Apr 1998 01:08:28 -0600
Message-ID: <6h1isr$k1t$1@nnrp1.dejanews.com>

> If I build the statement in a Varchar2 or Char variable
> Eg.
> SQL_STMT := 'Select ...';
> DBMS_SQL.PARSE(CURSOR_NUM,SQL_STMT,DBMS_SQL.V7);
>
> fails with the following message:
> *
> ERROR at line 1:
> ORA-20400: ORA-00911: invalid character
>
> Has anyone successfully used a character variable for the statement and
> if so, was it necessary to do anything special?
> TIA
> Cory Brooks

Here is a working example:

---

query_text varchar2(1000);

query_text := 'select tradename,sic from sotiri.levy,sotiri.siclevy where payernumber = levyno and ( ';

query_text := query_text || ' sic like ''' || sub_text || '%''' ;

query_text := query_text || ' or sic like ''' || sub_text || '%''' ;

query_text := query_text ||' ) ';

q_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(q_cursor,query_text,1);

etc...

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Apr 15 1998 - 02:08:28 CDT