So, there I am, creating a "special" kill user script (don't ask) for one of
our instances, 8.1.6.0.0 on Solaris. The problem is that some of the output
rows were getting truncated on the output. Here's the proc I had started:
CREATE OR REPLACE PROCEDURE Euthanize AS
v_printline VARCHAR2(140);
CURSOR c_sessions IS
SELECT vs.username, vs.osuser,
--
--REPLACE(vs.machine,CHR(0),NULL) "MACHINE",
vs.machine,
--
, vs.logon_time, vs.last_call_et, vp.SPID
FROM v$session vs, v$process vp
WHERE vs.username IS NOT NULL
AND vs.paddr = vp.addr
ORDER BY vs.last_call_et DESC;
BEGIN
FOR rsess IN c_sessions LOOP
v_printline := rsess.username||'|'||
rsess.osuser||'|'||rsess.machine||'|'||
rsess.logontime||'|'||rsess.idletime||'|'||
rsess.spid||'|'||rsess.logon_time;
dbms_output.put_line(v_printline);
END LOOP;
END Euthanize;
After compiling this, I called it from SQL*Plus using "execute euthanize;".
I noticed that all the DBMS_OUTPUT lines that were truncated were sessions
from Windohs workstations. Examining a SELECT DUMP(machine) FROM V$SESSION
showed that all of the MACHINE columns from Windohs sessions were
zero-terminated. No other client (Solaris) was.
Since the output from DBMS_OUTPUT is being prematurely truncated by this
zero-term'd field, the workaround is to either move the problem field to the
end of the output line, or to use the REPLACE function, as I've commented
out in the above code. The latter is necessary if there is more than one
bastardized field like this or if the order of the columns in the output is
important.
Can anyone reproduce this? The particular clients I found with the problem
are 8.1.7.
TIA!
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
