Oracle XDF Utility to Migrate Database Objects

Oracle E Business Suite provides utilities like FNDLOAD,WFLOAD to migrate Application Objects from one instance to another.Similarly it provides XDF utility to migrate Database Objects.
XDF utility is a Java class ,but a wrapped version of the it is available to be executed as Perl script too.Let us take an example to understand the utility in a better manner.Firstly we will create a custom table in a custom schema ,then generate the XDF file ,and finally apply the script to recreate the objects.

1. Table Creation in Custom Schema:
CREATE TABLE XX.XX_XDF_TEST
(EMPNO NUMBER(4,0) NOT NULL ENABLE,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
CONSTRAINT XX_XDF_TEST_PK PRIMARY KEY (EMPNO));

COMMENT ON COLUMN XX.XX_XDF_TEST.DEPTNO IS ‘Employee Department Number’;

CREATE INDEX XX.XX_XDF_TEST_N1 ON XX.XX_XDF_TEST (ENAME);

GRANT ALL ON XX.XX_XDF_TEST TO APPS;

CREATE SYNONYM APPS.XX_XDF_TEST FOR XX.XX_XDF_TEST;

2. Execute XDF’s FndXdfGen Java Class in Unix prompt to generate the XDF file of the Custom Table.The command will extract structure of the Table and its dependent objects like Index,Synonym,Constraints,Comments,Synonym and put them in an XDF file.

java oracle.apps.fnd.odf2.FndXdfGen apps_schema=apps apps_pwd= jdbc_protocol=thin jdbc_conn_string=host:port:SID object_name=XX_XDF_TEST xsl_directory=$FND_TOP/patch/115/xdf/xsl owner_app_shortname=XX xdf_filename=/home/user1/XX_XDF_TEST object_type=TABLE logfile=/home/user1/XX_XDF_TEST.log

3. Execute XDF’s FndXdfCmp Class un UNix prompt to create the database objects, here the database object is a Table and its related objects.

java oracle.apps.fnd.odf2.FndXdfCmp apps thin host:host:port:SID TABLE /home/user1/XX_XDF_TEST.xdf $FND_TOP/patch/115/xdf/xsl

Note : XDF utility will create a Select * View of the table under creation in custom schema and then create a synonym of the view in APPS schema.

4. The same objective can be achieved by using Perl script functions, which are nothing but a wrapper on the above Java classes.
Command to Download :
perl $FND_TOP/bin/xdfgen.pl apps/

owner_app_shortname=’XX’

Command to Upload :
perl $FND_TOP/bin/xdfcmp.pl XX/ apps/

Advertisements

ROWNUM vs ROW_NUMBER

In Oracle,we often use ROWNUM pseudo column to generate a sequence of numbers dynamically.But using ROWNUM has its own pros and cons.This post will explain what are the drawbacks,how it can be overcome and what are the other options other than ROWNUM.

Lets take our test table TEST_ROWNUM , a single column table with numeric data type.Values in the column range from 1..100 and it has been loaded twice, so it has duplicate values of 1..100.

Image

Now,let us include ROWNUM in the query and from the snapshot below, we can see that  ROWNUM column’s value runs continuously after 100th row fetch from the table.

Image

This time, let us include the ORDER BY clause in the same query and check the output.

Image

From the snapshot above, you can see that on including the ORDER BY clause in the query, the ROWNUM column’s value gets jumbled. The order is lost and no more we see the values being fetched in sequence.

The above example has proved that when it comes to generating a sequence of numbers dynamically in a query ROWNUM is not that reliable.The alternate option is to use ROW_NUMBER analytical function and the below example will help you understand how it can be used.

Syntax : ROW_NUMBER() OVER (PARTITION BY <column> ORDER BY <column> <ASC/DESC>)

Image

From the above snapshot, you can see that ROW_NUMBER analytical function is able to generate a sequence of number even if we order by a specific column, whereas ROWNUM pseudo column is not able to.

ROWNUM is useful when you have to limit a number of row fetch, without worrying about the exact data being fetched. For ex. If a specific column can have duplicate values and if you want to just check if atleast one row is available with that value then we can use ROWNUM < 2 or any number to limit the row fetch.

ROW_NUMBER is more useful when you have to generate a sequence of numbers after sorting the data fetch on specific column or limiting data fetched after sorting for ex. Top 10 salary of a dept , type of requirements.

Since ROWNUM is generated before sorting so it can be used in WHERE clause whereas ROW_NUMBER cannot be used in WHERE clause, it can be used to filter only after sorting, by using an outer query.

Explain Plans:

ROWNUM:

Image

ROW_NUMBER

Image

From the above snapshots, we can see that cost and bytes are same for both the type of queries, but in case of ROW_NUMBER, data is fetched based on WINDOW sort than ordinary SORT.

Note: If you use any analytic functions then it will result the Window functions plan to be included in the plan.I will explain Window functions in a separate post in detail.

RESULT CACHE function in Oracle

In 11g, Oracle has included a new feature called RESULT_CACHE.On including the key word RESULT_CACHE in a function,the results of a function with along with parameters is stored in SGA and thus helps in improving the performance of the function.On repeated execution of the function with same parameters, oracle directly picks the value from SGA, instead of executing the function again.It is ideal to use this option in functions which are expected to be repeatedly used in loops.

From the snapshot below you can see how on 2nd time run of same function for same set of data takes zero seconds for execution.

Code:

