CODEWITHSUNDEEP
oracle-databasetypescastingplsqlcursor
Xophmeister
Xophmeister

Reputation: 9211

Casting Oracle cursor row into user-defined record type for a pipelined function

In my package, I have defined a record type and a corresponding table type. I then have a pipelined function that opens a cursor and attempts to pipe each result out. The problem is that it's giving me type mismatch errors. I tried to cast the cursor to my record type, but to no avail: What am I doing wrong?

create or replace package myTest as
  type myRec is record(
    id  integer,
    foo varchar2(10)
  );
  type myTable is table of myRec;

  function output() return myTable pipelined;
end myTest;
/

create or replace package body myTest as
  function output() return myTable pipelined
  as
  begin
    for myCur in (
      select id, foo from someTable
    )
    loop
      pipe row(cast(myCur as myRec));
    end loop;

    return;
  end output;
end myTest;
/

Upvotes: 2

Views: 5421

Answers (1)

René Nyffenegger
René Nyffenegger

Reputation: 40499

create or replace package body myTest as
  function output return myTable pipelined
  as
    --   Add a "record" variable":
    xyz  myRec;
  begin
    for myCur in (
      select id, foo from someTable
    )
    loop
      -- Fill the record variable with the
      -- values from the cursor ...
      xyz.id  := myCur.id;
      xyz.foo := myCur.foo;
      -- ... and pipe it:
      pipe row(xyz);
    end loop;

    return;
  end output;
end myTest;

Upvotes: 4

Related Questions