Home » SQL & PL/SQL » SQL & PL/SQL » dblink variable refuses to compile (10.2.0.3 on linux rhel4)
dblink variable refuses to compile [message #387803] Fri, 20 February 2009 12:17 Go to next message
martin41339
Messages: 6
Registered: April 2008
Location: London, UK
Junior Member
we have a requirement to refresh any one of a dozen or more databases from a production server. Permanent dblinks are not tolerated, but application-created ones are OK (ie created for the session and then dropped). I am writing a transportable tablespace module which takes the target instance sid as a parameter and I am using that to name a database link created on the fly. In the code, the link is referred to as a variable:

create or replace procedure transport_tablespace_set(p_source_sid varchar2, p_target_sid varchar2, p_tablespace_list varchar2, p_password varchar2) as
v_source_sid varchar2(12) := upper(p_source_sid);
v_target_sid varchar2(12) := upper(p_target_sid);
v_tablespace_list varchar2(4000) := upper(p_tablespace_list);
.......
-- need to create a database link to connect to target instance
execute immediate('create or replace database link '||v_target_sid||' connect to system identified by '||p_password||' using '''||v_target_sid||'''');
-- need to check for connection
select 1 into v_checknum from dual@v_target_sid;
commit;


When I try to compile, the error returned is "ORA-00942 table or view does not exist".
I am aware that authid current_user does not need to verify objects until runtime, but even with this option set, this will still not compile. Any ideas out there?
Re: dblink variable refuses to compile [message #387804 is a reply to message #387803] Fri, 20 February 2009 12:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You use the database link @v_target_sid in the code.

The database link @v_target_sid does not exist at compile time.

Quote:

Permanent dblinks are not tolerated, but application-created ones are OK (ie created for the session and then dropped)



Go to the person who came up with that idea, and tell them there are catastrophes about to happen.

Rule One of Oracle development: Don't create objects at runtime by the application.

Re: dblink variable refuses to compile [message #387805 is a reply to message #387803] Fri, 20 February 2009 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

When I needed to do something similar in the past,
I always used @REMOTE database link within my code.

SELECT COUNT(*) INTO MYCOUNT FROM USER_OBJECTS@REMOTE;

Remember, privilege acquired via ROLE do not apply within PL/SQL
Re: dblink variable refuses to compile [message #387808 is a reply to message #387804] Fri, 20 February 2009 12:37 Go to previous messageGo to next message
martin41339
Messages: 6
Registered: April 2008
Location: London, UK
Junior Member
As your quote states, database links created for the session are OK. The link I want to create is on a per-session basis - I just cannot reference them in the code.
Re: dblink variable refuses to compile [message #387809 is a reply to message #387803] Fri, 20 February 2009 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Which comes first?
PL/SQL is compiled.
If no DB_LINK exists at compile time, you get error.
Re: dblink variable refuses to compile [message #387822 is a reply to message #387809] Fri, 20 February 2009 14:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That was not MY quote, I quoted you.

And then I said it is NOT OK to do it that way.

Don't. Create. Objects. At. Runtime.

Creating and dropping objects at runtime will break the application almost certainly pretty soon, and make it impossible to debug right from the start.
Re: dblink variable refuses to compile [message #387823 is a reply to message #387803] Fri, 20 February 2009 14:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ThomasG,
>Creating and dropping objects at runtime will break the application almost certainly pretty soon, and make it impossible to debug right from the start.

In principal I agree with you, but I wish to mention that when DB_LINK is involved, no method to "ALTER" or otherwise modify this type of object.

As Far As I Know, the only way to change a DB_LINK is to DROP & CREATE it (again).

Have A Nice Day (HAND!)
Re: dblink variable refuses to compile [message #387824 is a reply to message #387823] Fri, 20 February 2009 14:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep, you can't alter the link you have to re-create it when the remote database changes.

But that shouldn't really be a problem. Of course all the database objects that use it will be invalid, and be re-compiled the next time they are used ( if you don't re-compile the schema anyway to be sure they still compile before you let the users back on )

But I think that's pretty much OK, since after all you just changed the remote database they were using, so everything they did so far on the old remote database is pretty questionable, so the user SHOULD get an error that something did just break.

So dropping and creating database links that are in use is definitely something that should be done in a maintenance window where nobody is actually using them.

Re: dblink variable refuses to compile [message #387825 is a reply to message #387824] Fri, 20 February 2009 14:44 Go to previous messageGo to next message
martin41339
Messages: 6
Registered: April 2008
Location: London, UK
Junior Member
OK, ok. I understand about the dropping and recreating. However, the point of this post is that the link is created for one purpose, and one purpose only - to refresh another database with a transportable tableset, on demand by a developer for test purposes. The idea is that the user specifies a source, a target and one or more tablespaces (all subject to checking for validity and existence etc). Now, since I cannot have any permanent db links, I would like to create this package which generates a db link on the fly, copies the tablespaces over and then drops the link. In order to prevent conflict with any possible other db links in existence, I propose to append the current scn to the dblink name to make it unique, and then drop it on completion of the procedure. Apart from the opinion that this may be a bad idea in principle, I am still left with the inability to compile the procedure since the database link name ends up being a variable. When reference is made to a remote table in the procedure, the complier does not know of such a table and rightly refuses to compile. One way around this would be to use dynamic sql, but if I have a query such as:

select count(*) into v_checknum from (
select tp.endian_format
from v$transportable_platform tp
inner join v$database d
on tp.platform_name = d.platform_name
minus
select rtp.endian_format
from v$transportable_platform@v_target_sid rtp
inner join v$database@v_target_sid rd
on rtp.platform_name = rd.platform_name);

where v_target_sid is the variable placeholder for the db link name, how could I maintain the select .. into element inside the "execute immediate" version?
Re: dblink variable refuses to compile [message #387826 is a reply to message #387803] Fri, 20 February 2009 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Do not make this a hard problem!

In YOUR current session, manually CREATE DB_LINK named REMOTE.
use @REMOTE always within PL/SQL procedures.
compile these procedures (even if it changes to where @REMOTE points)
DROP DB_LINK REMOTE;


Upon further consideration, the above won't work.
After DROP DB_LINK REMOTE occurs the procedure goes invalid & will not recompile.
I was always able to keep a permanent copy of REMOTE DB_LINK.

Good Luck.

[Updated on: Fri, 20 February 2009 15:01]

Report message to a moderator

Re: dblink variable refuses to compile [message #387829 is a reply to message #387826] Fri, 20 February 2009 15:00 Go to previous messageGo to next message
martin41339
Messages: 6
Registered: April 2008
Location: London, UK
Junior Member
Black Swan,

I understand your point, and perhaps I am trying to make this too hard. But using REMOTE to create the db link is not of any use as this may be used concurrently by several users, each wanting to refresh a different database. Even if I build in a drop and recreate REMOTE named link in the procedure when called by a user, it is not a scaleable option as it may already be in use by another user.

I guess what I am really after, since I am perhaps leaning towards the dynamic sql option, is how I might deal with a returning value from a dynamic select statement?

For example:

select 1 into variable from dual@REMOTE;

is fairly simple. How can I recreate the functionality with:

execute immediate('select 1 from dual@REMOTE');

Is there an easy way to get at the value returned?
Re: dblink variable refuses to compile [message #387830 is a reply to message #387826] Fri, 20 February 2009 15:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ok. If this is not part of the actual application, but part of something a developer does, then don't create stored procedures for it either.

Just do it in a SQL script that the developer can run. Create an anonymous block, and put the create / drop database link before it and after it.


create database link ...;

declare

begin
  ... whatever ...
end;
/

drop database link ...;



Oh, and "use @REMOTE always within PL/SQL procedures" is a somewhat bad idea, if you have multiple remote databases you use over time and multiple developers.
Re: dblink variable refuses to compile [message #387831 is a reply to message #387830] Fri, 20 February 2009 15:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oh, btw. you can't copy "tablespaces" over a dblink.
Re: dblink variable refuses to compile [message #387832 is a reply to message #387803] Fri, 20 February 2009 15:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Upon further consideration, the above won't work.
After DROP DB_LINK REMOTE occurs the procedure goes invalid & will not recompile.
I was always able to keep a permanent copy of REMOTE DB_LINK.

EXECUTE IMMEDIATE exists to do DDL from within PL/SQL; not SELECT

Good Luck.
Re: dblink variable refuses to compile [message #387833 is a reply to message #387830] Fri, 20 February 2009 15:07 Go to previous messageGo to next message
martin41339
Messages: 6
Registered: April 2008
Location: London, UK
Junior Member
Actually this is part of an application called from an app in Application Express. The user selects the source, target, and a selection of tablespaces and presses go! In any case, no developers have the privileges of creating database links. This package will be executed in the context of a privileged user wuith just enough privileges to do the job.
Re: dblink variable refuses to compile [message #387838 is a reply to message #387833] Fri, 20 February 2009 15:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, it CAN'T be done in a package, since you can't compile the package until the dblink is there.

It could be done with SQLPlus in three steps like this :

Script test.sql:

set serverout on
whenever sqlerror continue

define link     = &1
define linkname = &link.suffix

create database link &linkname connect to user identified by pass using '&link';

declare

  v_ret number(12);

begin
    dbms_output.put_line('Running on Link  &link -> &linkname' );
    select count(*) into v_ret from dual@&linkname;
    dbms_output.put_line('Returned : '|| v_ret);
end;
/

drop database link &linkname;

exit



which could be called like :

$ sqlplus user/pass@LOCALDB @test.sql REMOTEDB

SQL*Plus: Release 10.2.0.4.0 - Production on Fr Feb 20 21:31:12 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

old   1: create database link &linkname connect to user identified by pass using '&link'
new   1: create database link REMOTEDBsuffix connect to user identified by pass using 'REMOTEDB'

Database link created.

old   6:     dbms_output.put_line('Running on Link  &link -> &linkname' );
new   6:     dbms_output.put_line('Running on Link  REMOTEDB -> REMOTEDBsuffix' );
old   7:     select count(*) into v_ret from dual@&linkname;
new   7:     select count(*) into v_ret from dual@REMOTEDBsuffix;
Running on Link  REMOTEDB -> REMOTEDBsuffix
Returned : 1

PL/SQL procedure successfully completed.

old   1: drop database link &linkname
new   1: drop database link REMOTEDBsuffix

Database link dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options



But it cannot be done in a pre-compiled procedure.

You possible could put the three steps ( create / anonymous block / drop ) in three execute immediates, but don't come crying when the CEO breaks it 10 minutes before a critical meeting in which he needs the data. Laughing

[Updated on: Fri, 20 February 2009 15:42]

Report message to a moderator

Re: dblink variable refuses to compile [message #387841 is a reply to message #387838] Fri, 20 February 2009 16:38 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
This may not solve your immediate problem, but I find it very useful to hide DB links from the PL/SQL or SQL but creating a view or synonym. Here you can see than my proc & synonym aren't invalidated when I drop my db link - it only throws a runtime error...
SQL> column object_name format a15
SQL> create database link my_link connect to xxx identified by yyy using 'zzz';

Database link created.

SQL> create or replace synonym syn_emp for emp@my_link;

Synonym created.

SQL> select ename from syn_emp where rownum =1;

ENAME
----------
KING

SQL> create or replace procedure p_emp is
  2    x number;
  3  begin  select count(*) into x from syn_emp; end;
  4  /

Procedure created.

SQL> -- all are valid
SQL> select object_name, status from user_objects where object_name in ('MY_LINK', 'SYN_EMP', 'P_EMP');

OBJECT_NAME     STATUS
--------------- -------
P_EMP           VALID
SYN_EMP         VALID
MY_LINK         VALID

SQL> exec p_emp;

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

SQL> alter session close database link my_link;

Session altered.

SQL> drop database link my_link;

Database link dropped.

SQL> -- fails at runtime as expected
SQL> exec p_emp;
BEGIN p_emp; END;

*
ERROR at line 1:
ORA-02019: connection description for remote database not found
ORA-06512: at "SYSTEM.P_EMP", line 3
ORA-06512: at line 1


SQL> -- but is still marked as valid!
SQL> select object_name, status from user_objects where object_name in ('MY_LINK', 'SYN_EMP', 'P_EMP');

OBJECT_NAME     STATUS
--------------- -------
P_EMP           VALID  <--  !
SYN_EMP         VALID

SQL>
Previous Topic: ORDER BY Question
Next Topic: ORA-00942: table or view does not exist
Goto Forum:
  


Current Time: Thu Dec 08 22:02:01 CST 2016

Total time taken to generate the page: 0.10670 seconds