CR/LF not working

[markis]

DB: Oracle 9.2.0.6

Listed below is the sql statement in question. When running this sql statement in SQLPLUS on *nux the output, as expected, is displayed on three lines. However, when running this sql statement in the ps/sql developer command window only the first line is displayed. I've also tried using chr(10)||chr(13) but to no avail.

The sql statement could be seperated out to three diffeent statements but I want to keep this as a single sql statement.

What's missing to make this work?

select rpad('Host Server',17,' ')||': ' ||upper(sys_context('USERENV','HOST'))||chr(10)||
rpad('Database Name',17,' ')||': ' ||upper(sys_context('USERENV','DB_NAME'))||chr(10)||
rpad('Script Begin time',17,' ')||': ' ||to_char(systimestamp,'dd-MON-yyyy HH24:mi:ss.ff2')
from dual;

 

[rbrooker]

change the order around. if you have database name, script begin time and then host server, you can get all three lines when you click on the (...) at the right of the result grid.

 

[markis]

The script runs fine when in SQLPLUS. However, when running the script in PL/SQL Developer it fails to handle CR/LF. Why would the order have anything to do with the output?

 

[William Robertson]

It works fine for me, in both a Command window and a SQL window (Oracle 9.2.0.5, PL/SQL Dev 6.0.5.926).

I would prefer this though:

select rpad('Host Server:',19,' ') ||upper(sys_context('USERENV','HOST'))||chr(10)||
rpad('Database Name:',19,' ') ||upper(sys_context('USERENV','DB_NAME'))||chr(10)||
rpad('Script begin time:',19,' ')|| to_char(systimestamp,'dd-MON-yyyy HH24:mi:ss.ff2')
from dual;

http://phantomnitpicker.blogspot.com/2005/03/it-colon.html

 

[D.]

Markis,

The sys_contect function appends a CHR(0) onto the end of the varchar2 value returned. It seems that PLSQL/Developer does not handle this NULL character properly.

Simply remove the extra "non-printable" character from each of the sys_context return values :-

SELECT rpad('Host Server', 17, ' ') || ': ' ||
       upper(rtrim(sys_context('USERENV', 'HOST'), chr(0))) || chr(10) ||
       rpad('Database Name', 17, ' ') || ': ' ||
       upper(rtrim(sys_context('USERENV', 'DB_NAME'), chr(0))) || chr(10) ||
       rpad('Script Begin time', 17, ' ') || ': ' ||
       TO_CHAR(systimestamp, 'dd-MON-yyyy HH24:mi:ss.ff2')
FROM   dual;

Hope this helps,
D.

 

[D.]

Markis,

Follow-up to my last post. It seems there is an Oracle bug in the HOST returned values and gets and extra 0x00 charcater appended. This would account for the change of order working OK.

See Metalink - Bug 2628258 for details.

Still remains the issue with PLSQL/Developer not displaying the NUL charcater improperly though. Maybe Marco can take this up as a bug.

Cheers,
D.

 

[D.]

Marco,

Are you going to log this as a bug ?

D.

 

[Marco Kalter]

Character strings should not contain 0-characters, but I will log this as a bug and will see what we can do.