CREATE FUNCTION GET_NAME (p_empno IN PLS_INTEGER)
RETURN VARCHAR2
RESULT_CACHE — Key word
AS
l_ename scott.emp.ename%TYPE;
BEGIN
SELECT ename
INTO l_ename
from SCOTT.EMP
WHERE empno = p_empno;

RETURN l_ename;
END GET_NAME;
/

Image

You may experience similar performance even without mentioning the key word RESULT_CACHE.It is ideal to test in bulk data.Will update this post with add another test case with bulk data.

CSV to Column & Column to CSV in Oracle

In Oracle ,if we have to convert a list of comma separated values to column or column values to comma separated values then we may have to write a piece of code in PL/SQL to loop through the values and create a CSV or vice versa.

We can also achieve this requirement by using DBMS_UTILITY package.The below mentioned example will explain you how it can be achieved.

Code:

DECLARE
v_list VARCHAR2(32000);
v_tablen BINARY_INTEGER;
v_tab DBMS_UTILITY.UNCL_ARRAY;
BEGIN
FOR i IN (SELECT ename FROM scott.emp) LOOP
v_list:=i.ename||’,’||v_list;
END LOOP;

v_list:=SUBSTR(v_list,1,length(v_list)-1);

–Convert comma separated list of values into table —
dbms_utility.comma_to_table(v_list,v_tablen,v_tab);

FOR i IN 1..v_tab.COUNT LOOP
dbms_output.put_line(‘Ename : ‘||v_tab(i));
END LOOP;

–Convert Table values into comma separated list of values —
dbms_utility.table_to_comma(v_tab,v_tablen,v_list);
dbms_output.put_line(v_list);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error : ‘||SQLERRM);
END;
/

Image

From the above snapshot you can see how DBMS_UTILITY.COMMA_TO_TABLE converts a list of CSV to Array of data.Similarly you can see how DBMS_UTILITY.TABLE_TO_COMMA is used to achieve otherwise too.The only drawback is that we can pass only a list of VARCHAR2 values to the package, so we can convert only values upto 32K.

This solution is more ideal,when we know exactly how many number of values have to be transposed.

Oracle CONTINUE Statement

Prior to Oracle 11g, incase if we had to break a loop’s iteration then EXIT statement was the only available option.But again EXIT statement had drawbacks as it will pass the control totally out of the loop.There was no option to conditionally ignore processing a set of statements and still continue processing the next iteration.Come 11g, CONTINUE statement does that job.

Using CONTINUE statement we can break out of the loop and process next iteration.We can also include specific condition to validated and also use label to switch back to specific loop.The below mentioned example will help you understand better.

Code:

DECLARE
v_count PLS_INTEGER;
BEGIN
<<FIRST_LOOP>>
FOR i IN 1..20 LOOP
DBMS_OUTPUT.PUT_LINE(‘i : ‘||i);
<<SECOND_LOOP>>
FOR j IN 1..20 LOOP
CONTINUE FIRST_LOOP WHEN j > 10; — Continue statement with condition.
DBMS_OUTPUT.PUT_LINE(‘j : ‘||j);
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error : ‘||SQLERRM);
END;

Output:

In the below output you can see that the second loop’s iteration breaks once j > 10 and control is transferred to first loop.

Image

CONTINUE is very useful when we have many conditions to be validated.It helps in avoiding lengthy IF THEN ELSE statements to validate and process.

Generate Date Stream using PL/SQL

When working in finance or accounting related modules,we often we get requirement to generate a stream of dates to generate Amortization schedules,Loan EMI Schedules or Rent Stream for Leases.We can achieve this requirement in Oracle using PIPELINED function.

In general a Function is bound to return only one value, but we can use pipelined function to produce result in table format. In the below mentioned example, i have used PIPELINED function along with CONNECT BY to generate a stream of dates between a given range. PIPELINED function returns the values as soon as it is generated.So you can notice in the code below that values fetched are not returned using RETURN statement.

 Code:

 CREATE OR REPLACE PACKAGE test_utilities_pkg AS

 TYPE g_DateStream_rec IS RECORD
    ( seq_no  INTEGER
    , seq_date DATE);

 TYPE g_DateList IS TABLE OF g_DateStream_rec;

 FUNCTION get_month_range (p_from_date DATE,
     p_to_date DATE) 
     RETURN test_utilities_pkg.g_DateList PIPELINED;

 END test_utilities_pkg;

CREATE OR REPLACE  PACKAGE BODY test_utilities_pkg AS
/*———————————————————-
| Function to generate dates between range |
————————————————————-*/
FUNCTION get_month_range (p_from_date DATE,
                                                  p_to_date DATE) 
                                                  RETURN test_utilities_pkg.g_DateList PIPELINED AS
BEGIN
     FOR select_Month_rec IN (SELECT LEVEL,ADD_MONTHS(p_from_date, LEVEL-1) date_stream
                                                FROM dual
                                                CONNECT BY LEVEL <= MONTHS_BETWEEN (last_day(p_to_date)+1, TRUNC(p_from_date,’MM’) )) LOOP 
              PIPE ROW (select_Month_rec);
     END LOOP;
 RETURN;
END get_month_range;

END test_utilities_pkg;
/

SELECT *
FROM  TABLE(test_utilities_pkg.get_month_range(to_date(’01-JAN-2001′,’DD-MON-YYYY’),to_date(’31-DEC-3000′,’DD-MON-YYYY’)))
ORDER BY 2;

Output:

Image

From the output above you can see the function has generated a stream of dates.