DBMS_OUTPUT.PUT_LINE truncates leading zero

dbforums_ajayp · #1 (**permalink**) 02-09-04, 06:13

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' ?

andrewst · #2 (**permalink**) 02-09-04, 07:15

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:


			
SQL> exec dbms_output.put_line(to_char(sysdate,'DDMMYYHH24MISS'))

090204121616



PL/SQL procedure successfully completed.



SQL> exec dbms_output.put_line(to_number(to_char(sysdate,'DDMMYYHH24MISS')))

90204121627



PL/SQL procedure successfully completed.

dbforums_ajayp · #3 (**permalink**) 02-09-04, 09:08

Quote:

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:


			
SQL> exec dbms_output.put_line(to_char(sysdate,'DDMMYYHH24MISS'))

090204121616



PL/SQL procedure successfully completed.



SQL> exec 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.

andrewst · #4 (**permalink**) 02-09-04, 09:32

Quote:

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.

dbforums_ajayp · #5 (**permalink**) 02-09-04, 09:43

Quote:

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 ' '

)

Internet Services

Web Development

Digital Marketing

Consumer Tech