Different display when run in TOAD and SQLPLUS

Toad for Oracle & DevOps Toolkit
1

When run the following script

col job_name for a15
col owner for a8
col state for a10
col start_date for a18
col job_name for a15
col job_action for a60
set linesize 200
set echo on

spool scheduler_job_status.log

select job_name, owner, state, enabled, to_char(start_date,'DD-MON-YY HH24:MI:SS') as start_date,job_action from dba_scheduler_jobs where job_name = 'LOCK_USER_JOB';

spool off

TOAD output :

toad

where sqlplus output:

sqlplus

TOAD seems warp the ‘JOB_ACTION’ wording to the beginning of next new line while sqlplus ‘JOB_ACTION’ warp within it own column which is what I expected.

How could I change the behaviour of ‘TOAD’ to ‘SQLPLUS’?

My TOAD version is :
Toad for Oracle Xpert Edition (64-bit)
Add-Ons: DB Admin Module
17.1.717.3711

2

I think it's a bug. I see that if the text is just too long, Toad wraps it the same way as SQLPlus. It just doesn't handle line feeds the same way SQLPlus does.

For example, this SQL returns long text the wraps the same way SQL Plus does.

col job_name for a15
col owner for a8
col state for a10
col start_date for a18
col job_name for a15
col job_action for a60
set linesize 200
set echo on

spool scheduler_job_status.log

select job_name, owner, state, enabled, to_char(start_date,'DD-MON-YY HH24:MI:SS') as start_date, 
       '1234567890 ' || '1234567890 ' ||'1234567890 ' ||'1234567890 ' ||'1234567890 ' ||
       '1234567890 ' ||'1234567890 ' ||'1234567890 '||'1234567890 'as job_action 
from dba_scheduler_jobs;

spool off
3

Dear John,

Thank for your reply. What additonal steps do I need to get it fixed or I just wait for patching release?

Tks/Kenneth

4

I've logged the bug in our internal bug tracking system. I can't provide an ETA for the fix.

The only workaround I can offer would be for you use the REPLACE function on your SQL, replacing line feeds with spaces, something like the following:

SELECT job_name, owner, state, enabled,
       TO_CHAR (start_date, 'DD-MON-YY HH24:MI:SS')     AS start_date,
       replace(replace(job_action, CHR(10), ' '), CHR(13), null) as job_action
  FROM dba_scheduler_jobs
WHERE job_name = 'LOCK_USER_JOB';