0
votes

I have written an Oracle Stored Function where I am trying to read few records from a DB table into a Cursor and set into a Custom Type. But I am getting Error while calling this Function.

Custom object type:

create or replace 
TYPE SHARED_ACCOUNT_TYPE AS object(
    item_account_id NUMBER(11),
    share_by_id NUMBER(11),
    share_to_id NUMBER(11)
  )

Custom table Type:

create or replace 
TYPE SHARED_ACCOUNT_TYPE_COLLECTION
  as table of SHARED_ACCOUNT_TYPE

Main Stored Function:

FUNCTION getUserSharedAccounts(v_user_id IN NUMBER)
  RETURN SHARED_ACCOUNT_TYPE_COLLECTION

  IS
    sharedAccounts_query VARCHAR2(200);
    sharedAccountCollection SHARED_ACCOUNT_TYPE_COLLECTION;
    TYPE SharedAccountCursorType IS REF CURSOR;  -- define weak REF CURSOR type
    shared_account_cursor   SharedAccountCursorType;  -- declare cursor variable

  BEGIN
    shareAccounts_query := 
    'SELECT item_id, share_by_id, share_to_id FROM share_accounts WHERE share_to_id = :s';

    OPEN shared_account_cursor FOR sharedAccount_query USING v_user_id;
    FETCH shared_account_cursor bulk collect into sharedAccountCollection;
    CLOSE shared_account_cursor;

    RETURN sharedAccountCollection;
  END;

I am calling above function like so:

declare
  sharedAccount SHARED_ACCOUNT_TYPE_COLLECTION;
begin
  sharedAccount := PKG_DATA_POINT_UTIL.getUserSharedAccounts(991033632);
end;

Note: All 3 fields fetched from DB table as of Type NUMBER(11).

Error I got is:

ORA-00932: inconsistent datatypes: expected %s got %s

ORA-06512: at"APP.PKG_DATA_HELPER_UTIL", line 1403

1
You've got three variations on the name sharedAccounts_query so this doesn't even compile. Not relevant to the error, but why are you using dynamic SQL?Alex Poole

1 Answers

4
votes

Ignoring the inconsistent naming that prevents this from compiling at all, the error is coming from the fetch, not the open.

The issue here is that you have a target collection of objects but you're selecting scalar values - Oracle can't (or won't) convert them to your object type automagically. So you need to create the object as part of the query, i.e instead of

SELECT item_id, share_by_id, share_to_id

you need

SELECT SHARED_ACCOUNT_TYPE(item_id, share_by_id, share_to_id)

so:

sharedAccounts_query := 
'SELECT SHARED_ACCOUNT_TYPE(item_id, share_by_id, share_to_id) FROM share_accounts WHERE share_to_id = :s';

Incidentally, you don't need to use dynamic SQL or a cursor here; static SQL would be fine:

FUNCTION getUserSharedAccounts(v_user_id IN NUMBER)
  RETURN SHARED_ACCOUNT_TYPE_COLLECTION
  IS
    sharedAccountCollection SHARED_ACCOUNT_TYPE_COLLECTION;
  BEGIN
    SELECT SHARED_ACCOUNT_TYPE(item_id, share_by_id, share_to_id)
    bulk collect into sharedAccountCollection
    FROM share_accounts
    WHERE share_to_id = v_user_id;

    RETURN sharedAccountCollection;
  END;