troubleshooting Question

Output a variable value via a SELECT statement.

Avatar of dineth
dinethFlag for United States of America asked on
Oracle Database
6 Comments1 Solution1471 ViewsLast Modified:
Hi All,

I'm new to PL/SQL and attempting to convert rows to columns. I have succeeded doing so by using the code below. However, I need to output the variable value via a SELECT statement rather than using a dbms_output.put_line( ) call so that the value could be consumed by C#.
There seems to be some syntax error in the way I'm using the SELECT statement. Appreciate your help !

Thanks,
- D
DECLARE str VARCHAR2(50);
        text VARCHAR2(100);
        CURSOR cur is SELECT DISTINCT time_period 
              FROM s1.TIME_PERIOD_LOOKUP 
              WHERE period_type = 'Q'
              ORDER BY time_period;
BEGIN 
  OPEN cur;
  LOOP
    FETCH cur into str;
    EXIT WHEN cur%NOTFOUND;    
    text:= text || '''' || str || '''' || ',';    
  END LOOP;
  
  CLOSE cur;
  
  text:= substr(text, 0, length(text)-1);
  dbms_output.put_line(text);
END;
 
SELECT text as Text FROM dual;  /* Need to output the 'text' variable via a SELECT statement*/
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
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 6 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