Tuesday, February 24, 2009

oracle returning record type

oracle select from a RECORD
oracle cursor RECORD TYPE
oracle procedure record argument
oracle RECORD
oracle RECORD cursor
oracle RECORD table cursor
oracle RECORD to cursor
oracle RECORDs
oracle return data from a record
oracle returning record
oracle returning record object
oracle returning record type
OracleDataAdapter record
OracleDataAdapter record type
returning a record
sql cursor “return just one record”
sql type for record

Did that make sense?

Those are, quite simply, the various searches I attempted yesterday trying to find a way to work with an Oracle PL/SQL procedure that returned a record type as an out parameter.

Apparently, it’s impossible.

ODP.Net provides no way of working with one.
Interestingly, there is no mention of in any of the references as to the impossibility of the task either. There’s tons of stuff about getting data into records but nothing about getting it out. Guess people don’t write about stuff they couldn’t do.

So anyway, the workaround:

FUNCTION readData(
InputParam1 IN VARCHAR2,
InputParam2 IN NUMBER,
CurForRcrd1 OUT SYS_REFCURSOR,
CurForRcrd2 OUT SYS_REFCURSOR
)
RETURN NUMBER
AS
LocalVrbl1 NUMBER := 0;
Rcrd1 RecType1;
Rcrd2 RecType2;
BEGIN
• After initial processing and
• populating the record objects
• The simple workaround
• Select data from record into cursor
OPEN CurForRcrd1
FOR
SELECT
Rcrd1.RecMember1,
Rcrd1.RecMember2,
Rcrd1.RecMember3,
Rcrd1.RecMember4,
Rcrd1.RecMember5
FROM DUAL;
OPEN CurForRcrd2
FOR
SELECT
Rcrd2.RecMember6,
Rcrd2.RecMember7,
Rcrd2.RecMember8,
Rcrd2.RecMember9
FROM DUAL;
END readData;

No comments:

Post a Comment