Home » SQL & PL/SQL » SQL & PL/SQL » RPAD (10G)
RPAD [message #293716] Mon, 14 January 2008 14:10 Go to next message
jimbo021956
Messages: 5
Registered: October 2007
Junior Member
I am writing a PL/SQL procedure that uses the following
File Ouput command. This works fine except when the
variable v_bcallno is NULL. In this case I would want 20 blank characters output but instead ORACLE moves the values for v_dtout and v_dtdue over to the left, effectively ignoring v_bcallno. Is there a way to get 20 blanks printed out in this case for v_bcallno?

UTL_FILE.PUTF (v_circborr_fh,'%s %s %s %s %s\n',
RPAD(v_bmti,70),RPAD(v_bcallno,20),v_dtout,v_dtdue);

Thanks
Re: RPAD [message #293769 is a reply to message #293716] Mon, 14 January 2008 20:59 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
For RPAD, the variable can not be null:
SQL> set null *
SQL> select length(rpad(null,20)) from dual;

LENGTH(RPAD(NULL,20))
---------------------
*

since the variable is sometimes null, then you can use NVL:
SQL> select length(rpad(nvl(null,' '),20)) from dual;

LENGTH(RPAD(NVL(NULL,''),20))
-----------------------------
                           20

SQL>
Previous Topic: Best way to refresh data
Next Topic: how to store procedures in the database
Goto Forum:
  


Current Time: Fri Dec 02 16:19:05 CST 2016

Total time taken to generate the page: 0.25986 seconds