manipulate date before returning as REF CURSOR to .NET

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.

Solution

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.