troubleshooting Question

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

Avatar of toooki
toooki asked on
Oracle DatabaseDatabases
5 Comments1 Solution469 ViewsLast Modified:
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%'));
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros