Re: DBMS_SQL.PARSE

Hi.

 The reason for error that you don't have column named "START_DATE"  in your table.
 There 2 possible solutions.

1. Fast and simple ( but not so good ).
   • Remove START_DATE variable declaration and references
   • Use DATE_PARM:=' (DATE_TIME>= TO_DATE(''01-APR-95'') ' ; You supposed to get following SQL statement: SELECT * FROM SITES WHERE (SITE_ID='VB122') AND (DATE_TIME >= TO_DATE('01-APR-95') )
     • The problem here that the statement is using hard-coded values, so the same statement using different values for date or site_id must be reparsed ( hard parsing ).
2. A bit more complicated approach is:
   • Generate following SQL statement SELECT * FROM SITES WHERE (SITE_ID=:ST_ID) AND (DATE_TIME >= :START_DATE) ST_ID and START_DATE are PL/SQL local variables
     • Assign values to both of them: ST_ID := 'VB122'; START_DATE := TO_DATE ( '01-APR-99');
     • Use DBMS_SQL.BIND_VARIABLE ( after PARSE ) to apass these values to SQL.

 HTH. Michael.

In article <39079F28.BE126D44_at_mindspring.com>,   hclarius_at_mindspring.com wrote:
> I am using DBMS_SQL to generate dynamic cursors to interact
> with Web Server.
>
> I have a date variable in my table called DATE_TIME
>
> I create a local date variable using
> START_DATE:=TO_DATE('01-APR-95)
>
> I create the following varchars (strings).
>
> SERIES_PARM:=' SITES ' ; (SITES is a table)
>
> SITE_PARM:= ' (SITE_ID:=''VB122'') ' ; SITE_ID is a varchar
> in the SITES table.
>
> DATE_PARM:=' (DATE_TIME>=START_DATE) ' ;
>
> ALL_PARMS:='SELECT * FROM ' || SERIES_PARM || ' WHERE ' ||
> SITE_PARM || ' AND ' || DATE_PARM;
>
> The string when printed out looks like
> SELECT * FROM SITES WHERE (SITE_ID='VB122') AND (DATE_TIME
> >= START_DATE)
>
> Then I pass this string as follows
>
> C1:= DBMS_SQL.OPEN_CURSOR; --Allocates a cursor
>
> DBMS_SQL.PARSE(C1,ALL_PARMS,DBMS_SQL.NATIVE);
>
> This Works for SELECT * FROM SITES WHERE (SITE_ID='VB122')
> but I get invalid column error when I tack on the DATE_PARM.
>
> START_DATE IS A VALID DATE AND DATE_TIME IS A DATE IN THE
> SITES TABLE.
>
> Any ideas would be helpful.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 27 2000 - 00:00:00 CDT