asked on

Output a variable value via a SELECT statement.

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*/

Open in new window

quincydude

Are you making a PL/SQL procedure or function? You can just return the value from the OUT parameter in procedure or directly return the value in function.
Hope this helps.
http://it.toolbox.com/blogs/oracle-guide/learn-plsql-procedures-and-functions-13030

Sujith

You can create a function with your pl/sql code and call it to get the string.
See the sample code below.

SQL> select * from time_period_lookup;
 
TIME_PERIOD          PE
-------------------- --
10                   Q
50                   Q
60                   T
20                   Q
 
SQL> create or replace function test_func
  2  return varchar2
  3  as
  4  str VARCHAR2(50);
  5          text VARCHAR2(100);
  6          CURSOR cur is SELECT DISTINCT time_period 
  7                FROM TIME_PERIOD_LOOKUP 
  8                WHERE period_type = 'Q'
  9                ORDER BY time_period;
 10  begin
 11    OPEN cur;
 12    LOOP
 13      FETCH cur into str;
 14      EXIT WHEN cur%NOTFOUND;    
 15      text:= text || '''' || str || '''' || ',';    
 16    END LOOP;
 17    
 18    CLOSE cur;
 19  
 20    text := rtrim(text, ',');
 21  
 22    return text;
 23  end;
 24  /
 
Function created.
 
SQL> select test_func as text from dual;
 
TEXT
--------------------------------------------------------
'10','20','50'
 
SQL>

Open in new window

dineth

ASKER

Hi Sujith, Thank you so much for the solution. I know this would work however, using a function isn't an option due to political constraints of the project. We do not own the database :( Looking for query based approach which can be passed as an inline SQL statement. Appreciate the post !!

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dineth

ASKER

Hi Sujith,

Thank you very much, this works great. Pretty awesome query this is. Appreciate your help !! Have a great day.

Thanks,
-D

Sujith

You are welcome.

To understand how exactly it works
- include the column rn in the select list
-You can run the inner query first and see the results
- then run the full query without the where rn = cn