[dineshyadav]

Hi,

I have a table which has three Columns Col A, Col B, COl C. I want to select values in Column Col A into a Single Variable variable Val with a delimiter of my choice. I want to do this without opening a Cursor the reason being in the stored procedure where I want this query inserted as I already have two cursors open and I don't want a third cursor in the procedure. The database version I am using is 8i. I iniitally had the same problem in MS SQL and it was sorted using COALESCE. Do we have any equivalent in Oracle.
Please help me.

Regards
Dinesh

[andrewst]

Originally posted by dineshyadav
Hi,

I have a table which has three Columns Col A, Col B, COl C. I want to select values in Column Col A into a Single Variable variable Val with a delimiter of my choice. I want to do this without opening a Cursor the reason being in the stored procedure where I want this query inserted as I already have two cursors open and I don't want a third cursor in the procedure. The database version I am using is 8i. I iniitally had the same problem in MS SQL and it was sorted using COALESCE. Do we have any equivalent in Oracle.
Please help me.

Regards
Dinesh

Try this, if your query will return just one row:

declare
  delim varchar2(1) := '|';
  var varchar2(100);
begin
  select a||delim||b||delim||c into var from table where ...;
end;

Not sure what your thinking is about not opening a third cursor. However you select data, a cursor is always used. It's just that sometimes the cursor is implicit so you don't have to name it or open/close it explicitly.