Re: dynamic SQL with date queries

From: Jerry Bull <gbbull_at_uswest.com>
Date: 1997/02/10
Message-ID: <32FF902D.24C9_at_uswest.com>#1/1

Guy Jones wrote:
>
> Can someone tell me why this doesn't work...
> This procedure is written to work with the employee DEMO table.
> Create the procedure, then try EXECing foo.
> You should get something like this:
>
> ERROR at line 1:
> ORA-00904: invalid column name
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> ORA-06512: at "SYS.DBMS_SQL", line 25
> ORA-06512: at "DEMO.FOO", line 13
> ORA-06512: at line 1
>
> Now try replacing line 10 with line 11.
> Hey presto... it works... why? Surely SYSDATE and n_date are the same data type?
>
> Am i missing something obvious here?
>
> -----------------------------------------------------------------
> /* Foo Procedure */
>
> create or replace procedure foo is
> q_string varchar2(256);
> ...
> n_date := to_date('01-JAN-86');
> q_string := 'select first_name, hire_date from employee where hire_date <
> n_date ';
> /* q_string := 'select first_name, hire_date from employee where hire_date <
> SYSDATE '; */
> cursor_handle := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_handle, q_string, dbms_sql.v7);
>...

You are embedding n_date into your query string. This makes it invisible to the parsing scheme, and therefore it thinks it is suppose to be a database column. You will need to perform one of the following two options:

1. convert your variable into a string and embed that into your string:

       q_string := 'select first_name, hire_date from employee where hire_date < to_date(''' ||

                        to_char(n_date, 'DD-MON-YYYY') || ''')';

  b) use a bind variable to bind your local variable into the string:

       q_string := 'select first_name, hire_date from employee where hire_date < :n_date';

       cursor_handle := dbms_sql.open_cursor;
       dbms_sql.parse(cursor_handle, q_string, dbms_sql.v7);
       dbms_sql.bind_variable (cursor_handle, 'n_date', n_date);
       ...

Jerry.

---

---

Jerry Bull, Sr. MTS         Phone: 303-624-3073
U S WEST Technologies       Fax:   303-624-8462

931 14th Street, Suite 920 Email: gbbull_at_uswest.com Denver, CO 80202

---

Received on Mon Feb 10 1997 - 00:00:00 CET