How to return a record set from a Function in Oracle

Question

I am trying to get the record data using function by passing values

please find the below

CREATE TABLE "TEST" 
   (    "TEST_ID" NUMBER(9,0) NOT NULL ENABLE, 
    "TEST_DESC" VARCHAR2(30 BYTE), 
    "TEST_DATE" DATE
); 

create or replace TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID   NUMBER(9),
TEST_DESC VARCHAR(30),
dates date
);

create or replace TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE;

Using the above object and table type created the function as follows

create or replace FUNCTION GET_ROWS(dates date)RETURN TEST_TABTYPE
AS
V_Test_Tabtype Test_TabType;
table_name varchar2(30);
q1 varchar2(300);
BEGIN

            table_name :='Test';

                q1 := 'SELECT TEST_OBJ_TYPE(A.TEST_ID, A.TEST_DESC)FROM' || '
                      (SELECT TEST_ID, TEST_DESC FROM ' || table_name || ' where 
                       TEST_DATE = '''||dates||''' ) A';
            dbms_output.put_line(q1); 
            EXECUTE IMMEDIATE  q1  BULK COLLECT INTO V_Test_TabType ;

            RETURN V_Test_TabType;

           EXCEPTION
           WHEN OTHERS THEN
           v_Test_TabType.DELETE;
           RETURN v_Test_TabType;
END;

When I execute this the SQL is printing correctly but not giving the record value.

Error as follows:
select (GET_ROWS('01-08-18')) from dual
Error report -
ORA-02315: incorrect number of arguments for default constructor
ORA-06512: at "AMTEL_MIS.GET_ROWS", line 13

SELECT TEST_OBJ_TYPE(A.TEST_ID, A.TEST_DESC) FROM (SELECT TEST_ID, TEST_DESC FROM Test where TEST_DATE = '01-08-18' ) A

Please assist me further

Thanks in advance

Answer 1

Your type TEST_OBJ_TYPE is defined with three attributes: TEST_ID, TEST_DESC, DATES. However, your query populates the constructor with just two columns:

SELECT TEST_OBJ_TYPE(A.TEST_ID, A.TEST_DESC) FROM

You're missing a value for DATES and that's why Oracle hurls ORA-02315.

I have tried as per your suggestion but it's is giving me an error
ORA-00904: "A"."DATES": invalid identifier

Because of the convoluted way your function is written you need to include TEST_DATE (or dates) in both the subquery and the object constructor:

 q1 := 'SELECT TEST_OBJ_TYPE(A.TEST_ID, A.TEST_DESC,A.TEST_DATE)FROM' || '  -- here!
             (SELECT TEST_ID, TEST_DESC, TEST_DATE FROM '  -- and here!
              || table_name || ' where TEST_DATE = '''||dates||''' ) A'; 

If you do that your code will work. Here is a LiveSQL demo of your code with the fix. (Free Oracle login required).

As it seems likely that you will want to pass in the table name so here is a version of your code which does that:

create or replace function get_rows(dates date, p_table_name in varchar2) 
    return test_tabtype   
as   
    v_test_tabtype test_tabtype;   
    q1 varchar2(300);   
begin   

    q1 := 'select test_obj_type(a.test_id, a.test_desc,a.test_date) from' 
             || '(select test_id, test_desc, test_date from ' 
             || p_table_name 
             || ' where test_date = :1 ) a';   
    dbms_output.put_line(q1);    

    execute immediate  q1  
        bulk collect into v_test_tabtype
        using dates ;   

    return v_test_tabtype;   

exception   
     when others then   
        v_test_tabtype.delete;   
        return v_test_tabtype;   
end;

Note how much easier it is to understand the code when it is laid out with consistent use of case and regular indentation. Readability is a feature!