Home » SQL & PL/SQL » SQL & PL/SQL » Error access remote table using synonyms
Error access remote table using synonyms [message #387672] Fri, 20 February 2009 04:24 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi guys,

I trying to access a table from a remote database but im having some problems with the synonyms that point to the relevant table.

I created synonyms as shown here..

CREATE SYNONYM CCS_RA_INTERFACE_DIST FOR CCS_RA_INTERFACE_DIST@SUK_CCS;
CREATE SYNONYM CCS_RA_INTERFACE_LINES FOR CCS_RA_INTERFACE_LINES@SUK_CCS;



The database link is active because if i run select sysdate from dual@suk_ccs i get the system date back.

If i try to access the table using the synonym as shown below i get the results back

select * from ccs_ra_interface_lines/


if i also try to bypass the synonym as shown below i get the results back

select * from ccs_ra_interface_lines@suk_ccs
where rownum<3
/


But i have a problem if i try to access the same synonym from within pl/sql rather than sql. Here is an example

declare n number;
 begin
 select count(*) into n from ccs_ra_interface_lines;
 end;


The above generates the following error

 select count(*) into n from ccs_ra_interface_lines;
                             *
ERROR at line 2:
ORA-06550: line 2, column 30:
PL/SQL: ORA-00980: synonym translation is no longer valid
ORA-06550: line 2, column 2:
PL/SQL: SQL Statement ignored


If i try to access the table directly and bypass the synonym via pl/sql i get a different error.
  1  declare n number;
  2  begin
  3  select count(*) into n from ccs_ra_interface_lines@suk_ccs
  4  where rownum<3;
  5* end;
10:08:45 > /
select count(*) into n from ccs_ra_interface_lines@suk_ccs
                            *
ERROR at line 3:
ORA-06550: line 3, column 29:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored




What causes the above error? I have a feeling its a permissions issue but i cant figure out what it is. The same owner owns the database link, the synonyms and running the pl/sql modules.

Thanks in advance

[Updated on: Fri, 20 February 2009 04:29]

Report message to a moderator

Re: Error access remote table using synonyms [message #387675 is a reply to message #387672] Fri, 20 February 2009 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
Privileges aquired via roles aren't used in procedures:

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html
Re: Error access remote table using synonyms [message #387677 is a reply to message #387675] Fri, 20 February 2009 04:53 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi,

The user that is executing the above has the dba privilege. I didnt quite understand how that affects access to the synonyms. By the way the following works

10:51:16> declare n date;
10:51:28   2  begin
10:51:29   3  select sysdate into n from dual;
10:51:36   4  end;
10:51:37   5  /


I am able to create a procedure as shown above but i cant use a procedure that uses the specific synonyms

I am using Oracle 10g.
Re: Error access remote table using synonyms [message #387678 is a reply to message #387677] Fri, 20 February 2009 04:55 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
DBA is a role, not a privilege
Re: Error access remote table using synonyms [message #387680 is a reply to message #387672] Fri, 20 February 2009 05:01 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
The synonyms are almost certainly a red herring here.

And the fact that you're running that as dba is irrelevent. It's the permissions granted to the user you're using to connect to the remote db as that are relevant (ie the user specified in the db link).

If you connect to the remote db directly and run:

select count(*) into n from ccs_ra_interface_lines;


declare n number;
 begin
 select count(*) into n from ccs_ra_interface_lines;
 end;


Do you get the same errors as when running them remotely?
Re: Error access remote table using synonyms [message #387683 is a reply to message #387672] Fri, 20 February 2009 05:15 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi,

Here is what i tried by logging on directly on to the remote database (using the user specified in the db link).

SQL> select count(*) from ccs_ra_interface_lines;

  COUNT(*)
----------
     13044

SQL> declare n number;
  2  begin
  3  select count(*) into n from ccs_ra_interface_lines;
  4  end;
  5  /

PL/SQL procedure successfully completed.



It looks like it works fine if im accessing the tables directly on the remote db.

Thanks

[Updated on: Fri, 20 February 2009 05:19]

Report message to a moderator

Re: Error access remote table using synonyms [message #387691 is a reply to message #387683] Fri, 20 February 2009 05:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you please execute the following code in the sequence and copy & paste it here from sql*plus.
set role none;
select * from ccs_ra_interface_lines@suk_ccs
where rownum<3;
set role all;
select * from ccs_ra_interface_lines@suk_ccs
where rownum<3;

Regards

Raj

[Updated on: Fri, 20 February 2009 05:33]

Report message to a moderator

Re: Error access remote table using synonyms [message #387693 is a reply to message #387691] Fri, 20 February 2009 05:38 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Here is the output.

I've just used count(*) because otherwise it will return lots of columns

> set role none;

Role set.

> select count(*) from ccs_Ra_interface_lines@suk_ccs
  /
>

  COUNT(*)
----------
     13044

> set role all;

Role set.

> select count (*) from ccs_Ra_interface_lines@suk_ccs
 /
Enter>

  COUNT(*)
----------
     13044



Did you want me to run the above on the remote database directly of from the client machine? I run the above from the client machine so wouldnt the role changes have affected the logged on user but not the db link user?

[Updated on: Fri, 20 February 2009 05:40]

Report message to a moderator

Re: Error access remote table using synonyms [message #387695 is a reply to message #387672] Fri, 20 February 2009 05:41 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
Curious.

Can you try that again but this time select from the synonyms?
Re: Error access remote table using synonyms [message #387700 is a reply to message #387695] Fri, 20 February 2009 05:48 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Here is the output using the synonyms. I dont have a problem accessing them using the synonyms. The problem is if i try using pl/sql.

> set role none;

Role set.

> select count(*) from ccs_Ra_interface_lines
 /


  COUNT(*)
----------
     13044

> set role all;

Role set.

> select count(*) from ccs_ra_interface_lines
/


  COUNT(*)
----------
     13044


Thanks

[Updated on: Fri, 20 February 2009 05:49]

Report message to a moderator

Re: Error access remote table using synonyms [message #387703 is a reply to message #387672] Fri, 20 February 2009 05:55 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is this relevant by any chance:

http://www.orafaq.com/forum/t/95480/0/
Re: Error access remote table using synonyms [message #387705 is a reply to message #387703] Fri, 20 February 2009 06:01 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
cookiemonster wrote on Fri, 20 February 2009 05:55
Is this relevant by any chance:

http://www.orafaq.com/forum/t/95480/0/


yes that is the same problem i am having. His solution wouldnt work for me though as i am only accessing one database directly. (There is no intermediary db)
Re: Error access remote table using synonyms [message #387707 is a reply to message #387672] Fri, 20 February 2009 06:10 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
So that'd be a no then.

I'm afraid I'm stumped.

Generally when a select statement works in SQL but not PL/SQL it's due to roles, but that doesn't appear to be the case here.

I can only assume you've got some obscure oracle bug.
Time to go to metalink methinks.
Re: Error access remote table using synonyms [message #387709 is a reply to message #387705] Fri, 20 February 2009 06:22 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you post the output of the following command please again from sql*plus.
Forgot to mention your oracle version as well.
select db_link, username, host from user_db_links;
select * from v$version;

Also could you please execute the following code on the other side of the database directly.
select * from v$version;
set role none;
<query>
set role all;
<query>


Regards

Raj

[Updated on: Fri, 20 February 2009 06:27]

Report message to a moderator

Re: Error access remote table using synonyms [message #387771 is a reply to message #387709] Fri, 20 February 2009 09:25 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
select db_link, username, host from user_db_links;


DB_LINK				USERNAME	HOST	
------------------------------------------------------------
CCSLIVE.xx.xx.xx		xxxx		xxxx
SUK_CCS.xx.xx.xx		suk_ccs		xxx



select * from v$version;


> select * from v$version
/


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production




select * from v$version (on remote db)

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.4.0 - 64bit Production
PL/SQL Release 9.0.1.4.0 - Production
CORE    9.0.1.2.0       Production
TNS for HPUX: Version 9.0.1.4.0 - Production
NLSRTL Version 9.0.1.4.0 - Production




I tried running the pl/sql directly on the remote db and it works. It just doesnt work if i try it on the client machine.

[Updated on: Fri, 20 February 2009 09:27]

Report message to a moderator

Re: Error access remote table using synonyms [message #387786 is a reply to message #387771] Fri, 20 February 2009 10:51 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.4.0 - 64bit Production
PL/SQL Release 9.0.1.4.0 - Production
CORE 9.0.1.2.0 Production
TNS for HPUX: Version 9.0.1.4.0 - Production
NLSRTL Version 9.0.1.4.0 - Production


You didn't mention that you are using dblink across two different oracle database version. Moreover you are using 9.0 which is having loads of issues. You should considering upgrading to atleast 9.2 preferabbly 10g. There are quite a number of issues especially with dblink between 10g and 9i. For this particular issue it looks like you are hitting the follow bug : 1829296. Check in metalink for more information.

Regards

Raj
Re: Error access remote table using synonyms [message #387896 is a reply to message #387700] Sat, 21 February 2009 10:49 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe a stupid question (I have no two db's running, so can't test it now), but does a "set role none" also unset any roles in the remote db, or will the dblink still use all the default roles at the other end for the connecting user?

[Updated on: Sat, 21 February 2009 10:50]

Report message to a moderator

Previous Topic: Temp usage
Next Topic: Sort alphanumberic column
Goto Forum:
  


Current Time: Tue Dec 06 02:53:13 CST 2016

Total time taken to generate the page: 0.23221 seconds