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:
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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