Q
Manage Learn to apply best practices and optimize your operations.

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?
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 last published in November 2003

Dig Deeper on Oracle stored procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close