PL/SQL and Default Parameters

[chrisrlong]

Okay, assume I have something as basic as this...

CREATE OR REPLACE PROCEDURE FOO
(
---p_Value1---IN---VARCHAR2---:=---NULL---,
---p_Value2---IN---VARCHAR2---:=---NULL
)
AS
BEGIN
---IF ( p_Value1 IS NULL ) THEN
------DBMS_OUTPUT.PUT_LINE ('Value1 IS NULL');
---END IF;
END;

Both of the following statements will tell me that 'Value1 IS NULL':

- FOO ( p_Value2=>'Hello');
- FOO ( NULL, 'Hello' );

**********************************************
My question is: Is there any way at all to distinguish, within the procedure, between the 2 calls?
**********************************************

Basically, I need to know whether a parameter was actually passed to the procedure or not. I know I can set the default value so that they don't *have to* pass the parameter. Done. But now, how do I distinguish between the default value that I set and the user actually filling the parameter with a value that happens to match the default value?

Thanks in advance,

- Chris

[kmesser]

make the default value a non-null value that will never be sent via the calling code

[chrisrlong]

I have no control over what the client could send. On top of that, this is a specific usage that will be duplicated potentially hundreds of times. It needs to be completely generic.

Thanks for the feedback, but the issue remains - Any other ideas out there?

- Chris

[jmodic]

Chris,

AFAIK there is no way to find out if the default value in the parameter was actually passed with procedure call or if it was ismply set as default by the procedure.

In your case, would it be acceptable to set the parameter's default value to some nonprintable character (ASCII < 32) instead of NULL? I guess your end user will not be able to send you a nonprintable character as parameter value, would he? Of course, in your procedure code you'll substitute this default value with NULL, but you'll be able to catch user entered NULL's.

eg
CREATE OR REPLACE PROCEDURE FOO
(
---p_Value1---IN---VARCHAR2---:=---CHR(13)---,
---p_Value2---IN---VARCHAR2---:=---CHR(13)
)
AS
--v_value1 VARCHAR2(4000) := p_value1;
BEGIN
---IF ( v_Value1 IS NULL ) THEN
------DBMS_OUTPUT.PUT_LINE ('Value1 IS NULL');
---ELSIF ( v_value = CHR(13) THEN
------DBMS_OUTPUT.PUT_LINE ('Value1 IS NULL by DEFAULT');
------value_1 := NULL;
---END IF;
END;

[chrisrlong]

Hmmm, interesting thought. I like that one, but...

Unfortunately, I neglected to mention that I need to accomplish this same 'trick' with every possible datatype.

Any more cool tricks up your sleeve?

- Chris

[jmodic]

Chris (and anyone else who might still be interested in this thread, despite the long silence since the last post on this topic),

Below is an universal sollution to distinguish between the default NULL or passed NULL in a parameter of any datatype. The sollution seems somehow clumsy, but this is as close as I could get.

The main idea: instead of setting null value as the default parameter value, we rather call a function which sets this default null value to the parameter, but at the same time this function also buffers the information that parameter is null because no value was passed to it. This buffer can later be inspected inside the called procedure or function.

The functions for setting default NULLs to the parameters of three main data types are packed inside the package called DEFAULT_NULL. For buffering information which parameters were set to null because of the default value I used system package DBMS_APLICATION_INFO. With this package we can easily read and write inter-session information, but the main drawback is that the length of such session specific information is limited to 64 characters. So this approach might not be usable on procedures or functions with very high number of parameters with the default values (the upper limit of such parameters would be around 30). As a delimiter between the refferences to different parameters I used character '#'.

So here is the package DEFAULT_NULL:

CREATE OR REPLACE PACKAGE default_null AS
--FUNCTION date_null (p_name VARCHAR2) RETURN DATE;
--FUNCTION varchar2_null (p_name VARCHAR2) RETURN VARCHAR2;
--FUNCTION number_null (p_name VARCHAR2) RETURN NUMBER;
END default_null;
/

CREATE OR REPLACE PACKAGE BODY default_null AS
--PROCEDURE append_info (p_info VARCHAR2) IS
----v_current_info VARCHAR2(64);
--BEGIN
----DBMS_APPLICATION_INFO.READ_CLIENT_INFO(v_current_info);
----DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NVL(v_current_info, '#') || p_info || '#');
--END v_current_info;

