?
Solved

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

Posted on 2011-10-06
5
Medium Priority
?
451 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:toooki
  • 3
  • 2
5 Comments
 

Author Comment

by:toooki
ID: 36928092
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

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36928139
What is the final result going to be used for?
Can you maybe use XML as output and not a CSV?
0
 

Author Comment

by:toooki
ID: 36928317
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...
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1900 total points
ID: 36930658
Just take the first 2000 characters:

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

Author Comment

by:toooki
ID: 36933569
Ok, thank you. Will do so.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question