Sunday, April 4, 2010

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

create new region

Click Next.

create form

Click Next.

create form from procedure

Click Next.

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

select schema

Click Next.

Now click on the little button thing in red

red button thingy

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

list of objects

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

error

Well that sucks.

Can I do it manually?

First I create the process:



Build a simple form:

simple form

Enter in some data, click on submit.

JP form

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. :)

No comments: