Synonyms, packages and database links

NormTeamT · January 18, 2011, 10:08am

Afternoon all,

has anyone ever set up a synonym that refers to a package which is
located at the far end of a database link, and then been able to execute
procedures within that package using the synonym?

Something like:

create or replace database link fred
connect to norm identified by secret
using ‘fred.world’;

create synonym mypackage
for mypackage@fred;

begin
fred.myProcedure(parameter, parameter …);
end;

Everything works fine up until the attempt to execute the code, where I
get an error “ORA-00904: “SQLTEST”.“TEST”: invalid identifier”.

On the other hand, I can do this without problems:

begin
myPackage.myProcedure@fred(parameter, parameter …);
end;

Unfortunately, I need to be able to execute the code via the synonyms
as we intend to move things around and don’t want the users to have to
edit all their reports each time we move stuff.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

KevinAngus · January 18, 2011, 10:28am

Hi Norm,

I think your block should be:

BEGIN

mypackage.myprocedue(param, param…);

END;

i.e. use the synonym name for the package rather than the link name.

Kevin.

On 18 Jan 2011, at 16:08, Dunbar, Norman (Capgemini) wrote:

Afternoon all,

has anyone ever set up a synonym that refers to a package which is
located at the far end of a database link, and then been able to execute
procedures within that package using the synonym?

Something like:

create or replace database link fred
connect to norm identified by secret
using 'fred.world';

create synonym mypackage
for mypackage@fred;

begin
fred.myProcedure(parameter, parameter ...);
end;

Everything works fine up until the attempt to execute the code, where I
get an error "ORA-00904: "SQLTEST"."TEST": invalid identifier".

On the other hand, I can do this without problems:

begin
myPackage.myProcedure@fred(parameter, parameter ...);
end;

Unfortunately, I *need* to be able to execute the code via the synonyms
as we intend to move things around and don't want the users to have to
edit all their reports each time we move stuff.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally
privileged. If you have received this message by mistake, please notify the
sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should
still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to
under the Freedom of Information Act, Data Protection Act or for litigation.
Email messages and attachments sent to or from any Environment Agency
address may also be accessed by someone other than the sender or recipient,
for business purposes.

If we have sent you information and you wish to use it please read our terms
and conditions which you can get by calling us on 08708 506 506. Find out
more about the Environment Agency at www.environment-agency.gov.uk

NormTeamT · January 18, 2011, 10:39am

I have a “work around”, from Oracle. It’s daft!

Drop the synonyms for the remote package(s). (!)
Create a local package containing all the required procedures and
functions, with exactly the same names as the remote ones, and have them
do nothing other than call the remote code passing over any supplied
parameters and receiving any returns.

I’ve tested this and it works. However, it raises the question “if
Oracle allows me to create a synonym for a remote package, if I can’t
call code within it, what’s the point of creating the synonym?”

Sigh!

Cheers,
Norm. [TeamT]

Norman Dunbar
Contract Senior Oracle DBA
Capgemini Database Team (EA)
Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Guest · January 18, 2011, 11:01am

Message from: Kevin Angus

Hi Norm,

The following works for me: (10.2.0.3)

CREATE DATABASE LINK kevlink CONNECT TO fred IDENTIFIED BY pw USING
‘fred’ ;

SELECT mypackage . myfunction@kevlink FROM dual ;

CREATE OR REPLACE SYNONYM mypackage FOR mypackage@kevlink ;

SELECT mypackage . myfunction FROM dual ;

Should your block be:

BEGIN

mypackage.myprocedue(param, param…);

END;

i.e. use the synonym name for the package rather than the link name.

Kevin.

Historical Messages

Author: Kevin Angus
Date: Tue Jan 18 09:01:09 PST 2011
Hi Norm,

The following works for me: (10.2.0.3)

CREATE DATABASE LINK kevlink CONNECT TO fred IDENTIFIED BY pw USING
‘fred’ ;

SELECT mypackage . myfunction@kevlink FROM dual ;

