CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
craig
craig

Reputation: 26262

Oracle PL/SQL: assign a new value to an IN variable

I have a procedure defined as:

CREATE OR REPLACE PROCEDURE foo (
  AS_OF_DATE_IN IN DATE DEFAULT TRUNC(sysdate)-1
) AS ...

Which can be executed in these manners:

-- passes '11-NOV-2011'
exec foo('11-NOV-2011');

--set DEFAULT value (yesterday's date)
exec foo();

--makes things more difficult, as default value isn't set
exec foo(NULL);

What I want to do is:

AS_OF_DATE_IN:=NVL(AS_OF_DATE_IN, TRUNC(sysdate)-1);

but it generates a reassignment error.

Other than wrapping all usages of AS_OF_DATE_IN with NVL(), is there a more-efficient way to handle this situation?

** edit ** I made a fairly stupid mistake--I'm writing a procedure, not a function. The procedure doesn't return a value.

Upvotes: 2

Views: 2382

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132570

You can use a local variable inside the function:

CREATE OR REPLACE FUNCTION foo (
  AS_OF_DATE_IN IN DATE DEFAULT TRUNC(sysdate)-1
) RETURN ??? AS 
    V_AS_OF_DATE DATE DEFAULT NVL(AS_OF_DATE_IN, TRUNC(sysdate)-1);
BEGIN
 ... use V_AS_OF_DATE throughout
END;

Upvotes: 7

Jeff Moore
Jeff Moore

Reputation: 444

You have two issues:

1) you are passing in a date vs. a variable. You can not change the string value that is passed in.

2) You can make the value an "IN OUT" parameter and then you can assign it a value.

Example:

CREATE OR REPLACE FUNCTION FOO (as_of_date in out date) return date
AS
BEGIN
  as_of_date:= nvl(as_of_date,trunc(sysdate)-1);
  return as_of_Date;
END;


-- pass in a null date
DECLARE
   returnDate Date:= null;
BEGIN
   returnDate := foo(null);
   dbms_output.put_line('return date is '|| returnDate) 
END

-- pass in a real date
DECLARE
   returnDate Date:= '01-JAN-2011';
BEGIN
   returnDate := foo(null);
   dbms_output.put_line('return date is '|| returnDate) 
END

Upvotes: 2

Related Questions