APEX: Database Links

I've talked about APEX being hardly known among quite a few people in the Oracle world...at least the ones I have worked with. When I first moved to Tampa, people had heard of it, but never got around to trying it out. This was late 2006, around version 2.2. My next job, same thing. The DBA had heard of it, but hadn't used it yet. Thankfully he was open and willing to try out new things. This is the same guy who had an 11gR1 RAC install within months of 11g being released in 2007. Can you say glutton for punishment?

My next assignment had it installed, but it was mostly for reporting so it had it's own instance. APEX utilized database links to access other databases. I was tasked with re-engineering their payment processing application using APEX as a skin. I worked to convince them that APEX needed to be installed on the same machine. The DBA fought me tooth and nail (and ultimately won) on that issue. I didn't get the opportunity to finish that application.

Anyway, the point I try to make is that you take away a lot of the features of APEX when you use it across a database link.

I'm not one to use a lot of the wizards or declarative stuff, I like to use packages and use the "Create Procedure from Form" wizard.

It's amazingly simple and only requires a little tweaking on the front end to finish up a input/update/delete form.

Using database links though, this isn't possible.

For example:

CREATE USER table_owner
  IDENTIFIED BY testing
  DEFAULT TABLESPACE users
  QUOTA 10M ON users;

CREATE DATABASE LINK my_application
  CONNECT TO table_owner 
  IDENTIFIED BY testing
  USING 'TESTING';

That's a loopback database link pointing to my sandbox.

CREATE TABLE table_owner.t
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  first_name VARCHAR2(30)
    CONSTRAINT nn_firstname_t NOT NULL,
  last_name VARCHAR2(30)
    CONSTRAINT nn_lastname_t NOT NULL
);

CREATE SEQUENCE table_owner.sq_t
  START WITH 100
  INCREMENT BY 1
  CACHE 10
  NOCYCLE;

CREATE OR REPLACE
FUNCTION table_owner.create_t
  ( p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2 ) RETURN NUMBER
IS
  l_id NUMBER;
BEGIN
  INSERT INTO t
    ( id,
      first_name,
      last_name )
  VALUES
    ( sq_t.nextval,
      p_first_name,
      p_last_name )
  RETURNING id INTO l_id;
  
  RETURN l_id;
END create_t;
/
show errors

I then create some basic objects in the TABLE_OWNER schema including a function that returns the ID of the newly created record. On to APEX where I have created a simple one page app. This workspace is mapped to my schema, CJUSTICE and the database link is private to that schema.

Now I want to create a new region on the page



Click Next.



Click Next.



Click Next.

Now select your schema. Your workspace can map to multiple schemas, but I have only one, my own.



Click Next.

Now click on the little button thing in red



You'll see a popup window listing out all the procedure and functions in your schema.



So it's not there...but it is in another schema (and since the database link is using the schema owner, there is no need to GRANT EXECUTE on the procedure).



Nothing there.

Let's try entering it in manually



Well that sucks.

Can I do it manually?

First I create the process:



Build a simple form:



Enter in some data, click on submit.



Then verify:

TABLE_OWNER@TESTING>SELECT * FROM T;

        ID FIRST_NAME                     LAST_NAME
---------- ------------------------------ ------------------------------
       100 JOHN                           PIWOWAR

Amusingly, I seemed to recall an issue, a few years back about using the RETURNING clause across a database link. I wonder if that's because it's not a true database link? (Figured it out the next day of course, ORA-22816)

Anyway, the point is, install APEX on the database you plan on building your application. You'll only add unnecessary work and time to your development efforts which means you might as well use Java. :)