Hi,

thanks... I was thinking in that option... Im going to test and I´ll give you a feedback.

Had public DB link from Oracle to SQL Server. Select query worked fine, but the same query embedded in stored procedure gave error about not being able to find the remote public object. Spent almost an entire day banging on this with no solution. Turns out the SQL Server view I was querying contained field names that exceeded 30 chars. Apparently Oracle doesn't appreciate that. Created new view with only the fields need and none that exceeded 30 chars. Procedure now compiles with no error and works as intended.

we are getting the error which are mentioned below. Please look into it and suggest.

ORA-04052: error occurred when looking up remote object OSC.REF_ACCOUNTS@OSCT.EMRSN.ORG
ORA-00604: error occurred at recursive SQL level 1
ORA-03106: fatal two-task communication protocol error
ORA-02063: preceding line from OSCT

SQL> select * from dual@OSCT.EMRSN.ORG;

D
-
X

Source DB GDMD version is - 12.1.0.2.0 (DBlink created in this db)
Target DB OSCT version is - 12.2.0.1.0 (connected to this database)


Below are the code which we have ran in our database.

declare
CURSOR lookup_frdw_from_osc(in_customer_number VARCHAR2) IS
SELECT addr.source_system_reference_value -- acct.org_deo_gdm_id
FROM osc.ref_accounts@osct acct,
osc.ref_addresses@osct addr
WHERE addr.source_system_reference_value = in_customer_number
AND addr.osc_object_party_id = acct.osc_acct_party_id;
v_frdw_customer_number VARCHAR2(38);
v_customer_number VARCHAR2(10) := '10000';
begin
v_frdw_customer_number := NULL;
-- Get GDM ID from OSC to make them in sync. Can run process to sync up after this table is setup
OPEN lookup_frdw_from_osc('10000');
FETCH lookup_frdw_from_osc INTO v_frdw_customer_number;
IF lookup_frdw_from_osc%NOTFOUND THEN
v_frdw_customer_number := NULL;
END IF;
CLOSE lookup_frdw_from_osc;
dbms_output.put_line('done');
end;
/

Regards
Lalit Saini