Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    19

    Question Unanswered: Problem assigning defaults declaration

    I am having trouble assigning defaults in the declaration statement of my procedures. Here is an example:

    CREATE OR REPLACE PROCEDURE EW_ORDER_TRACKING (ORDER_NUM_V IN NUMBER DEFAULT NULL,
    DATE_LOW_V IN DATE DEFAULT '01/01/2001',
    DATE_HIGH_V IN DATE DEFAULT TRUNC(SYSDATE))IS

    I usually assign NULL to most of these variables to catch any empty strings but in this case I need to default the variables to the given dates. However, no matter what I add as the default, they always seem to take on NULL as their defaults.

    Can anyone tell me what I'm doing wrong here?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem assigning defaults declaration

    It works for me:

    SQL> create or replace procedure EW_ORDER_TRACKING (ORDER_NUM_V IN NUMBER DEFAULT NULL,
    2 DATE_LOW_V IN DATE DEFAULT '01/01/2001',
    3 DATE_HIGH_V IN DATE DEFAULT TRUNC(SYSDATE))IS
    4
    5 begin
    6 dbms_output.put_line(DATE_LOW_V);
    7 dbms_output.put_line(DATE_HIGH_V);
    8 end;
    9 /

    Procedure created.

    SQL> exec EW_ORDER_TRACKING (1)
    01/01/2001
    13/11/2003

    PL/SQL procedure successfully completed.

    However, I would amend the DATE_LOW_V parameter as follows:

    DATE_LOW_V IN DATE DEFAULT TO_DATE('01/01/2001','DD/MM/YYYY'),

    otherwise you are dependent on the NLS_DATE_FORMAT:

    SQL> alter session set nls_date_format='DD-MON-YYYY';

    Session altered.

    SQL> exec EW_ORDER_TRACKING (1)
    begin EW_ORDER_TRACKING (1); end;

    *
    ERROR at line 1:
    ORA-01843: not a valid month
    ORA-06512: at line 1

  3. #3
    Join Date
    Jan 2003
    Posts
    19

    Update

    Thank Tony..

    The procedure is running using iAS. I have an HTML form accepting variables for the order number and date range. If the user doesn't enter anything for the date do they default to null and over ride the defaults in the declaration? That may be what's happening but I was under the assumption that the defaults would take over in the event of a null entry.

    What do you think?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Update

    You have hit the nail on the head. If a value is passed for the parameter at all, even a NULL, then it overrides the default. The default only applies if the parameter is actually omitted from the call, as in my example:

    SQL> REM Parameters omitted
    SQL> exec EW_ORDER_TRACKING (1)
    01/01/2001
    13/11/2003

    PL/SQL procedure successfully completed.

    SQL> REM Parameters passed as NULL
    SQL> exec EW_ORDER_TRACKING (1, NULL, NULL)



    PL/SQL procedure successfully completed.

Posting Permissions

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