Обсуждение: Call to package procedure in Oracle not working
We have migrated from Oracle to PostgreSQL and one thing could only be tested in Production and of course it is not working so I am desperate for a solution.
In Oracle (it works here), we had a database link and a synonym to a subroutine in the foreign table package:
- DBLINK to another Oracle database: REMP_MSS
- Package name in REMP_MSS: REMP_API CREATE OR REPLACE SYNONYM "PGXLIMSP"."REMPASS_API" FOR "REMPASS_API"@"REMP_MSS”
- SYNONYM: CREATE OR REPLACE SYNONYM "PGXLIMSP"."REMPASS_API" FOR "REMPASS_API"@"REMP_MSS";
- Call from an Oracle procedure rempass_api.put_line(0,'');
This is what we have in POSTGRESQL:
- Using oci_dblink, a foreign server named REMP_MSS, this link works because we can query tables in this database
- SYNONYM: CREATE OR REPLACE SYNONYM "PGXLIMSV8"."REMPASS_API" FOR "REMPASS_API"@"REMP_MSS”
- Call from Oracle procedure rempass_api.put_line(0,' '); but get the error message “schema REMPASS_API@REMP_MSS does not exist”
In the Oracle database this is the package that has the put_line subroutine
We gave tried several variations of the call but all error out with “schema xyz does not exist”:
- CALL "REMPASS_API@REMP_MSS".put_line(0,'');
- CALL "MSS.REMPASS_API@REMP_MSS ".put_line(0,'');
- CALL "MSS.REMPASS_API@REMP_MSS ".put_line(0,'');
- CALL "rempass_api@remp_mss".put_line(0,'');
Can anyone help?
Best Regards,
Karen
Karen Smalara Senior Principal Software Engineer Boehringer Ingelheim Pharmaceuticals, Inc. 900 Ridgebury Road, Ridgefield, CT 06877 C: +1 804 244 0111 karen.smalara@boehringer-ingelheim.com Facebook | Twitter | Instagram | LinkedIn
|
Вложения
I think you are using EDB's Advanced Server product, and therefore need to contact them for help on this. --------------------------------------------------------------------------- On Fri, Jul 23, 2021 at 05:06:47PM +0000, karen.smalara@boehringer-ingelheim.com wrote: > We have migrated from Oracle to PostgreSQL and one thing could only be tested > in Production and of course it is not working so I am desperate for a solution. > > > > > > In Oracle (it works here), we had a database link and a synonym to a subroutine > in the foreign table package: > > ● DBLINK to another Oracle database: REMP_MSS > ● Package name in REMP_MSS: REMP_API CREATE OR REPLACE SYNONYM > "PGXLIMSP"."REMPASS_API" FOR "REMPASS_API"@"REMP_MSS” > ● SYNONYM: CREATE OR REPLACE SYNONYM "PGXLIMSP"."REMPASS_API" FOR > "REMPASS_API"@"REMP_MSS"; > ● Call from an Oracle procedure rempass_api.put_line(0,''); > > > > This is what we have in POSTGRESQL: > > ● Using oci_dblink, a foreign server named REMP_MSS, this link works because > we can query tables in this database > ● SYNONYM: CREATE OR REPLACE SYNONYM "PGXLIMSV8"."REMPASS_API" FOR > "REMPASS_API"@"REMP_MSS” > ● Call from Oracle procedure rempass_api.put_line(0,' '); but get the error > message “schema REMPASS_API@REMP_MSS does not exist” > > > > In the Oracle database this is the package that has the put_line subroutine > > [cid] > > We gave tried several variations of the call but all error out with “schema xyz > does not exist”: > > ● CALL "REMPASS_API@REMP_MSS".put_line(0,''); > ● CALL "MSS.REMPASS_API@REMP_MSS ".put_line(0,''); > ● CALL "MSS.REMPASS_API@REMP_MSS ".put_line(0,''); > ● CALL "rempass_api@remp_mss".put_line(0,''); > > > > Can anyone help? > > > > Best Regards, > > Karen > > [cid] Karen Smalara > > Senior Principal Software Engineer > > Boehringer Ingelheim Pharmaceuticals, Inc. > > 900 Ridgebury Road, Ridgefield, CT 06877 > > C: +1 804 244 0111 > > karen.smalara@boehringer-ingelheim.com > > Facebook | Twitter | Instagram | LinkedIn > > > > > > > > > This e-mail is confidential and may also be legally privileged. If you are not > the intended recipient please reply to sender, delete the e-mail and do not > disclose its contents to any person. Any unauthorized review, use, disclosure, > copying or distribution is strictly prohibited. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.