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
sharedAccounts_query
so this doesn't even compile. Not relevant to the error, but why are you using dynamic SQL? – Alex Poole