CREATE OR REPLACE SYNONYM mypackage FOR mypackage@kevlink ;

SELECT mypackage . myfunction FROM dual ;

Should your block be:

BEGIN

mypackage.myprocedue(param, param…);

END;

i.e. use the synonym name for the package rather than the link name.

Kevin.

__

Author: Norman Dunbar
Date: Tue Jan 18 08:08:08 PST 2011
Afternoon all, has anyone ever set up a synonym that refers to a package which
is located at the far end of a database link, and then been able to execute
procedures within that package using the synonym? Something like: create or
replace database link fred connect to norm identified by secret using
‘fred.world’; create synonym mypackage for mypackage@fred; begin
fred.myProcedure(parameter, parameter …); end; Everything works fine up until
the attempt to execute the code, where I get an error “ORA-00904:
“SQLTEST”.“TEST”: invalid identifier”. On the other hand, I can do this without
problems: begin myPackage.myProcedure@fred(parameter, parameter …); end;
Unfortunately, I need to be able to execute the code via the synonyms as we
intend to move things around and don’t want the users to have to edit all their
reports each time we move stuff. Cheers, Norm. [TeamT] Information in this
message may be confidential and may be legally privileged. If you have received
this message by mistake, please notify the sender immediately, delete it and do
not copy it to anyone else. We have checked this email and its attachments for
viruses. But you should still check any attachment before opening it. We may
have to make this message and any reply to it public if asked to under the
Freedom of Information Act, Data Protection Act or for litigation. Email
messages and attachments sent to or from any Environment Agency address may also
be accessed by someone other than the sender or recipient, for business
purposes. If we have sent you information and you wish to use it please read our
terms and conditions which you can get by calling us on 08708 506 506. Find out
more about the Environment Agency at www.environment-agency.gov.uk
__

KevinAngus · January 18, 2011, 11:01am

CREATE DATABASE LINK kevlink CONNECT TO fred IDENTIFIED BY pw USING ‘fred’;

SELECT mypackage.myfunction@kevlink FROM dual;

CREATE OR REPLACE SYNONYM mypackage FOR mypackage@kevlink;

SELECT mypackage.myfunction FROM dual;

BEGIN

mypackage.myprocedue(param, param…);

END;

i.e. use the synonym name for the package rather than the link name.

Hi Norm,

The following works for me: (10.2.0.3)

Should your block be:

Kevin.

NormTeamT · January 19, 2011, 1:49am

Morning Kevin,

I think your block should be:

BEGIN
mypackage.myprocedue(param, param...);
END;

i.e. use the synonym name for the package rather than the link name.

That's exactly what I want to do, unfortunately, Oracle won't allow it
to be done.

The only (?) way I can find to call a packaged procedure over a db link
is directly, as in:

BEGIN
mypackage.myprocedure@mydb_link(param, param...);
END;

Which is no good to me at all.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Guest · January 19, 2011, 3:03am

Message from: Kevin Angus

Morning Norm,

That’s weird, it works for me. What versions are your databases?

I created a package on the remote database (11.2.0.1):

CREATE OR REPLACE PACKAGE linktest AS

PROCEDURE myproc ;

END linktest ;

/

CREATE OR REPLACE PACKAGE BODY linktest AS

PROCEDURE myproc IS

BEGIN

NULL;

END myproc ;

END linktest ;

/

I then did the following on my local database (10.2.0.3):

sql>CREATE DATABASE LINK kevlink CONNECT TO remote_user IDENTIFIED BY pw
USING ‘remote_db’;

Database link created.

sql>CREATE OR REPLACE SYNONYM linktest_syn FOR linktest@kevlink ;

Synonym created.

sql>BEGIN
2 linktest_syn.myproc;
3 END;
4 /

PL/SQL procedure successfully completed.

Does that test case not work for you?

Kevin.

Historical Messages

Author: Kevin Angus
Date: Wed Jan 19 01:03:24 PST 2011
Morning Norm,

