Oracle Table Function from CTE

Question

I'd like to create a table function in oracle from the following query:

 WITH A AS (
    SELECT * FROM PART_TABLE WHERE PART_NO LIKE part_num
 )
SELECT * FROM A;

'part_num' being a parameter passed into the function. I am having trouble with the syntax. This is what I have tried:

CREATE OR replace FUNCTION part_test_f(search_part IN varchar2)
RETURN part_test_t PIPELINED 
AS 

    rec PART_TEST;

    CURSOR cur(part_num) IS 
         WITH A AS (
            SELECT * FROM PART_TABLE WHERE PART_NO LIKE part_num
         )
        SELECT * FROM A;
BEGIN
        FOR record IN cur(search_part) LOOP
        rec := PART_TEST(record);
        pipe row(rec);
        END LOOP;
return;
END;

I have created the types for the table and the rows. Thanks for any help.

EDIT: I have given it another go. The declaration now looks like:

    create or replace FUNCTION part_test_f(search_part IN varchar2)
RETURN part_test_t PIPELINED
AS

    rec PART_TEST;

    CURSOR cur(part_num varchar2) RETURN PART_TEST IS
         WITH A AS (
            SELECT * FROM F6RD_PART WHERE PART_NO LIKE part_num
         )
        SELECT * FROM A;
BEGIN
        FOR rec IN cur(search_part) LOOP
        pipe row(rec);
        END LOOP;
return;
END;

Now I receive an 'No more data to read from socket' error

Answer 1

While dealing with Objects you be extra cautious while making select. There were errors in your code which were resulting in issues. Also am not very sure if direct assignment done below is actually allowed:

rec := PART_TEST(record);

However I propose 2 solutions here. First with Pipeline and other without it. See below:

--Table and Objects Preparation

CREATE TABLE part_table (
    part_no   NUMBER,
    col1      NUMBER
);

INSERT INTO PART_TABLE VALUES(1,11);
INSERT INTO PART_TABLE VALUES(1,33);
INSERT INTO PART_TABLE VALUES(2,22);

SELECT * FROM PART_TABLE;

CREATE OR REPLACE TYPE part_test IS OBJECT (
    part_no   NUMBER,
    col1      NUMBER
);

CREATE OR REPLACE TYPE part_test_t IS  TABLE OF part_test;
/

--Function with Pipeline

CREATE OR replace FUNCTION part_test_f(search_part IN number)
RETURN part_test_t PIPELINED 
AS 

 rec part_test; --<--Variable of type Object since we want to piperow.

 CURSOR cur(part_num number) IS 
      WITH A AS 
      (       --Make sure you cast your select statement of object type    
              SELECT part_test(PART_NO,col1) FROM PART_TABLE WHERE PART_NO LIKE part_num
       )
       SELECT * FROM A;        
BEGIN        
   OPEN cur(search_part) ;
    LOOP
      Fetch cur into rec;    --<-- Note here am not using `Bulk Collect` even though its being a collection since we are `piping` the rows.    
      exit when cur%NOTFOUND;
       pipe row(rec);         
    END LOOP;
RETURN ;
END;
/

Result:

SQL> SELECT * FROM TABLE (PART_TEST_F(1));

     PART_NO       COL1
    ---------- ----------
      1            11
      1            33

--Without Pipeline

CREATE OR REPLACE FUNCTION part_test_f (search_part IN NUMBER) 
RETURN part_test_t 
 AS
    rec   part_test_t;
    CURSOR cur ( part_num NUMBER) IS 
    WITH a AS 
    ( SELECT part_test( part_no,col1 )
       FROM part_table
       WHERE part_no LIKE part_num       
    ) 
    SELECT *  FROM a;
BEGIN
    OPEN cur(search_part);
    LOOP
        FETCH cur BULK COLLECT INTO rec;
        EXIT WHEN cur%notfound;      
    END LOOP;
    RETURN rec;
END;
/

Result:

SQL> Select * from table (part_test_f(1));

   PART_NO       COL1
---------- ----------
         1         11
         1         33

Choose whichever suits you but we all know the advantages of using Pipeline functions hence that would be best suited.