JB to Oracle Conversion Guides
April 1, 2016
Objectives
Introduction of DCD Drivers
Installation and Configuration
VOC Table Entries
Error Analysis
File operations and Indexing
Queries
Introduction of DCD
Middle ware component
Transfer DATA between t24 to Oracle Storage
Convert JQL to SQL
Data stored in ORACLE as XML CLOB columns
Table has two fields RECD , XMLRECORD
Installation and Configuration
.profile changes
export DRIVER_HOME=$TAFC_HOME/XMLORACLE
export
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export ORACLE_SID=FBLT24DC
export TWO_TASK=FBLT24DC
export LIBPATH=$LIBPATH:$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:$DRIVER_HOME/bin
export JBCOBJECTLIST=$DRIVER_HOME/lib:$JBCOBJECTLIST
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export TNS_ADMIN=$ORACLE_HOME/network/admin
Configuration
XMLORACLE_FRMWRK Version
R12.107986
*******************************************
Main menu
** XMLORACLE_FRMWRK Version R12.107986
*******************************************
*************************************************
********************************
1. Database settings.
2. User settings.
3. Run installation script against database.
4. Test connection.
5. Save only.
6. Save and exit.
Database menu
**********************************************************
***********************
1. List current setting.
2. Set Server.
3. Set Database.
4. Set data tablespace.
5. Set index tablespace.
6. Set long tablespace.
7. Set index storage group.
9. Exit without saving.
9. Back to main menu.
Select option:
Select option: 1
Database Server Name : 'Compiled default'
Database Name : 'FBLT24DC'
Data tablespace: 'T24DATA'
Index tablespace: 'T24INDEX'
Long tablespace: 'Compiled default'
Index storage group: 'Compiled default'
Press any key to continue.
Configuration
** XMLORACLE_FRMWRK Version R12.107986
*******************************************
User menu
***************************************************************************
******
1. List current settings.
2. Set Oracle user name.
3. Set Oracle password.
4. Set Oracle schema.
5. Set Oracle role name.
6. Set Oracle role password.
7. Set Exception email address.
9. Back to main menu.
Select option: 1
DB login user name: 'fblt24'
DB login password: 'qOmk4OdyYRo='
DB login schema: ''
Email address: '**NONE**'
XML Schema directory: '**NONE**'
Oracle role name: '**NONE**'
Oracle role pwd: '**NONE**'
Press any key to continue.
Configuration
Save the changes and do a test connection and make sure it returns no error from ORACLE
** XMLORACLE_FRMWRK Version R12.107986 *******************************************
Main menu
*********************************************************************************
1. Database settings.
2. User settings.
3. Run installation script against database.
4. Test connection.
5. Save only.
6. Save and exit.
9. Exit without saving.
Select option: 4
Connection successful
Press any key to continue.
Configuration Run Install.sql
1.
2.
3.
4.
5.
6.
Database settings.
User settings.
Run installation script against database.
Test connection.
Save only.
Save and exit.
9. Exit without saving.
Select option: 3
Connection successful
Connection successful
Connection successful
Connection successful
Connection successful
Connection successful
Connection successful
Connection successful
Connection successful
Installation completed!
VOC Table Entries
Sample VOC Entries :
>JED VOC FBNK.CUSTOMER
File VOC Record FBNK.CUSTOMER
Command >
0001 TABLE
0002 XMLORACLEInit
0003 D_F_CUSTOMER
0004 FBNK_CUSTOMER
Statistics After Conversion
Error Analysis
ORAdriver.log
Sample Screen shot
3.5.1.80087 - 19202202 - (select.b,7) - Tue Nov 11 02:48:56 - FBNK.PK.W.RF.BILL FBNK_PK_W_RF_BILL - SQL Failed: SELECT t.RECID, t.XMLRECORD FROM
FBNK_PK_W_RF_BILL t Error:ORA-01013: user requested cancel of current operation
.5.1.80087 - 19202202 - (select.b,7) - Tue Nov 11 02:48:56 - FBNK.PK.W.RF.BILL FBNK_PK_W_RF_BILL - ** Error ** SCANFILE: Failed to process statement SELECT
t.RECID FROM FBNK_PK_W_RF_BILL t WHERE RECID LIKE '2002820962%' ESCAPE '\'
ORDER BY t.RECID
3.5.1.80087 - 11796532 - (select.b,7) - Tue Nov 11 02:50:06 - FBNK.PK.W.RF.BILL FBNK_PK_W_RF_BILL - SQL Failed: SELECT t.RECID, t.XMLRECORD FROM
FBNK_PK_W_RF_BILL t Error:ORA-01013: user requested cancel of current operation
3.5.1.80087 - 11796532 - (select.b,7) - Tue Nov 11 02:50:06 - FBNK.PK.W.RF.BILL FBNK_PK_W_RF_BILL - ** Error ** SCANFILE: Failed to process statement SELECT
t.RECID FROM FBNK_PK_W_RF_BILL t WHERE RECID LIKE '2003176234%' ESCAPE '\'
ORDER BY t.RECID
10
File operations
CREATE-FILE
CLEAR-FILE
DELETE-FILE
>CREATE-FILE ORA.TEST TYPE=XMLORACLE TABLE = ORA_TEST
>DELETE-FILE ORA.TEST
>CLEAR-FILE ORA.TEST
11
Indexing
CREATE-EXTINDEX
LIST-EXTINDEX
DELETE-EXTINDEX
CREATE-EXTINDEX fv FBNK.ACCOUNT CATEGORY
LIST-EXTINDEX FBNK.ACCOUNT
DELETE-EXTINDEX fv FBNK.ACCOUNT CATEGORY
12
Queries ?
13
Thank you !!!
14