That’s weird, it works for me. What versions are your databases?

I created a package on the remote database (11.2.0.1):

CREATE OR REPLACE PACKAGE linktest AS

PROCEDURE myproc ;

END linktest ;

/

CREATE OR REPLACE PACKAGE BODY linktest AS

PROCEDURE myproc IS

BEGIN

NULL;

END myproc ;

END linktest ;

/

I then did the following on my local database (10.2.0.3):

sql>CREATE DATABASE LINK kevlink CONNECT TO remote_user IDENTIFIED BY pw
USING ‘remote_db’;

Database link created.

sql>CREATE OR REPLACE SYNONYM linktest_syn FOR linktest@kevlink ;

Synonym created.

sql>BEGIN
2 linktest_syn.myproc;
3 END;
4 /

PL/SQL procedure successfully completed.

Does that test case not work for you?

Kevin.

__

Author: Norman Dunbar
Date: Tue Jan 18 23:49:58 PST 2011
Morning Kevin, >> I think your block should be: >> >> BEGIN >>
mypackage.myprocedue(param, param…); >> END; >> >> i.e. use the synonym name
for the package rather than the link name. That’s exactly what I want to do,
unfortunately, Oracle won’t allow it to be done. The only (?) way I can find
to call a packaged procedure over a db link is directly, as in: BEGIN
mypackage.myprocedure@mydb_link(param, param…); END; Which is no good to me at
all. Cheers, Norm. [TeamT] Information in this message may be confidential and
may be legally privileged. If you have received this message by mistake, please
notify the sender immediately, delete it and do not copy it to anyone else. We
have checked this email and its attachments for viruses. But you should still
check any attachment before opening it. We may have to make this message and any
reply to it public if asked to under the Freedom of Information Act, Data
Protection Act or for litigation. Email messages and attachments sent to or from
any Environment Agency address may also be accessed by someone other than the
sender or recipient, for business purposes. If we have sent you information and
you wish to use it please read our terms and conditions which you can get by
calling us on 08708 506 506. Find out more about the Environment Agency at
www.environment-agency.gov.uk
__

Author: Kevin Angus
Date: Tue Jan 18 09:01:09 PST 2011
Hi Norm,

The following works for me: (10.2.0.3)

CREATE DATABASE LINK kevlink CONNECT TO fred IDENTIFIED BY pw USING
‘fred’ ;

SELECT mypackage . myfunction@kevlink FROM dual ;

CREATE OR REPLACE SYNONYM mypackage FOR mypackage@kevlink ;

SELECT mypackage . myfunction FROM dual ;

Should your block be:

BEGIN

mypackage.myprocedue(param, param…);

END;

i.e. use the synonym name for the package rather than the link name.

Kevin.

__

