Thanks for the question, naveen.
Asked: November 08, 2016 - 5:31 pm UTC
Last updated: November 09, 2016 - 10:05 am UTC
Version: 10g
Viewed 1000+ times
You Asked
I have created procedure with the our parameter as type
CREATE OR REPLACE TYPE char_array AS TABLE OF varchar2(32000);
create or replace
procedure GET_INSERTS_PRC(V_TABLE_NAME IN VARCHAR2,OUT_inserts_TYP out char_array );
I can able to run the procedure in the same schema. While I am running the same from other schema through DB link I am facing the below error.
Error report:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'GET_INSERTS_PRC'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Please suggest me the way
and Chris said...
There are various restrictions relating to user-defined types and database links:
You cannot connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table.
You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.
You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.
You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call. http://docs.oracle.com/database/121/ADOBJ/adobjbas.htm#ADOBJ7083 So if you want to call this over the DB link, you need to find a way that doesn't use your char_array type.