Re: referencing a remote table from PL/SQL

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Wed, 27 Sep 2000 15:38:18 -0700
Message-ID: <8qtssp$k15$1_at_spiney.sierra.com>


The link name <anything>_at_<anything> is not valid.

Start by creating a link to the <sid>.<domainname> connecting to your username and password on <sid>.

After trying to use the link, oracle will tell you what the name must be.

SQL> create database link prod.home.com
  2 connect to me identified by me_me
  3 using 'my_tnsname'
  4 /

Database link created.

SQL> select table_name from user_tables_at_prod.home.com; select table_name from user_tables_at_prod.home.com

                                   *

ERROR at line 1:
ORA-02085: database link PROD.HOME.COM connects to PROD.WORLD

drop database link prod.home.com
/
create database link PROD.WORLD
connect to me identified by me_me
using 'my_tnsname'
/

"Mark Tomlinson" <marktoml_at_hotmail.com> wrote in message news: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 Thu Sep 28 2000 - 00:38:18 CEST

Original text of this message