List Oracle multiple rows into one w/o a primary key field.

I want to write the output of the below sql in a variable (varchar2 2000) inside a Oracle procedure. This could return multiple rows. How could I concatenate this into one -- so that I could always assign the o/p to the variable.
I do not see any primary key to LISTAGG on...

SELECT 'username: ' || s.username ||
        ', osuser: ' || s.osuser ||
        ', machine: ' || s.osuser ||
        ', sid: ' || s.sid ||
        ', serial#-program-terminal-sessinfo: ' || s.serial#||' '||s.program||'@'||s.terminal ||
        ', blocking_session: ' || s.blocking_session ||
        ', seconds_in_wait: ' || s.seconds_in_wait ||
        ', sql_text: ' || l.sql_text 
        FROM v$session s, v$sql l 
        WHERE s.sql_id = l.sql_id(+)
        AND ((lower(l.sql_text) LIKE '%mytab1%') OR  (lower(l.sql_text) LIKE '%mytab2%'));

Open in new window

toookiAsked: 2011-10-06

Who is Participating?

LVL 79

slightwv (䄆 Netminder) Commented: 2011-10-07

Just take the first 2000 characters:

Insert into mylogTab (LogText) values ( substr(v_LockText,1,2000));

toookiAuthor Commented: 2011-10-06

Is sid the Primary key in v$session .. if it is.. then not sure if the attached sql could work.
Also is there any way to limit the o/p to less than 2000 character..?

SELECT NVL(substr(TRIM(session_info), 1, 1500),
                   'No CWV table session detected')
          INTO v_LockText
          FROM (SELECT one,
                       LISTAGG(session_info, ';') WITHIN GROUP(ORDER BY one) as session_info
                  FROM (SELECT '1' as one,
                               'username: ' || s.username || ', osuser: ' ||
                               s.osuser || ', machine: ' || s.osuser ||
                               ', sid: ' || s.sid ||
                               ', serial#-program-terminal-sessinfo: ' ||
                               s.serial# || ' ' || s.program || '@' ||
                               s.terminal || ', blocking_session: ' ||
                               s.blocking_session || ', seconds_in_wait: ' ||
                               s.seconds_in_wait || ', sql_text: ' ||
                               substr(TRIM(l.sql_text), 1, 200) session_info
                          FROM v$session s, v$sql l
                         WHERE s.sql_id = l.sql_id(+)
                           AND ((upper(l.sql_text) LIKE '%mytab1%') OR
                               (upper(l.sql_text) LIKE '%mytab2%')))
                 GROUP BY one);

Open in new window

LVL 79

slightwv (䄆 Netminder) Commented: 2011-10-06

What is the final result going to be used for?
Can you maybe use XML as output and not a CSV?

toookiAuthor Commented: 2011-10-06

Thank you.
In my Oracle procedure I wrote an exception block:

CREATE OR REPLACE PROCEDURE myProc
...
...

BEGIN

--Truncate a table MyTab

EXCEPTION
WHEN TIMEOUT_ON_RESOURCE THEN

---Above SELECT INTO v_LockText query.

Insert into mylogTab (LogText) values ( v_LockText);
--LogText in the log table is varchar2(2000).

END;

Not sure how XML field works...