PL/SQL ORA-00932: inconsistent datatypes: expected - got -

Question

I get the following error with my function (ORA-00932: inconsistent datatypes: expected - got -) Can anyone help me, what I have done wrong?

I would be grateful for any advice.

Kind Regards

Jegor

CREATE TABLE TEST_TABLE (ID NUMBER, COLUMN1 VARCHAR2(200));

Insert into TEST_TABLE(ID, COLUMN1) Values(1,'200:300');
Insert into TEST_TABLE(ID, COLUMN1) Values(2,'5');


create or replace TYPE  "DUMMY_RECORD_TEST" as object (
    ID                           NUMBER,
    COLUMN_LIST                  varchar2(100))

create or replace TYPE  "DUMMY_TABLE_TEST" as table of DUMMY_RECORD_TEST


FUNCTION DUMMYFUNCTION(
    p_id_name             in VARCHAR2,
    p_column_name         in VARCHAR2,
    p_tablename           in VARCHAR2,
    p_type                in VARCHAR2,
    p_delimter            in VARCHAR2
)
return DUMMY_TABLE_TEST

as 

sql_qry     VARCHAR2(4000);
csr         SYS_REFCURSOR;  
dummy_records DUMMY_TABLE_TEST;

BEGIN   

    sql_qry := 'Select DUMMY_RECORD_TEST(' || p_id_name || ',
      trim(regexp_substr(' || p_column_name || ',''[^:]+'', 1, lines.column_value)))
        From ' || p_tablename || ',
             Table (CAST (Multiset( Select 
                                        Level 
                                    From dual 
                                    Connect by instr(' || p_column_name || ','':'', 1, Level - 1) > 0
                        ) as sys.ODCIVARCHAR2LIST 
                    )
                ) lines fetch first 1 rows only';

    Open csr For sql_qry;
    Loop
        Fetch csr Bulk collect into dummy_records Limit 3;
            Exit When csr%NOTFOUND;
        End Loop;
        Close csr;
        return dummy_records;
    END DUMMYFUNCTION;

My Select Statement

 Select 
     * 
 From table(DUMMYFUNCTION(p_id_name=>'ID', p_column_name=>'COLUMN1', p_tablename=>'TEST_TABLE', p_type=>'ODCIVARCHAR2LIST', p_delimter=>':'))

Answer 1

You are fetching two scalar columns into an object type. Oracle doesn't know how to map those columns to that object type. You have to use the object constructor in your query:

sql_qry := 'Select DUMMY_RECORD_TEST(' || p_id_name || ',
  trim(regexp_substr(' || p_column_name || ',''[^:]+'', 1, lines.column_value))
                                    )
    From ' || p_tablename || ',

Answer 2

Okay,i have rewritten my Query and everything works now. Thanks for the help :)

CREATE TABLE TEST_TABLE (ID NUMBER, COLUMN1 VARCHAR2(200));

Insert into TEST_TABLE(ID, COLUMN1) Values(1,'200:300');
Insert into TEST_TABLE(ID, COLUMN1) Values(2,'5');


create or replace TYPE  "DUMMY_RECORD_TEST" as object (
    ID                           NUMBER,
    COLUMN_LIST                  varchar2(100))

create or replace TYPE  "DUMMY_TABLE_TEST" as table of DUMMY_RECORD_TEST


FUNCTION DUMMYFUNCTION(
    p_id_name             in VARCHAR2,
    p_column_name         in VARCHAR2,
    p_tablename           in VARCHAR2,
    p_type                in VARCHAR2,
    p_delimter            in VARCHAR2
)
return DUMMY_TABLE_TEST

as 
  
dummy_records DUMMY_TABLE_TEST;

BEGIN   

      Execute immediate
      'Select DUMMY_RECORD_TEST(' || p_id_name || ',
                trim(regexp_substr(' || p_column_name || ',''[^:]+'', 1, lines.column_value)))
       From TEST_TABLE,
       Table (CAST (Multiset( Select 
                                   Level 
                              From dual 
                              Connect by instr(' || p_column_name || ',''' || p_delimter || ''', 1, Level - 1) > 0
                    ) as sys.' || p_type ||' 
                )
            ) lines'
            Bulk collect into dummy_records; 

       For indx in 1 .. dummy_records.COUNT
       Loop 
          Pipe row (dummy_records (indx));
       End Loop;
END DUMMYFUNCTION;