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

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Mon, 22 Sep 2003 07:34:37 -0800
Message-ID: <F001.005D0B46.20030922073437@fatcity.com>

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). Received on Mon Sep 22 2003 - 10:34:37 CDT