Oracle Database Link Elimination for Java

1.    Outline

This documentation is to resolve the problem that database links exist in the embedded SQL, which will have to be migrated into Stored Procedure (SP) where database link should not exist at any code layer.

First, the general existence form of database links will be classified; second, separated solution for each classification will be presented with specific example and study cases; third, the advantages, disadvantages, risks as well as coping strategy will be deeply discussed; finally, this documentation will give a summary and relevant prediction on the Cloud Database Link.

 

2.    DB Link Classification

§  Used as Data Replication

The database link is used as data replication for specific application. For example, there is a module for refreshing data, which will refresh relevant data from remote database to local database.

BTW: Data replication which happens in the backend or as maintenance of DBA, will not in our scope.

 

§  Used Lonely in DML SQL.

Data manipulation language happens on the linked db table lonely. For example, delete/update a row of data of the linked db table.

Basically, IDF Services will be needed.

 

§  Used as Joined table IN SELECT SQL.

Generally, no mater one or more than one linked DB table exists in the SELECT SQL, the solution will be the same. 

 

§  Used in Subquery or Where Clause with Few Data

 

 

3.    DB Link Elimination Solution

§  Used as Data Replication

 

§  Used Lonely in DML SQL.

 

§  Used as Joined table IN SELECT SQL.

When the linked table is used as a joined table, we could fetch the dataset as an object array through IDF Services or additional SP of the Linked DB, then, make the array as an input of the main SP in the application.

The implementation flow is described as below:

 

Study Case:

Original Db link usage:

FROM user.publish_books full outer join user.book_sets@db_link  book

 

Call the sp in the java code

"{call user.getBookSummary(?,?)}";

 

First, Create relevant type for Main SP to pass Array into SP, the array contains the data ofuser.book_sets@db_link  :

 

create or replace

type books_object as object

(

  BOOKS_ID   NUMBER(10),

  NAME              VARCHAR2(60),

  SHORT_NAME        VARCHAR2(40),

  DESCRIPTION       VARCHAR2(200),

  CURRENCY_CODE     VARCHAR2(20),

)

 

create or replace

type books_table as table of books_object;

 

Second, we have to know the database link user credential information, then create relevant SP to fetch the Linked DB table data:

"{call user_link.books_SEL(?)}";

 

The function to fetch the Linked DB table data is :

 

public ArrayListselectBooks()throws Exception{

              Connection connection = null;

              CallableStatement cs=null;

              String sql=null;         

              ResultSet rs=null;

              connection = getFinAppsConnection();

              sql = "{call user_link.books_SEL(?)}";

             

              try {

                      cs = connection.prepareCall(sql);

                      cs.registerOutParameter(1, OracleTypes.CURSOR);      

                      cs.execute();

                      rs = (ResultSet)cs.getObject(1);

                      while (rs.next())

                      {

                          int book_id =rs.getInt(1) ;

                          Object[] bookObject = {book_id, rs.getString(2),

                                                rs.getString(3), rs.getString(4),

 rs.getString(5)};

                          dataList.add(bookObject);

                      }

              } catch (SQLException e) {

                     log.debug("exception occurred in select Book:" + e);

                     log.error(e);

                     throw e;

              }

               finally

                      {

                          if (cs !=null)

                            cs.close();

                          if (connection !=null)

                              connection.close();

                      }

 

              return dataList;

       }

Finally, Convert the dataset to be Array supported by Oracle through Descriptor :

  

BookList = oDBService.selectBooks();

sql = "{call user.getBookSummary(?,?)}";               

connection = oDBService.getConnection();

cs = connection.prepareCall(sql);

logger.debug("BookDAO.getBookSummary(): Query" + sql);

final OracleConnection oc = (OracleConnection) cs.getConnection();

StructDescriptor st = new StructDescriptor("user.BOOKS_OBJECT",oc);

Object[] bookArrayObject = new Object[BookList.size()];

int i = 0;

while( i< BookList.size())

     {

       Object[] ob1 =  (Object[])BookList.get(i);

       STRUCT s1 = new STRUCT(st,oc,ob1);

       bookArrayObject[i] = s1;

       i++;

      }

 ArrayDescriptor descriptor = null;

 descriptor = ArrayDescriptor.createDescriptor("user.BOOKS_TABLE", oc);

 ARRAY bookArray = new ARRAY(descriptor, oc, bookArrayObject);

 cs.setArray(1, bookArray);

 cs.registerOutParameter(2, OracleTypes.CURSOR);      

 cs.execute();

 rs = (ResultSet)cs.getObject(2);

 

/****************************

The most important thing is to construct the struct and array object to be transferred into the procedure

**********************/