CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Crazymango
Crazymango

Reputation: 111

Why parameter with default value is null?

I just want to ask a simple question, I have a procedure in my package: procedure(p_x in default sysdate). However, when i try to use this p_x to do query in my procdure, i got nothing from my table. My assumption is p_x does not get value from sysdate and it is null. Why does this happen? How can I fix it?

Thanks in advance.

In Package:

Procedure M_m(p_x in default sysdate)

In Package Body:

Procedure M_m(p_x in default sysdate) as

cursor cur is select manager_id, manager_name,manger_department 
  from employ_table where trunc(EmploymentDate)=trunc(p_x);
r_cur cur%rowtype;

Begin
  for r_cur in cur 
  loop
  DBMS_OUTPUT.PUT_LINE(employ_table.manager_id || employ_table.manager_name 
   || employ_table.manager_department);   
end loop;

end;

Upvotes: 2

Views: 1847

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Currently (as of 18c) the default or := clause in a PLSQL parameter specification behaves like a pre-12c default clause in a table column definition. That is, the default value is only applied when the corresponding column is not specified at all.

If you define a table column as

somedate date default sysdate

then it will only be assigned the default value if the insert statement does not mention column somedate at all. If the insert sets somedate to null then it will be null, regardless of any default. The same behaviour is true of PL/SQL parameters. An explicit null value passed in overrides any default that you set for a parameter.

You will need to define a local constant along the lines of

k_x constant date := coalesce(p_x,sysdate);

and then have the rest of the procedure refer to that instead of the parameter.

In 12c we have default on null for table columns, but in my opinion PL/SQL lags behind, as it has no equivalent syntax for parameter defaults. If you pass null to a PL/SQL parameter, it respects that null and not any default that you specified, as it has no on null syntax option.

If you would like Oracle to extend PL/SQL parameter defaults along the same lines as table columns then consider voting for my suggestion here:

https://community.oracle.com/ideas/18189

Upvotes: 2

wolφi
wolφi

Reputation: 8361

I guess Wernfried is right, it depends on how exactly you call it. Just add a DBMS_OUTPUT.PUT_LINE(p_x) immediately before the FOR to verify the value of the variable:

CREATE TABLE employ_table AS SELECT * FROM scott.emp;
UPDATE employ_table SET hiredate=SYSDATE WHERE ename='SCOTT';

CREATE OR REPLACE PACKAGE P IS
  PROCEDURE M_m(p_x IN DATE DEFAULT sysdate);
END;
/

CREATE OR REPLACE PACKAGE BODY p IS

  PROCEDURE M_m(p_x IN DATE DEFAULT sysdate) IS
    CURSOR cur IS 
    SELECT * FROM employ_table WHERE TRUNC(hiredate)=TRUNC(p_x);
  BEGIN
    DBMS_OUTPUT.PUT_LINE('p_x='||p_x); 
    FOR r_cur IN cur LOOP
      DBMS_OUTPUT.PUT_LINE(r_cur.mgr);   
    END LOOP;
  END M_m;

END P;
/

EXEC P.m_m(DATE '1980-12-17');
p_x=1980-12-17 00:00:00
7902

EXEC P.m_m();
p_x=2018-05-26 17:50:11
7566

EXEC P.m_m(NULL);
p_x=

Upvotes: 3

Related Questions