Wednesday, June 3, 2009

Create Database Link - Loopback Edition

I've been tasking with making some fairly drastic changes. When I say drastic I mean that I'm changing the name of a table and updating all of the dependencies. The drastic part comes in because there are about 11 Trillion dependencies.

So not to affect the other developers while I hammer this out, I created my own instance and one-by-one brought over the necessary objects (and their dependent objects). I know there are other ways to do this, develop in your named schema in the development database just changing or repointing the objects to your own.

Since I am new however, I thought it would be very beneficial to do it on my own. There are a couple of advantages:
1. I won't interfere with any developer's progress
2. It's a great opportunity to learn how everything fits together
3. I have ApEx installed (development doesn't...yet).
4. Look what I can do

Anyway, one procedure had an INSERT into our reporting environment (another database) so it wouldn't compile. It used the INSERT INTO [table]@database_link syntax. How can I replicate that? I know I've seen or read the solution somewhere, but I decided to do without Google this time to see how long it would take me.

I'll create TEST_USER, where the procedure lives and REPORT_DATA where the table lives. Following is performed with DBA privs:
CREATE USER test_user
IDENTIFIED BY testing
DEFAULT TABLESPACE users
QUOTA 5M ON users;

GRANT resource, create session TO test_user;

CREATE PUBLIC DATABASE LINK loopback_link
CONNECT TO report_data
IDENTIFIED BY testing
USING 'TESTING';
Now I'll create the procedure in the TEST_USER schema:
PROD@TESTING>CREATE OR REPLACE
2 PROCEDURE test_user.update_t
3 ( p_x IN NUMBER,
4 p_y IN VARCHAR2 )
5 IS
6 BEGIN
7 INSERT INTO t@loopback_link
8 ( x,
9 y )
10 VALUES
11 ( p_x,
12 p_y );
13 END update_t;
14 /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.18
PROD@TESTING>show errors
Errors for PROCEDURE TEST_USER.UPDATE_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 ORA-04052: error occurred when looking up remote object
REPORT_DATA.T@LOOPBACK_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM
ORA-00604: error occurred at recursive SQL level 1
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from LOOPB
Yup, no user yet.
CREATE USER report_data 
IDENTIFIED BY testing
DEFAULT TABLESPACE users
QUOTA 1M ON users;

GRANT create session TO report_data;

CREATE TABLE report_data.t
(
x NUMBER PRIMARY KEY,
y VARCHAR2(30)
);
Try creating the procedure in TEST_USER again
PROD@TESTING>CREATE OR REPLACE
2 PROCEDURE test_user.update_t
3 ( p_x IN NUMBER,
4 p_y IN VARCHAR2 )
5 IS
6 BEGIN
7 INSERT INTO t@loopback_link
8 ( x,
9 y )
10 VALUES
11 ( p_x,
12 p_y );
13 END update_t;
14 /

Procedure created.
WIN! Does it work though?
PROD@TESTING>EXEC TEST_USER.UPDATE_T( 1, 'BOLLOCKS' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
PROD@TESTING>SELECT * FROM report_data.t;

X Y
---------- ------------------------------
1 BOLLOCKS

1 row selected.
As LC would say, "That's not so bad."

9 comments:

Crisatunity said...

Two issues, one big and one little.

First the little one: When you are creating deployment code, you will want to avoid any schema references as much as possible. Your "create procedure test_user.update_t" creates constricted developer sandbox and production deployment flexibility.

Second the bigger one: In general you will not want to develop source code with the dblink syntax in it.

If you create a synonym to the same object:

create synonym t for t@loopback_link;
...
create procedure update_t is
...
insert into t (x,y) values ...

This also gives you code with more developer sandbox and production deployment flexibility. This is especially true if you are using the dblink for some component in your development sandbox and no such dblink exists when it comes time to deploy to production.

oraclenerd said...

@crisatunity

re: Little One
I've been having the same conversation with @serge_a_storms the past year. I don't believe that scripts should include the schema qualifier.

re: Big One
Completely agree. Unfortunately I am not the creator nor do I have the ability to change that...yet. From what I can tell, it's essentially the L (of ETL), just staging the data. An ideal would be either batching it (once a day or whatever) or using something like CDC to do it automagically.

Crisatunity said...

One other thing I ran into with dblinks that may or may not be in the documentation and may or may not be relevant to your situation:

Avoid using dblink references that reference a synonym on the target database.

Gary Myers said...

I beg to differ on the 'Big Issue'.
Having an object (table or view) on a remote DB has implications for the optimizer (and with user defined types).
I'd want future developers to KNOW that whenever they look at the relevant SQL statement. I want anyone reviewing the code to see it too.
If the synonym is named such that it obviously uses a DB link, or the team and environment are such that everyone knows it anyway, that's fine. Pros and Cons.

If, in production, the object is referenced over a DB link, I'd want it to do that in development too. Synonym or no synonym.

moleboy said...
This comment has been removed by the author.
Crisatunity said...

@Gary Using synonyms has no effect on the optimizer. Nor does it conceal the dblink dependency in data dictionary queries.

My thinking is that the developer should KNOW what the optimizer implications are by explain plans of statements, profiling the code and data dictionary queries. If they the think are KNOWING anything by visually examining the source code of a stored procedure, they are standing in quicksand.

Putting dblink syntax into source code is a pattern of failure. It sacrifices flexibility for no legitimate gain.

Tom said...

Why not just add a comment that it uses a synonym or db link or a dblink to a synonym. Try to be informational if you can : )

Gary Myers said...

"It sacrifices flexibility for no legitimate gain."
Sacrifices flexibility ? Why ? You think that it isn't possible to change the code later ?
After all, if you do change which DB the object is in, you'll need to test the code set anyway. Changing a reference or two is hardly a chore.
Besides, moving schemas between databases is going to be rare enough that I'll never worry about it.

Crisatunity said...

@gary You ask "why". Here is my answer. "Being able to change the code later" is not the way I think.

The underlying connection information encapsulated by a database link as well as the meta-information of the dblink's name itself are an ENVIRONMENTAL components to your app, analogous to the host name or IP address. Things important to the overall execution of all software in the mix, but they are something to avoid at all costs as a dependency in your code.

Philosophically, I am opposed to the very concept of an environmental component changing and it requires my code to be modified and recompiled.

I can tell you have a lower threshold of concern for changing application source when in fact you are not changing its functionality in any way. I am very resistant to doing that.