| dblink variable refuses to compile [message #387803] |
Fri, 20 February 2009 12:17  |
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  |
ThomasG
Messages: 3212
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 #387822 is a reply to message #387809] |
Fri, 20 February 2009 14:12 |
ThomasG
Messages: 3212
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 #387824 is a reply to message #387823] |
Fri, 20 February 2009 14:28 |
ThomasG
Messages: 3212
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 |
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 |
 |
BlackSwan
Messages: 26766
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 |
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 |
ThomasG
Messages: 3212
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 #387833 is a reply to message #387830] |
Fri, 20 February 2009 15:07 |
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 |
ThomasG
Messages: 3212
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. 
[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 |
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> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
