Re: referencing a remote table from PL/SQL
Date: Mon, 18 Sep 2000 20:33:02 -0400
Message-ID: <ssdcb8ln9g08ef_at_corp.supernews.com>
I haven't seen a problem doing this (referencing a table via DBLINK) from Oracle 8 and have done this many times, but you may be hitting this because of the double _at_ sign in the dblink name. I suspect that the PL/SQL parser uses this to help it delimt the dblink name and it is probably then getting confused.
"Cristian Veronesi" <c.veronesi_at_crpa.it> wrote in message
news:39C22BC6.C4244739_at_crpa.it...
May you please explain me the reason of the below situation?
SQL> desc uno_at_stav_at_atec
Name Null? Type ------------------------------- -------- ---- CODICE VARCHAR2(10)
SQL> select * from uno_at_stav_at_atec ;
CODICE
3
SQL> declare
2 x integer ;
3 begin
4 select 0 into x from uno_at_stav_at_atec ;
5 end ;
6 /
select 0 into x from uno_at_stav_at_atec ;
*
ERROR at line 4:
ORA-06550: line 4, column 22: PLS-00201: identifier 'UNO_at_STAV_at_ATEC' must be declared ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
The same thing happens if I create a synonym :
SQL> create synonym uno for uno_at_stav_at_atec ;
Synonym created.
SQL> declare
2 x integer ;
3 begin
4 select 0 into x from uno_at_stav_at_atec ;
5 end ;
6 /
select 0 into x from uno;
*
ERROR at line 4:
ORA-06550: line 4, column 22: PLS-00201: identifier 'UNO' must be declared ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
The only workaround that I found is to create a view :
SQL> drop synonym uno ;
Synonym dropped.
SQL> create view uno as select * from uno_at_stav_at_atec ;
View created.
SQL> declare
2 x integer ;
3 begin
4 select 0 into x from uno ;
5 end ;
6 /
PL/SQL procedure successfully completed.
What have I to in order to be able to reference the remote table directly, without passing through a view?
Best Regards, Cristian
-- Cristian Veronesi ><((((ยบ> http://www.crpa.it How many deaths will it take till we knows That too many people have died ?Received on Tue Sep 19 2000 - 02:33:02 CEST