dynamic SQL with date queries
From: Guy Jones <guyj_at_outsource.com.au>
Date: 1997/02/10
Message-ID: <5dn985$7da_at_inferno.mpx.com.au>#1/1
/* Foo Procedure */
end;
/
show errors
Date: 1997/02/10
Message-ID: <5dn985$7da_at_inferno.mpx.com.au>#1/1
[Quoted] [Quoted] 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 */
[Quoted] 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
Received on Mon Feb 10 1997 - 00:00:00 CET