How to pass a date to a stored procedure in Oracle9i

v_test := 'begin test_edel(''p_processed_date'',''p_module''); end;'; execute immediate v_test;

Error = ORA-01858: a non-numeric character was found where a numeric was expected.

The error has to do with the way p_processed_date is being passed in. P_processed_date is of type date. Any ideas?
There are a couple of ways to pass local variables or values in to dynamic native SQL. I'll illustrate with examples. Both examples use a very simple procedure we'll create, NEXT_MONTH.

create or replace procedure next_month
( inDate in date )
as
begin
  dbms_output.put_line(add_months(inDate,1));
end next_month;
/

For the first example, we'll convert our date to a string (with TO_CHAR) that will be implicitly converted back to a date by our call to NEXT_MONTH. We will set SERVEROUTPUT to ON in order to see the output from the NEXT_MONTH procedure.

set serveroutput on

declare
  myDate date := sysdate;
  sqlText varchar(100);
begin
  sqlText := 'begin next_month(''' || to_char(mydate) || '''); end;';
  execute immediate sqltext;
end;
/

For the next example (which I happen to like better), we'll use a bind variable and the USING clause of the EXECUTE IMMEDIATE statement. The colon (:) that comes before "boundDate" indicates that it is a bind variable. The USING clause indicates what value to pass in for the bind variable. Here it is.

declare
  myDate date := sysdate;
  sqlText varchar(100);
begin
  sqlText := 'begin next_month(:boundDate); end;';
  execute immediate sqltext using myDate;
end;
/

The results of running these two PL/SQL blocks are the same:

01-DEC-2003

PL/SQL procedure successfully completed