asked on 1/8/2009

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

Last Comment

1/9/2009

1/8/2009

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

1/8/2009

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

1/8/2009

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

1/8/2009

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

dineth

1/8/2009

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

1/9/2009

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