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."

Labels: database, howto

  ¶ 6/03/2009 01:40:00 PM