Oracle-L: Re: ORA-4052 querying in PLSQL across a DB-Link from 9.2.0.1 to

From: raju pa <raju_pa_24_at_yahoo.com>
Date: Mon, 22 Sep 2003 09:09:42 -0800
Message-ID: <F001.005D0B5F.20030922090942@fatcity.com>

Did you check if it is a privilege issue because the access privilege is granted thru a role. roles are not recognised in PL/SQL

Hemant K Chitale <hkchital_at_singnet.com.sg> wrote:

I face this peculiar behaviour when I connect from my 9.2.0.1/Tru64 database to a 9.2.0.4/Solaris [upgraded from 9.2.0.1 to 9.2.0.2 to 9.2.0.4] database through a Database Link.
{I use the 9.2.0.1/Tru64 Database to monitor a number of other 8.0-->9.2.0.2/NT->Solaris->Tru64
databases using the DBSNMP account, with some additonal custom views} Here @HRDM is a DB-Link to the 9.2.0.4/Solaris Database :

When querying across a DB-Link I get no errors if the query is an SQL query. However, I get ORA-4052 and other errors when I put the query in a PLSQL block.
SQL :
SQL> l
1 select count(*)
2 from dba_tablespaces_at_hrdm dtbs,
3 dba_data_files_at_hrdm ddfs
4 where dtbs.tablespace_name = ddfs.tablespace_name 5* and ddfs.status = 'AVAILABLE'
SQL> /
COUNT(*)

7
SQL>
PLSQL :
SQL> get X1.sql
1 declare
2 cnt number;
3 begin
4 select count(*) into cnt
5 from dba_tablespaces_at_hrdm dtbs,
6 dba_data_files_at_hrdm ddfs
7 where dtbs.tablespace_name = ddfs.tablespace_name 8 and ddfs.status = 'AVAILABLE';
9 dbms_output.put_line('Cnt is '||cnt);
10* end;
SQL> /
select count(*) into cnt
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PL/SQL: ORA-04052: error occurred when looking up remote object SYS.DBA_DATA_FILES_at_HRDM

ORA-06553: PLS-623: FLOAT precision constraint must be between 1 and 126
ORA-06553: PLS-623: FLOAT precision constraint must be between 1 and 126
ORA-06553: PLS-623: FLOAT precision constraint must be between 1 and 126
ORA-06553: PLS-623: FLOAT precision constraint must be between 1 and 126
ORA-06553: PLS-623: FLOAT precision constraint must be between 1 and 126
ORA-06553: PLS-623: FLOAT precision constraint must be bet
ORA-06550: line 4, column 1:

PL/SQL: SQL Statement ignored
SQL> I've logged a Priority-2 TAR with Support currently but wonder if someone else can reproduce this {I can reproduce it on the only 2 9.2.0.4 instances I have,
both Solaris, connecting from the 9.2.0.1 instance}

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Hemant K Chitale
INET: hkchital_at_singnet.com.sg

Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: raju pa
INET: raju_pa_24_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------