--FUNCTION date_null(p_name VARCHAR2) RETURN DATE IS
--BEGIN
----append_info (p_name);
----RETURN NULL;
--END date_null;

--FUNCTION varchar2_null(p_name VARCHAR2) RETURN VARCHAR2 IS
--BEGIN
----append_info (p_name);
----RETURN NULL;
--END varchar2_null;

--FUNCTION number_null(p_name VARCHAR2) RETURN NUMBER IS
--BEGIN
----append_info (p_name);
----RETURN NULL;
--END number_null;
END default_null;
/

And here is a procedure FOO which sets default NULLs by calling DEFAULT_NULL's functions:

CREATE OR REPLACE PROCEDURE FOO
--(p_value1 IN DATE := default_null.date_null('P1'),
---p_value2 IN NUMBER := default_null.number_null('P2')
--)
AS
--v_client_info VARCHAR2(64);
BEGIN
--IF p_value1 IS NULL THEN
----DBMS_APPLICATION_INFO.READ_CLIENT_INFO(v_client_info);
----IF INSTR(v_client_info, '#P1#') > 0 THEN
------DBMS_OUTPUT.PUT_LINE ('Value1 IS NULL by DEFAULT');
----ELSE
------DBMS_OUTPUT.PUT_LINE('Value1 IS NULL');
----END IF;
--END IF;
--IF p_value2 IS NULL THEN
----IF INSTR(v_client_info, '#P2#') > 0 THEN
------DBMS_OUTPUT.PUT_LINE ('Value2 IS NULL by DEFAULT');
----ELSE
------DBMS_OUTPUT.PUT_LINE('Value2 IS NULL');
----END IF;
--END IF;
--DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL); -- Clear the buffered information
--NULL; -- "True" procedure logic follows
END;

Here are some different calls to this procedure and its output from SQL*Plus:

SQL> EXEC foo(sysdate, 1)
PL/SQL procedure successfully completed.

SQL> EXEC foo
Value1 IS NULL by DEFAULT
Value2 IS NULL by DEFAULT
PL/SQL procedure successfully completed.

SQL> EXEC foo(NULL, NULL)
Value1 IS NULL
Value2 IS NULL
PL/SQL procedure successfully completed.

SQL> EXEC foo(NULL, 1)
Value1 IS NULL
PL/SQL procedure successfully completed.

SQL> EXEC foo(sysdate, NULL)
Value2 IS NULL
PL/SQL procedure successfully completed.

SQL> EXEC foo(NULL)
Value1 IS NULL
Value2 IS NULL by DEFAULT
PL/SQL procedure successfully completed.

SQL> EXEC foo(p_value2 => NULL)
Value1 IS NULL by DEFAULT
Value2 IS NULL
PL/SQL procedure successfully completed.

HTH,

[chrisrlong]

A function call as a default!??!

Simply brilliant Jurij!!!

That is completely awesome!!

I hope you don't mind if I run with that idea for the auto-CRUD-generating macro code in my book (with proper credits, of course ).

Since I'm auto-generating the packages, I can do something slightly different like have a different package-level flag for each field or a table structure with all the field names. Then, I can pass the column variable or table variable to the default_null package. I actually have other plans for the application info area, although I'm still not sure if that part will fit into the 'flow' of the book or not.

Thanks a ton!! You are a life saver!!

- Chris

[jmodic]

Originally posted by chrisrlong
I hope you don't mind if I run with that idea for the auto-CRUD-generating macro code in my book (with proper credits, of course ).

Sure I don't mind. On the contrary, I'd be very flattered .

There might be one condition, though - to send me your draft book when it is finished. Just kidding.....

[Sonali]

Is there any way to add default to the column with function call
like this one ?

create table xyz
Adv_ID int PRIMARY KEY DEFAULT fn_GetNextID('ETADVANCEADVID')

Thanks
Sonali

[Sonali]

I want to do this to get the unique id across schema, for offline access so the function will get it.
Is there any way i can do this other than having a trigger and a counter table ??
You can do this in sql server like this.
Thanks
Sonali