Author: Kevin Angus
Date: Tue Jan 18 09:01:09 PST 2011
Message from: Kevin Angus Hi Norm, The following works for me: (10.2.0.3) CREATE
DATABASE LINK kevlink CONNECT TO fred IDENTIFIED BY pw USING ‘fred’ ;
SELECT mypackage . myfunction@kevlink FROM dual ; CREATE OR REPLACE SYNONYM
mypackage FOR mypackage@kevlink ; SELECT mypackage . myfunction FROM dual ;
Should your block be: BEGIN mypackage.myprocedue(param, param…); END; i.e. use
the synonym name for the package rather than the link name. Kevin.
_______________________________________ Historical Messages Author: Kevin Angus
Date: Tue Jan 18 09:01:09 PST 2011 Hi Norm, The following works for me:
(10.2.0.3) CREATE DATABASE LINK kevlink CONNECT TO fred IDENTIFIED BY pw USING
‘fred’ ; SELECT mypackage . myfunction@kevlink FROM dual ; CREATE OR
REPLACE SYNONYM mypackage FOR mypackage@kevlink ; SELECT mypackage . myfunction
FROM dual ; Should your block be: BEGIN mypackage.myprocedue(param, param…);
END; i.e. use the synonym name for the package rather than the link name. Kevin.
__ Author: Norman Dunbar Date: Tue Jan 18 08:08:08 PST 2011 Afternoon all, has
anyone ever set up a synonym that refers to a package which is located at the
far end of a database link, and then been able to execute procedures within that
package using the synonym? Something like: create or replace database link fred
connect to norm identified by secret using ‘fred.world’; create synonym
mypackage for mypackage@fred; begin fred.myProcedure(parameter, parameter …);
end; Everything works fine up until the attempt to execute the code, where I get
an error “ORA-00904: “SQLTEST”.“TEST”: invalid identifier”. On the other hand, I
can do this without problems: begin myPackage.myProcedure@fred(parameter,
parameter …); end; Unfortunately, I need to be able to execute the code via
the synonyms as we intend to move things around and don’t want the users to have
to edit all their reports each time we move stuff. Cheers, Norm. [TeamT]
Information in this message may be confidential and may be legally privileged.
If you have received this message by mistake, please notify the sender
immediately, delete it and do not copy it to anyone else. We have checked this
email and its attachments for viruses. But you should still check any attachment
before opening it. We may have to make this message and any reply to it public
if asked to under the Freedom of Information Act, Data Protection Act or for
litigation. Email messages and attachments sent to or from any Environment
Agency address may also be accessed by someone other than the sender or
recipient, for business purposes. If we have sent you information and you wish
to use it please read our terms and conditions which you can get by calling us
on 08708 506 506. Find out more about the Environment Agency at
www.environment-agency.gov.uk __ _______________________________________
__

Author: Norman Dunbar
Date: Tue Jan 18 08:39:21 PST 2011
I have a “work around”, from Oracle. It’s daft! 1. Drop the synonyms for the
remote package(s). (!) 2. Create a local package containing all the required
procedures and functions, with exactly the same names as the remote ones, and
have them do nothing other than call the remote code passing over any supplied
parameters and receiving any returns. I’ve tested this and it works. However, it
raises the question “if Oracle allows me to create a synonym for a remote
package, if I can’t call code within it, what’s the point of creating the
synonym?” Sigh! Cheers, Norm. [TeamT] Norman Dunbar Contract Senior Oracle DBA
Capgemini Database Team (EA) Internal : 7 28 2051 External : 0113 231 2051
Information in this message may be confidential and may be legally privileged.
If you have received this message by mistake, please notify the sender
immediately, delete it and do not copy it to anyone else. We have checked this
email and its attachments for viruses. But you should still check any attachment
before opening it. We may have to make this message and any reply to it public
if asked to under the Freedom of Information Act, Data Protection Act or for
litigation. Email messages and attachments sent to or from any Environment
Agency address may also be accessed by someone other than the sender or
recipient, for business purposes. If we have sent you information and you wish
to use it please read our terms and conditions which you can get by calling us
on 08708 506 506. Find out more about the Environment Agency at
www.environment-agency.gov.uk
__

Author: Kevin Angus
Date: Tue Jan 18 08:28:12 PST 2011
Hi Norm, I think your block should be: BEGIN mypackage.myprocedue(param,
param…); END; i.e. use the synonym name for the package rather than the link
name. Kevin. On 18 Jan 2011, at 16:08, Dunbar, Norman (Capgemini) wrote:
Afternoon all, has anyone ever set up a synonym that refers to a package which
is located at the far end of a database link, and then been able to execute
procedures within that package using the synonym? Something like: create or
replace database link fred connect to norm identified by secret using
‘fred.world’; create synonym mypackage for mypackage@fred; begin
fred.myProcedure(parameter, parameter …); end; Everything works fine up until
the attempt to execute the code, where I get an error “ORA-00904:
“SQLTEST”.“TEST”: invalid identifier”. On the other hand, I can do this without
problems: begin myPackage.myProcedure@fred(parameter, parameter …); end;
Unfortunately, I need to be able to execute the code via the synonyms as we
intend to move things around and don’t want the users to have to edit all their
reports each time we move stuff. Cheers, Norm. [TeamT] Information in this
message may be confidential and may be legally privileged. If you have received
this message by mistake, please notify the sender immediately, delete it and do
not copy it to anyone else. We have checked this email and its attachments for
viruses. But you should still check any attachment before opening it. We may
have to make this message and any reply to it public if asked to under the
Freedom of Information Act, Data Protection Act or for litigation. Email
messages and attachments sent to or from any Environment Agency address may also
be accessed by someone other than the sender or recipient, for business
purposes. If we have sent you information and you wish to use it please read our
terms and conditions which you can get by calling us on 08708 506 506. Find out
more about the Environment Agency at www.environment-agency.gov.uk
__

