Problems passing date with dynamic sql procedure.

From: marcie <marcie.tietjen_at_westgroup.com>
Date: 20 Aug 2002 12:06:32 -0700
Message-ID: <899474e5.0208201106.719948d5@posting.google.com>

I've written a procedure in v9i that uses dynamic sql to execute another procedure - the name of which is read from a table. Everything works fine when not passing any variables to the second procedure or when passing a number. But when I try to pass a date, I'm receiving the following error:

ERROR at line 1:

ORA-06550: line 1, column 18:
PLS-00201: identifier 'JUN' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored
ORA-06512: at "NETFLOW_USER.NF3_DAILY", line 29 ORA-06512: at line 1

When executing the procedure from a command line (ie. execute p_sum(2,'02-Jun-2002'), it works fine. But I can't seem to get it to work from within the other procedure. Here's an excerpt from the main procedure. 'procedure_name' is the cursor that contains the name of each procedure to run.

declare

 report_date			date := '2-Jun-2002';
 time_type     			number := 0;			
		 exe_ps := 'begin '||procedure_name||'('||time_type||','||report_date||');end;';
execute immediate exe_ps;	<- This is line 29.

The procedure being executed is:

create or replace procedure p_sum ( num in number, run_day in date) as begin
  insert into netflow.netflow_record_daily_sum (netflow_report_id,in_bit,device_group_id,file_timestamp,packets)   VALUES('test',num,'br',run_day,12345);   commit;
end p_sum;

Any ideas? Received on Tue Aug 20 2002 - 14:06:32 CDT