Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Question Unanswered: DBMS_OUTPUT.PUT_LINE truncates leading zero

    I am passing date time variable (ddmmyyhh24miss) as input to PL*SQL from a Unix shell script.

    When I print this variable in the PL*SQL using DBMS_OUTPUT.PUT_LINE, in case of a day less than 10 (e.g. 06) it prints only '6'.

    How can I make DBMS_OUTPUT.PUT_LINE to print '06' ?
    Regards,

    Ajay Patel

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

    Re: DBMS_OUTPUT.PUT_LINE truncates leading zero

    I suspect it is your call from Unix that is losing the leading zero - e.g. by passing the value as a NUMBER rather than as a character string:

    PHP Code:
    SQLexec dbms_output.put_line(to_char(sysdate,'DDMMYYHH24MISS'))
    090204121616

    PL
    /SQL procedure successfully completed.

    SQLexec dbms_output.put_line(to_number(to_char(sysdate,'DDMMYYHH24MISS')))
    90204121627

    PL
    /SQL procedure successfully completed

  3. #3
    Join Date
    Feb 2004
    Posts
    11

    Re: DBMS_OUTPUT.PUT_LINE truncates leading zero

    Originally posted by andrewst
    I suspect it is your call from Unix that is losing the leading zero - e.g. by passing the value as a NUMBER rather than as a character string:

    PHP Code:
    SQLexec dbms_output.put_line(to_char(sysdate,'DDMMYYHH24MISS'))
    090204121616

    PL
    /SQL procedure successfully completed.

    SQLexec dbms_output.put_line(to_number(to_char(sysdate,'DDMMYYHH24MISS')))
    90204121627

    PL
    /SQL procedure successfully completed
    No, the call from Unix is not removing the leading zero.

    When I print the variable value using PROMPT before the PL*SQL block, the value is printed with the leading zero.
    Regards,

    Ajay Patel

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

    Re: DBMS_OUTPUT.PUT_LINE truncates leading zero

    Originally posted by dbforums_ajayp
    No, the call from Unix is not removing the leading zero.

    When I print the variable value using PROMPT before the PL*SQL block, the value is printed with the leading zero.
    Show your code! Clearly DBMS_OUTPUT is not the isee (see my example above). Perhaps you forgot to put single quotes around the value? :-

    SQL> exec dbms_output.put_line(&v)
    Enter value for v: 010203
    10203

    PL/SQL procedure successfully completed.

    SQL> exec dbms_output.put_line('&v')
    Enter value for v: 010203
    010203

    PL/SQL procedure successfully completed.

  5. #5
    Join Date
    Feb 2004
    Posts
    11

    Re: DBMS_OUTPUT.PUT_LINE truncates leading zero

    Originally posted by andrewst
    Show your code! Clearly DBMS_OUTPUT is not the isee (see my example above). Perhaps you forgot to put single quotes around the value? :-

    SQL> exec dbms_output.put_line(&v)
    Enter value for v: 010203
    10203

    PL/SQL procedure successfully completed.

    SQL> exec dbms_output.put_line('&v')
    Enter value for v: 010203
    010203

    PL/SQL procedure successfully completed.
    Thanks!!!

    The problem was because of the missing quotes ' ' )
    Regards,

    Ajay Patel

Posting Permissions

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