Author: Norman Dunbar
Date: Tue Jan 18 08:08:08 PST 2011
Afternoon all, has anyone ever set up a synonym that refers to a package which
is located at the far end of a database link, and then been able to execute
procedures within that package using the synonym? Something like: create or
replace database link fred connect to norm identified by secret using
‘fred.world’; create synonym mypackage for mypackage@fred; begin
fred.myProcedure(parameter, parameter …); end; Everything works fine up until
the attempt to execute the code, where I get an error “ORA-00904:
“SQLTEST”.“TEST”: invalid identifier”. On the other hand, I can do this without
problems: begin myPackage.myProcedure@fred(parameter, parameter …); end;
Unfortunately, I need to be able to execute the code via the synonyms as we
intend to move things around and don’t want the users to have to edit all their
reports each time we move stuff. Cheers, Norm. [TeamT] Information in this
message may be confidential and may be legally privileged. If you have received
this message by mistake, please notify the sender immediately, delete it and do
not copy it to anyone else. We have checked this email and its attachments for
viruses. But you should still check any attachment before opening it. We may
have to make this message and any reply to it public if asked to under the
Freedom of Information Act, Data Protection Act or for litigation. Email
messages and attachments sent to or from any Environment Agency address may also
be accessed by someone other than the sender or recipient, for business
purposes. If we have sent you information and you wish to use it please read our
terms and conditions which you can get by calling us on 08708 506 506. Find out
more about the Environment Agency at www.environment-agency.gov.uk
__

KevinAngus · January 19, 2011, 3:03am

CREATE OR REPLACE PACKAGE linktest AS

PROCEDURE myproc;

/

CREATE OR REPLACE PACKAGE BODY linktest AS

BEGIN

END myproc;

/

Morning Norm,

That’s weird, it works for me. What versions are your databases?

I created a package on the remote database (11.2.0.1):

END linktest;
PROCEDURE myproc IS NULL;
END linktest;

I then did the following on my local database (10.2.0.3):

sql>CREATE DATABASE LINK kevlink CONNECT TO remote_user IDENTIFIED BY pw USING ‘remote_db’;

Database link created.

sql>CREATE OR REPLACE SYNONYM linktest_syn FOR linktest@kevlink;

Synonym created.

sql>BEGIN
2 linktest_syn.myproc;
3 END;
4 /

PL/SQL procedure successfully completed.

Does that test case not work for you?

Kevin.

NormTeamT · January 20, 2011, 7:03am

Afternoon all,

you are going to love this! I have a solution to the problem of calling
remote code objects (functions, procedure and packages) via a synonym -
thanks to Oracle support.

So, the problem:

Create or replace synonym fred
for fred@db_link;

begin
fred(params);
end;
ORA: 00904 “FRED” invalid identifier.

The solution, fully qualify the remote object name:

Create or replace synonym fred
for OWNER.fred@db_link;

It all “just” works when you create the synonym in this manner.

Documented nowhere in the Oracle docs, but now available at
http://qdosmsq.dunbar-it.co.uk/blog/2011/01/executingpackaged-code-over-
a-database-link/.

Cheers,
Norm.

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk