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

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);
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