How to pass a date to a stored procedure in Oracle9i

How do I pass a date to a stored procedure in Oracle9i? When I use the following I get an error message:

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?

    Requires Free Membership to View

    Login

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 
 






This was first published in November 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top