CODEWITHSUNDEEP
oracle-database
rohit thakur
rohit thakur

Reputation: 1

ERROR: Remote Operations Not Permitted on Object Tables or User-Defined Type Columns

Whenever I am trying to fetch data from remote table using db link and put inside the collection variable, I am getting ORA-22804 Error. I don't have access to the remote database.

I tried recreating the type using oid, but still facing the same issue.

create or replace type type_demo as table of varchar2(32767);

CREATE OR REPLACE PROCEDURE PROC_TEST(EMP_ID IN TYPE_DEMO,E_NAME OUT TYPE_DEMO)
AS 
BEGIN
SELECT EMP_NAME BULK COLLECT INTO E_NAME FROM EMP_TABLE@CDM_LINK WHERE EMPLOYEE_ID MEMBER OF EMP_ID;
END;

/

Whenever I am trying to test the above procedure I am getting ORA-22804 Error:

DECLARE
A TYPE_DEMO:=TYPE_DEMO('1001');
B TYPE_DEMO:=TYPE_DEMO();
BEGIN
PROC_TEST(A,B);
FOR I IN 1..B.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(B(i));
END LOOP;
END;

Upvotes: 0

Views: 3090

Answers (1)

g00dy
g00dy

Reputation: 6778

The error message says it all:

ORA-22804 remote operations not permitted on object tables or user-defined type columns

You're trying to do exactly that - remote operation on a user defined type type_demo, over a database link CDM_LINK.

Cheers

Upvotes: 1

Related Questions