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.
CREATE USER table_ownerThat's a loopback database link pointing to my sandbox.
IDENTIFIED BY testing
DEFAULT TABLESPACE users
QUOTA 10M ON users;
CREATE DATABASE LINK my_application
CONNECT TO table_owner
IDENTIFIED BY testing
CREATE TABLE table_owner.tI 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.
CONSTRAINT pk_id PRIMARY KEY,
CONSTRAINT nn_firstname_t NOT NULL,
CONSTRAINT nn_lastname_t NOT NULL
CREATE SEQUENCE table_owner.sq_t
START WITH 100
INCREMENT BY 1
CREATE OR REPLACE
( p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2 ) RETURN NUMBER
INSERT INTO t
RETURNING id INTO l_id;
Now I want to create a new region on the page
Now select your schema. Your workspace can map to multiple schemas, but I have only one, my own.
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).
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.
TABLE_OWNER@TESTING>SELECT * FROM T;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)
ID FIRST_NAME LAST_NAME
---------- ------------------------------ ------------------------------
100 JOHN PIWOWAR
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. :)