Migration of Oracle Forms to Apex. Connecting to different databases from Apex

Oracle Apex Login Page without database name

Oracle Forms login page with Database Name

One of the common feature in oracle forms is connection to various databases from a single application while logging in. This functionality is not natively availabe in Oracle apex but can be achieved with below steps.

1. Create the necessary database links.
2. Getting the database name from the login screen.

a. Create an application item from the shared components (Name : DBLINK)

b.From the login page in oracle apex, mostly page : 9999, add a page item of type “Text Field” Name : P9999_DATABASE

c. Create a process from below

         Type     : Execute code 
         Point    : Processing
         Language : PLSQL
         PLSQL CODE :
         :DBLINK := :P9999_DATABASE;
         APEX_UTIL.SET_SESSION_STATE ('DBLINK', :P9999_DATABASE);

3. Read from database

For read operations you can either use Classic / Interactive Report

a. Create a function to dynamically retrieve the query

-- This function will be called from the source region
CREATE OR replace FUNCTION Get_run_time_sql(p_dblink IN VARCHAR2)
RETURN VARCHAR2
IS
  v_sql VARCHAR2(32000);
BEGIN
    v_sql := ' select columun1,column2 ...   from TABLE_NAME@'
             ||p_dblink
             || '  where  (columun1 = :apex_page_parameter_1)        order by columun1';

    RETURN v_sql;
END; 

b. Source information for the region

   Location : Local Database
   Type     : Function Body Returning SQL Query 
   Language : PLSQL
   PL/SQL Function Body returning SQL Query : 
   DECLARE
    a VARCHAR2(32000);
  BEGIN
      a := Get_run_time_sql_tt(Nvl(:DBLINK, 'default value'));
  
      RETURN a;
  END; 

c. Pass parameters for the query in page items to submit

4. Write operations

Same like read operations we need to achieve this functionality through dynamic SQL.

In the below steps we will call a procedure / function that executes Insert / Update / Delete that takes parameters from the Oracle apex page.

Create a dynamic action / process that executes plsql

DECLARE
     out_msg1      VARCHAR2(100);
     out_msg2      VARCHAR2(100);
    BEGIN
      EXECUTE IMMEDIATE 'begin  remote_procedure@'||:DBLINK||
     '(PARAMETER1=>:P1_PARAM1, 
       PARAMETER2=>:P1_PARAM2, 
       p_alert_msg =>:msg,
       p_alert_type=>:msg_type); end;'
     USING IN :P1_PARAM1, :P1_PARAM2, out_msg1 msg, out_msg2;
    -- Call remote procedure
    -- Pass input parameters
    -- Get values into local variables
     :P1_OUT_1 := msg;
     :P1_OUT_2 := msg_type;
    END;