Re: dynamic SQL with date queries
Date: 1997/02/10
Message-ID: <5do56h$1o7_at_inet-nntp-gw-1.us.oracle.com>#1/1
Unread news in comp.databases.oracle.tools 45 articles + 223 oldIn article <5dn985$7da_at_inferno.mpx.com.au>, guyj_at_outsource.com.au (Guy Jones) writes:
|> 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?
|>
It's not the type of the variable - it's the name of the variable. Try this:
q_string := 'select first_name, hire_date from employee where hire_date < '; q_string := q_string || to_char(n_date, 'DD-MON-YY');
|> Am i missing something obvious here?
|>
|> -----------------------------------------------------------------
|> /* Foo Procedure */
|>
|> create or replace procedure foo is
|> q_string varchar2(256);
|> cursor_handle integer;
|> cnt number;
|> f_name varchar2(15);
|> h_date date;
|> n_date date;
|> begin
|> 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);
|> dbms_sql.define_column(cursor_handle, 1, f_name, 15);
|> dbms_sql.define_column(cursor_handle, 2, h_date);
|> cnt := dbms_sql.execute(cursor_handle);
|> LOOP
|> IF dbms_sql.fetch_rows(cursor_handle) = 0 then
|> exit ;
|> else
|> dbms_sql.column_value(cursor_handle, 1, f_name);
|> dbms_sql.column_value(cursor_handle, 2, h_date);
|> dbms_output.put_line(f_name||' '||h_date);
|> end if;
|> end loop;
|> dbms_sql.close_cursor(cursor_handle);
|> end;
|> /
|> show errors
|>
|> Please reply to my email address
|> guyj_at_outsource.com.au
|>
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Mon Feb 10 1997 - 00:00:00 CET