manipulate date before returning as REF CURSOR to .NET

0
votes

I have a plsql procedure being called from VB.NET via ODAC. Procedure does a simple select:

cursor C_emp is select name, surname from employees;

Procedure must have an OUT parameter as REF CURSOR.

I know I can do a simple:

declare
  L_cursor sys_refcursor;
begin
  open L_cursor for select name, surname from employees;
  ...
end;

But I need to add a couple of columns to the OUT REF CURSOR, based on some logic from the select statement.

I thought about doing a loop and process one record at a time into a table of type array , then convert it into a REF CURSOR.

How can I do this? Any suggestion is much appreciated, many thanks in advance.

2
oracleplsqlcursor
If you want to add a few columns to the cursor, just add it to you select statement, and return it as usual. - Pavel Smirnov
Yeah I thought about it, but logic is a bit messy and long. - jacknomiz
You can not just add a column to a cursor. You either create a new cursor looping through the first one, or use one single cursor and provide all data you need in it. You should probably explain what is that "some logic". - Pavel Smirnov
Yes, I want to create a new REF CURSOR while looping through my first select cursor. How can I do this? - jacknomiz

2 Answers

0
votes

I'm pretty sure that these calculations can be done inside one select statement with no need to create an extra cursor. But, if you still want it, I'll show you one possible example how to achieve that.

First, you have to declare an object TYPE. (It will represent a row in the result query):

create or replace type MY_TYPE as object (
    name varchar2(64),
    surname varchar2(64),
    calculatedValue varchar2(64) --your calculated column. You may add as many columns as needed, but for simplicity, I'll stick to only one column
);

Now, create a nested table of MY_TYPE objects (it will represent the whole result set):

create or replace type MY_TYPE_LIST as table of MY_TYPE;

Now you're ready to write a pipeline function to return the result set you want.

create or replace function MY_FUNC return MY_TYPE_LIST pipelined as
cursor cur is
    select name, surname from employees;
name varchar2(64);
surname varchar2(64);
calculatedColumn varchar2(64);
begin
  open cur;

  loop
    fetch cur into name, surname;
    exit when cur%notfound;

    --do something here, calculate new columns
    calculatedColumn := ...; --assign a value you want
    pipe row (MY_TYPE(name, surname, calculatedColumn)); --the moment of truth. Pipe a row containing the new column

  end loop;
end;

This function will return a new cursor containing values of MY_TYPE object.

0
votes

The same way you use any logic in a select statement. 

declare
  L_cursor sys_refcursor;
begin
 OPEN l_cursor FOR SELECT name,
     CASE
          WHEN name LIKE 'Jack%' THEN 'Jack the Ripper'
     END AS nick_name,
     surname FROM employees;

...
end;