Pass (comma separated) -yet single parameter to cursor

cursorsoracleplsql

I have to get selection for a given string (with comma separated values) passed to the cursor. There can be multiple values that could be passed via single variable. My code is somewhat like:

CURSOR My_Cursor( vsStr1 )
  IS
  SELECT some_field
    FROM some_table
   WHERE txtfield1 IN ( vsStr1 );    --this field is varchar2 type

vsStr1 varchar2(100) := '01, 25, 80, 100';

How to pass that variable correclty? Any help would be appreciated.

Best Answer

The simplest little dirty trick I know for this is xmltable(). With xmltable()you can convert a comma-seperated list of values into rows, for example:

select * from xmltable('1, 2, 3')

COLUMN_VALUE
------------
1
2
3

Notice how the column is named column_value automatically. This is an XMLType column, that you need to convert to be able to compare it with your regular types, for example (column_value).getnumberval() or (column_value).getstringval()

Now using this, a cursor could be coded like this:

set serveroutput on
declare
  l_parameters varchar2(50) := '101, 102, 103, 104';

  cursor my_cursor (params varchar2)
  is
  select employee_id, first_name, last_name from hr.employees
  where employee_id in
    (select (column_value).getnumberval() from xmltable(params));
begin
  for c in my_cursor(l_parameters) loop
    dbms_output.put_line(c.employee_id || ': ' || c.first_name || ', ' || c.last_name);
  end loop;
end;
/

101: Neena, Kochhar
102: Lex, De Haan
103: Alexander, Hunold
104: Bruce, Ernst

Related Solutions

SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

This uses the same basic technique as jonearles ⁽⁺¹⁾ (since removed) but eliminates the PIVOT and would require only one function that turns the string into a CASE statement.

SELECT substr(
      MIN (
         CASE WHEN Blank=1 THEN '1 Blank' 
              WHEN Error=1 THEN '2 Error' 
              WHEN InProgress=1 THEN '3 InProgress' 
              WHEN Completed=1 THEN '4 Completed' 
         END
         )
   ,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;

I assume the data looks something like this:

drop table m_object;
create table m_object as 
   (select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);

drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);

Here is what the function might look like if it were done in PL/SQL. It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.

set serveroutput on format wrapped

DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';

   Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
      vBuilt     Varchar2(500);
      vWord      Varchar2(100);
      vChar      Char(1);
      vWordCount Number(1) := 1;
   Begin
      For vLoop In 1..Length(pOriginal) Loop
         vChar := substr(pOriginal,vLoop,1);
         If (vChar = ',') Then
            If (vBuilt IS NULL) Then
               vBuilt := 'CASE WHEN ';
            End If;
            vBuilt := vBuilt || vWord || '=1 THEN '''
               || to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
            vWord := '';
            vWordCount := vWordCount + 1;            
         Else                  
            vWord := vWord || vChar;            
         End If;
      End Loop;
      vBuilt := vBuilt || vWord || '=1 THEN ''' 
         || to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
      vBuilt := vBuilt || ' END ';
      Return vBuilt;
   End;

BEGIN
   vPassed := MakeCase(vPassed);
   DBMS_Output.Put_Line(vPassed);

   --Use vPassed here to build SQL statement.
END;
/

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Your procedure is missing two OUT parameters:

PROCEDURE myprocedure (myinput      IN Varchar2, 
                       myretcursor  OUT sys_refcursor,
                       p_count      OUT NUMBER,
                       p_message    OUT VARCHAR2) IS
BEGIN
   OPEN myretcursor FOR
      SELECT * FROM MYTABLE;
   p_count := 123;
   p_message := 'message';
END myprocedure

You would also need to modify your C# code to register the new OUT parameters accordingly.

Best Answer

Related Solutions

SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Related Question