Re: Problems passing date with dynamic sql procedure.

My guess

This is what you are doing from the command line

execute p_sum(2,'02-Jun-2002')

and this is what you are asking for from the procedure

execute p_sum(2,02-Jun-2002)

Try putting 2 more single quotes, before and after the date field, like this

exe_ps := 'begin ' || procedure_name || '(' || time_type || ',''' || report_date || '''); end;';

and it will produce

execute p_sum(2,'02-Jun-2002')

"marcie" <marcie.tietjen_at_westgroup.com> wrote in message news:899474e5.0208201106.719948d5_at_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:15:36 CDT