Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    29

    Unanswered: Selecting Values in a Column as a Single Variable

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Selecting Values in a Column as a Single Variable

    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:

    Code:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •