CODEWITHSUNDEEP
oracle-databaseplsqlpipeline
serkelion
serkelion

Reputation: 83

Oracle pipelined function with record type

I have a situation like the following:

CREATE OR REPLACE FUNCTION GET_CURSOR_PIPELINE(placeholder IN NUMBER)
RETURN MY_RECORD_TYPE PIPELINED IS

 TYPE CURSOR_TYPE IS REF CURSOR;
 myCursor CURSOR_TYPE;

 TYPE RECORD_TYPE IS RECORD(
   record_id NUMBER, 
   firstname VARCHAR(50)
 );
 resultSet RECORD_TYPE; 

BEGIN

OPEN myCursor FOR

SELECT 1, 'Scott' FROM DUAL
UNION
SELECT 2, 'Tiger' FROM DUAL;

IF (myCursor IS NOT NULL) THEN

  LOOP
      FETCH myCursor INTO resultSet;
      EXIT WHEN myCursor%NOTFOUND;
      PIPE ROW (MY_RECORD_OBJ(
        resultSet.record_id, 
        resultSet.firstname
      ));
  END LOOP;

  CLOSE myCursor;

END IF;

END GET_CURSOR_PIPELINE;

the only difference between my production code and the sample above is that I need to fetch about 20 fields from a real table, and not just 2 fields from DUAL.

I'd like to avoid the boiler code where I have to list esplicitally all the involved fields. The function above works fine, but I have to define ALL the involved fields 3 times. The fist time when I define the return type.

CREATE OR REPLACE TYPE MY_RECORD_OBJ AS OBJECT (
 RECORD_ID NUMBER,
 FIRSTNAME VARCHAR2(50)
);
/
CREATE OR REPLACE TYPE MY_RECORD_TYPE AS TABLE OF MY_RECORD_OBJ;
/

the second time when I define the RECORD type (declare section of the function), and the third time when I push the object in the pipeline (PIPE ROW).

Here it is the question: is there a way to avoid this and writing simply something like this?

PIPE ROW (MY_RECORD_OBJ(resultSet))

Moreover, if the answer was "yes, it is possible", how would the code look when applying to a real table? Should I put a %rowtype label somewhere?

Upvotes: 1

Views: 4515

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132700

How about this:

CREATE OR REPLACE FUNCTION GET_CURSOR_PIPELINE(placeholder IN NUMBER)
RETURN MY_RECORD_TYPE PIPELINED IS

 myCursor SYS_REFCURSOR;

 resultSet MY_RECORD_OBJ; 

BEGIN

  OPEN myCursor FOR
    SELECT MY_RECORD_OBJ(1, 'Scott') FROM DUAL
    UNION ALL
    SELECT MY_RECORD_OBJ(2, 'Tiger') FROM DUAL;

  LOOP
      FETCH myCursor INTO resultSet;
      EXIT WHEN myCursor%NOTFOUND;
      PIPE ROW (resultSet);
  END LOOP;

  CLOSE myCursor;

END GET_CURSOR_PIPELINE;

You can also shrink it further with a cursor FOR LOOP:

CREATE OR REPLACE FUNCTION GET_CURSOR_PIPELINE(placeholder IN NUMBER)
RETURN MY_RECORD_TYPE PIPELINED IS

BEGIN

  FOR myCursor IN
  (
    SELECT MY_RECORD_OBJ(1, 'Scott') my_record FROM DUAL
    UNION ALL
    SELECT MY_RECORD_OBJ(2, 'Tiger') my_record FROM DUAL
  ) LOOP
    PIPE ROW(myCursor.my_record);
  END LOOP;

END GET_CURSOR_PIPELINE;

Upvotes: 7

Related Questions