toad 12.1.022 editor formatting problem

Seems that Toad has truncate problem in sqlplus editor (sorry if this is somewhere repeated bug):

Here is query:

set pagesize 1000
set linesize 170
column since format a17
column logon format a20
column event format a25 truncate
column machine format a25 truncate
column stt format a3
column sts format a3
column username format a10 truncate
column n format 9999.99

break on machine on report skip 1

compute sum of sess_count on machine
compute sum of sess_count on report

set time on
spool client.txt append

select ‘CFROM’ tag,
to_char(sysdate,‘hh24:mi:ss’) when,
machine,
event,
seconds_in_wait sec_wait,
sql_id,
prev_sql_id,
count(*) sess_count,
to_char(sysdate - (SECONDS_IN_WAIT / (60 * 60 * 24)),‘dd.mm.yy hh24:mi:ss’) since,
– next two lines useful if trying to predict a concurrent spike. 1200 being 20 minutes
– Left over from site specific issue but could be useful.
– next is last active time + 20 minutes
– n is a count down to next predicted spike
– to_char(sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)),‘dd-mon-yy hh24:mi:ss’) NEXT ,
– ((sysdate - (sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)) ) ) * (60 * 60 * 24) ) / 60 n,
username,
substr(status,1,3) sts,
substr(state,1,3) stt
from v$session
where 1=1 --sid=&1
group by machine,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
username,
substr(status,1,3),
substr(state,1,3)
order by machine,
username,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
substr(status,1,3),
substr(state,1,3)
;

Toad output:

unknown command “sqlplus” - rest of line ignored.
unknown command “sqlplus” - rest of line ignored.
unknown command “sqlplus” - rest of line ignored.
old: select ‘CFROM’ tag,
to_char(sysdate,‘hh24:mi:ss’) when,
machine,
event,
seconds_in_wait sec_wait,
sql_id,
prev_sql_id,
count() sess_count,
to_char(sysdate - (SECONDS_IN_WAIT / (60 * 60 * 24)),‘dd.mm.yy hh24:mi:ss’) since,
– next two lines useful if trying to predict a concurrent spike. 1200 being 20 minutes
– Left over from site specific issue but could be useful.
– next is last active time + 20 minutes
– n is a count down to next predicted spike
– to_char(sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)),‘dd-mon-yy hh24:mi:ss’) NEXT ,
– ((sysdate - (sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)) ) ) * (60 * 60 * 24) ) / 60 n,
username,
substr(status,1,3) sts,
substr(state,1,3) stt
from v$session
where 1=1 --sid=&1
group by machine,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
username,
substr(status,1,3),
substr(state,1,3)
order by machine,
username,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
substr(status,1,3),
substr(state,1,3)
new: select ‘CFROM’ tag,
to_char(sysdate,‘hh24:mi:ss’) when,
machine,
event,
seconds_in_wait sec_wait,
sql_id,
prev_sql_id,
count(
) sess_count,
to_char(sysdate - (SECONDS_IN_WAIT / (60 * 60 * 24)),‘dd.mm.yy hh24:mi:ss’) since,
– next two lines useful if trying to predict a concurrent spike. 1200 being 20 minutes
– Left over from site specific issue but could be useful.
– next is last active time + 20 minutes
– n is a count down to next predicted spike
– to_char(sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)),‘dd-mon-yy hh24:mi:ss’) NEXT ,
– ((sysdate - (sysdate - ( (seconds_in_wait ) / (60 * 60 * 24)) + (1200 / (60 * 60 * 24)) ) ) * (60 * 60 * 24) ) / 60 n,
username,
substr(status,1,3) sts,
substr(state,1,3) stt
from v$session
where 1=1 --sid=%
group by machine,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
username,
substr(status,1,3),
substr(state,1,3)
order by machine,
username,
event,
seconds_in_wait,
sql_id,
prev_sql_id,
substr(status,1,3),
substr(state,1,3)

TAG WHEN MACHINE EVENT SEC_WAIT SQL_ID


PREV_SQL_ID SESS_COUNT SINCE USERNAME STS STT


CFROM 14:17:59 AGROKOR\A-BJOVCIC SQL*Net message from client 2270 09zkyck6mgr5s
09zkyck6mgr5s 1 20.02.14 13:40:09 SYS INA WAI

           ****************************************************************                                                                                          
          ----------                                                         
                                                                                                                                                                      
           sum                                                                                                                                                       
                   1                                                         

The script engine is not recognizing the truncate syntax on the column commands:

column event format a25 truncate

column machine format a25 truncate

column username format a10 truncate

So that is bug #1 - which caused these errors:

unknown command “sqlplus” - rest of line ignored.

unknown command “sqlplus” - rest of line ignored.

unknown command “sqlplus” - rest of line ignored.

Seems that Toad has truncate problem in sqlplus editor

This was mine first sentence in topic...

:slight_smile:

Our parser chokes on truncate, so Bert do you think the above would get right if we fix this?

Andre

We’ll fix it anyway (issue QP-417).

yes - we should fix it first in the parser. Then Greg may need to verify that our script engine handles it correctly (i.e. does what it’s supposed to). But fix it first in parser nonetheless :slight_smile: