PL/SQL and Default Parameters
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: PL/SQL and Default Parameters

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    make the default value a non-null value that will never be sent via the calling code

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15
    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

  10. #10
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width