Posted by: shijesh | November 9, 2014

UTL_FILE – Improving Performance (Part 1)

We most often use UTL_FILE in oracle to unload the data into flat files like CSV files. Although UTL_FILE is very useful, it is relatively slow. However, there are ways by which we can improve the data unload speed using UTL_FILE.

Let see one of the way by which we can improve the performance.

Each write operation performed by UTL_FILE is expensive. So, one way to improve performance would be to reduce the number of write operation.

Eg. Suppose we have to unload 100,00 rows.

1. Slower Approach –> Writing each row 1 by 1. So, to unload 100,000 rows we will have 100,000 write operation.

2. Little better/Faster Approach –> Write a group of row (say 100) at a time. So, in this case number of write operation is reduced and hence performanc is impoved.

Let us see an example:-

Below code is slower approach and it took 2.155 seconds.


DECLARE
   L_File_Handle Utl_File.File_Type;
  
   CURSOR C1
   IS
   SELECT Level , 'A' , 'B' FROM Dual CONNECT BY Level <= 147000;
 
  Type T1_Rec
   IS
   Record
   ( Col1 NUMBER , Col2 VARCHAR2(1) , Col3 VARCHAR2(1));  

  Type T1_Tab
   IS
   TABLE OF T1_Rec;

  L_Rec T1_Tab;
  L_Row    VARCHAR2(100);
  L_Buffer VARCHAR2(32767);
  l_delimiter     CHAR(1) := chr(10);

  BEGIN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE , 'DD-MON-YYYY HH:MI:SS'));
    L_FILE_HANDLE := UTL_FILE.FOPEN('DATA_UNLOAD','DATA.TXT','W');

    OPEN C1;
    FETCH C1 Bulk Collect INTO L_Rec;
    CLOSE C1;

    FOR I IN 1..L_Rec.Count
    LOOP
        L_Row := L_Rec(I).Col1 || ',' || L_Rec(I).Col2 || ',' || L_Rec(I).Col3 || L_DELIMITER ;
        Utl_File.Put_Line( L_FILE_HANDLE , L_Row);
 END LOOP;
 Utl_File.Fclose(L_File_Handle);
 DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE , 'DD-MON-YYYY HH:MI:SS'));
 END;

Below code gives the same result but in 0.956 second.

 DECLARE
  L_File_Handle Utl_File.File_Type;
  CURSOR C1
  IS
    SELECT Level , 'A' , 'B' FROM Dual CONNECT BY Level <= 147000;
  
  Type T1_Rec
  IS
  Record
 ( Col1 NUMBER , Col2 VARCHAR2(1) ,Col3 VARCHAR2(1));

  Type T1_Tab IS TABLE OF T1_Rec;
  L_Rec T1_Tab;
  L_Row       VARCHAR2(100);
  L_Buffer    VARCHAR2(32767);
  l_delimiter CHAR(1) := chr(10);
BEGIN
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE , 'DD-MON-YYYY HH:MI:SS'));
  L_FILE_HANDLE := UTL_FILE.FOPEN('DATA_UNLOAD','DATA.TXT','W');
  OPEN C1;
  FETCH C1 Bulk Collect INTO L_Rec;
  CLOSE C1;
  FOR I IN 1..L_Rec.Count
  LOOP
    L_Row := L_Rec(I).Col1 || ',' || L_Rec(I).Col2 || ',' || L_Rec(I).Col3 || L_DElimiter ;
 
   /*Instead of writing directly to file , data is stored in varible */
  IF LENGTH(L_Buffer) + LENGTH(L_Row) > 32700 THEN
    Utl_File.Put_Line( L_FILE_HANDLE , L_Buffer);
    L_BUFFER := L_ROW;
  ELSE
    L_Buffer := L_Buffer || L_Row;
  END IF;
  END LOOP;

  Utl_File.Put_Line(L_File_Handle ,L_Buffer);
  Utl_File.Fclose(L_File_Handle);
  Dbms_Output.Put_Line(TO_CHAR(Sysdate , 'DD-MON-YYYY HH:MI:SS'));

END;
Posted by: shijesh | June 20, 2013

Checking Oracle DB Characterset

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_CHARACTERSET’;

Untitled

Posted by: shijesh | August 9, 2011

Download to Excel (XML)

At present APEX provide download to CSV only. If we want to download it to EXCEL , we need to use some reporting tool or we can create our own PL/SQL package.

Here I tried to create a package in PL/SQL which will download the file in XML Spreadsheet 2003 format which will open in MS Excel. It supports formatting. At present I have given option to download the spreadsheet in two different style.

Demo Link : http://apex.oracle.com/pls/apex/f?p=12060:26

Soon I will be posting the link to the package.

Posted by: shijesh | June 15, 2010

APEX 4.0 and jQuery Modal Dialog

Couple of months back I posted how to create jQuery Modal Form in APEX 3.2
Here is the link for the same : jQuery Modal Form

Recently APEX 4.0 has been released and the good news is that it includes jQuery and jQuery-ui. Now, there is no need to put a reference to jQuery , jQuery-ui and the corresponding css file.

I created a sample page show how to create jQuery Modal Form in APEX 4.0 in apex.oracle.com
jQuery Modal Form in APEX 4.0

If you want to see the code , send a mail to apexconsultant11@gmail.com and i will send you the credential.

Posted by: shijesh | June 2, 2010

Multiple Interactive Report on same page

Example

Steps to create multiple IR on same page:-

1) Create page 1 with an Interactive Report.

2) Create page 2 with another Interactive Report.

3) Create page 3.

  • Add 2 HTML Region , one for each IR.
  • Edit the first HTML Region. Go to Source Tab and add the below code
    <iframe src ="f?p=&APP_ID.:1:&SESSION." height=600 width=600 frameborder=0>
    </iframe>

     

  • Edit the second HTML Region.Go to Source Tab and add the below code.
    <iframe src ="f?p=&APP_ID.:2:&SESSION." height=600 width=600 frameborder=0>
    </iframe>

     

Thats it!!!!

Posted by: shijesh | May 23, 2010

Vertical Report – Multi Column

Example

The steps below is based on above mentioned example in which I have used EMP table and Theme 20. The report has 4 columns.

Steps for creating Multi Column Vertical Report.

  1. Create a new report template (eg. Multi Column – Vertical Report).
  2. Edit the newly created template (eg. Multi Column – Vertical Report).
  3. Create a report and select the above created report new report tempage ( i.e. Multi Column – Vertical Report) as Report template.

 

1.       Creating a new report template for Multi Column Vertical Report

  • Go to Shared component.
  • Click on Templates (Under User Interface)
  • Click on Create >  button.
  • Select Report in Template Type and click on Next Button.
  • Select From Scratch and click on Next Button.
  • Type the name for your template Eg. Multi Column – Vertical Report. Leave the Theme dropdown with default value. In Template Class dropdown , select Value Attribute Pair. In template type  select  Named Column (row template). Click on create.

2.       Edit the newly created template

  • Now, from the list of template click on newly created template (eg. Multi Column – Vertical Report) to edit it.
  • In Row Template 1 enter the below code
    <tr>
        <td>
            <table width="100%" cellpadding="0" cellspacing="0" border="0">
                <tr><th class="t20ReportHeader">EMPNO</th></tr>
                <tr><th class="t20ReportHeader">ENAME</th></tr>
                <tr><th class="t20ReportHeader">JOB</th></tr>
                <tr><th class="t20ReportHeader">HIREDATE</th></tr>
                <tr><th class="t20ReportHeader">SAL</th></tr>
            </table>
        </td>
        <td>
            <table width="100%" cellpadding="0" cellspacing="0" border="0">
                <tr><td class="t20data">#1#</td></tr>
                <tr><td class="t20data">#2#</td></tr>
                <tr><td class="t20data">#3#</td></tr>
                <tr><td class="t20data">#5#</td></tr>
                <tr><td class="t20data">#6#</td></tr>
            </table>
        </td>

    In row template 1 Condition select  Use Based on PL/SQL Expression.
    Row template 1 Expression enter  mod(#rownum#,4)=1

  • In Report Template 2 and Report template 3 enter the below code.
    <td>
        <table width="100%" cellpadding="0" cellspacing="0" border="0">
            <tr><td class="t20data">#1#</td></tr>
            <tr><td class="t20data">#2#</td></tr>
            <tr><td class="t20data">#3#</td></tr>
            <tr><td class="t20data">#5#</td></tr>
            <tr><td class="t20data">#6#</td></tr>
        </table>
    </td>

    In Row Template 2 Condition select Use Based on PL/SQL Expression.
    In Row Template 2 Expression enter mod(#rownum#,4)=2
    In Row Template 3 Expression enter mod(#rownum#,4)=3

  • In Report Template 4 enter the below code
    <td>
        <table width="100%" cellpadding="0" cellspacing="0" border="0">
            <tr><td class="t20data">#1#</td></tr>
            <tr><td class="t20data">#2#</td></tr>
            <tr><td class="t20data">#3#</td></tr>
            <tr><td class="t20data">#5#</td></tr>
            <tr><td class="t20data">#6#</td></tr>
        </table>
    </td>
    </tr>
    <tr>
        <td colspan="2" class="t20seperate"><br /></td>
        <td colspan="2" class="t20seperate"><br /></td>
        <td colspan="2" class="t20seperate"><br /></td>
        <td colspan="2" class="t20seperate"><br /></td>
        <td colspan="2" class="t20seperate"><br /></td>
    </tr>
    
  • In row template 4 Condition select  Use Based on PL/SQL Expression.
    Row template 4 Expression enter  mod(#rownum#,4)=0
  • In Before row enter the below code
    <table cellpadding="0" cellspacing="0" border="0" summary=""#REPORT_ATTRIBUTES#>#TOP_PAGINATION#<tr><td><table cellpadding="0" cellspacing="0" border="0" summary=""> 
  • In After rows enter
    </table>
    <div class="t20CVS">#EXTERNAL_LINK##CSV_LINK#</div>
    </td></tr>#PAGINATION#</table>
    
  • Click on apply change

  3.       Creating a report based on our new report template

  •  Create a simple report ( eg. Select * from emp).
  • In Region Attribute , under Report Template select the template which we created above (eg. Multi Column – Vertical Report) from the dropdown and click on Create Region Button.

Thats it….  

Posted by: shijesh | April 10, 2010

JQuery Modal Form in APEX

Today I’m going to document the process to create a JQuery Modal Form in Oracle Application Express.
First lets see an example : JQuery Modal Form

Now heres the step to create a page similar to the one show in the above link.

  1. Create a simple interactive report (in the example the report is based in table called PERSON with ID , FIRST_NAME , LAST_NAME and SEX as its field
  2. Create a button in the interactive report region. Edit this button -> Go to URL Redirect tab.
    In Target is dropdown select URL.
    In URL Text area enter the below code

     javascript:openForm();
  3. Next create a HTML Region with 2 Textfield (P3_FIRST_NAME and P3_LAST_NAME and radio group P3_SEX).
    Edit this HTML Region -> Go to Header and Footer tab
    In the Region Header enter the below code

    <div id="ModalForm" title="Add Person" style="display:none">
    <p class="msg">All the fields are mandatory</p>

    In the Region Footer enter the below code

    </div>

    The key to create Modal Form in APEX using JQuery is to create a HTML region with different items ( do not add any button) , then adding DIV tag with an ID to the Region Header.

  4. Next step would be to add javascript and JQuery code in the HTML Header of the page. Note: In my example I have used validation which does not allow to save the data if the fields are blank. For simplicity, I’m not adding any validation here , but if you want to do validation view the source code of my page.
    <link rel="stylesheet" href = "http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/themes/
    redmond/jquery-ui.css" type="text/css" />
    
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.js"> </script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/jquery-ui.js"> </script>
    <script type="text/javascript">
    $( function() {
       $('#ModalForm').dialog(
       {
            modal : true ,
            autoOpen : false ,
            buttons  : {
                Cancel : function() {
                    closeForm();
                } ,
                Add : function() {
                         addPerson();
                    }
                }
    
       });
    });
    
    function openForm()
    {
        $('#ModalForm').dialog('open');
    }
    
    function closeForm()
    {
        $('#ModalForm input[type="text"]').val('');
        $('#ModalForm').dialog('close');
    }
    
    function addPerson()
    {
       var radio_value = $x('P3_SEX_0').checked ? 'M' : 'F';
       var ajaxRequest = new htmldb_Get( null , &APP_ID. , 'APPLICATION_PROCESS=addPerson', 0);
       ajaxRequest.add( 'P3_FIRST_NAME', $v('P3_FIRST_NAME'));
       ajaxRequest.add( 'P3_LAST_NAME', $v('P3_LAST_NAME'));
       ajaxRequest.add( 'P3_SEX', radio_value);
       ajaxRequest.get();
       ajaxRequest = null;
       closeForm();
       gReport.search('SEARCH');
    }
    </script>
    
  5. Final step would be to create an OnDemand Process – addPerson. For this Go to Shared Compnent -> Click on Application and Create and OnDemand Porcess. For my example I have used the below code
    declare
       fname varchar2(100);
       lname varchar2(100);
       male_female char(1);
    begin
       fname := :P3_FIRST_NAME;
       lname := :P3_LAST_NAME;
       male_female := :P3_SEX;
       insert into person( first_name , last_name , sex )
       values( fname , lname , male_female);
       htp.prn('Record added successfully');
    EXCEPTION
    when others then
       htp.prn('Error adding record');
    end;
    
Posted by: shijesh | April 4, 2010

JQuery Tab in APEX

Creating a JQuery Tab in APEX is quite simple and it also gives a very good look and feel to the APEX application.
Here is an EXAMPLE of APEX Application with JQuery Tab.

As you can see in the Application, it has 2 tabs – Employee and Chart. Well let me tell you in advance, these two tabs are actually normal report region and chart region. Got Confused !!!. Let me explain how can we represent different regions in APEX as JQuery Tab.

Edit the Employee Region which contain a simple SQL report.
Go to Header and Footer tab.
In the Region Header enter

 <div id = "tabs-1"> 

In the Region Footer enter

</div>

Edit the 2nd region, in my Example Chart Region which contain a simple chart.
Go to Header and Footer tab.
In the Region Header enter

<div id = "tabs-2">

In the Region Footer enter

</div>

For each region you have in your page you need to repeat the above step and make sure the DIV id is different .

Now, create a HTML Region with Region Template as No Template and add the below code in the Region Source.

<div id="tabs">
<ul>
 <li><a href="#tabs-1">Employee</a></li>
 <li><a href="#tabs-2">Chart</a></li>
</ul>
</div>

Notice the 2 li tag in the above code , first one is for 1st tab (Employee) and second one is for second tab (Chart)

Finally add the below code to the HTML Page Header.

<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/themes/redmond/jquery-ui.css" type="text/css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.js"> </script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/jquery-ui.js"> </script>
<script type="text/javascript">
$(function() {
$("#tabs").tabs();
$x("tabs").appendChild( $x("tabs-1"));
$x("tabs").appendChild( $x("tabs-2"));
});
</script>

In the above code, line 6 creates the tab , line# 7 adds the first Region with id tabs-1 (Employee Region) to the firsts tab container and line# 7 adds the Region with id tabs-2 (Chart Region) to tab container.

Thats it !!!

Posted by: shijesh | March 21, 2010

AJAX – The Simplest Example

It is quite easy to implement AJAX in APEX (Thanks to Carl Backstrom for a clear and conscience document). Here is a sample AJAX code to show how easy it is to use AJAX in APEX application.

The aim of my code was to select PRODUCT_NAME from Select list and populate the textarea with PRODUCT_DESCRIPTION without reloading the page. Here is what I did.

1. Created a select list P1_PRODUCT_NAME and a textarea P1_PRODUCT_DESCRIPTION.

2. In the HTML Form Element Attribute of select list I entered

onchange="getDescription( this , 'P1_PRODUCT_DESCRIPTION')"

3. In the HTML Page Header I entered the javascript which will call the ondemand process.

<script type="text/javascript">
function getDescription( pThis , pDescription)
{
   var ajaxRequest = new htmldb_Get( 'P1_PRODUCT_DESCRIPTION' , &APP_ID. , 'APPLICATION_PROCESS=getDescription' , 0);
   ajaxRequest.add( 'P1_PRODUCT_NAME' , pThis.value);
   var ajaxResult = ajaxRequest.get(); 
}
</script>

4. Last step is to create a ondemand process. For this go to shared component -> Click on Application process and
create a new application process getDescription

declare
   prod_desc varchar2(2000);
   id number;
begin
   id := : P1_PRODUCT_NAME;
   select PRODUCT_DESCRIPTION into prod_desc from DEMO_PRODUCT_INFO
   where PRODUCT_ID = id;
   htp.prn( prod_desc );
end;

Here is the example
E X A M P L E

Posted by: shijesh | February 1, 2010

Switch Row/Column in chart using VBA

Well this Friday I was working on creating a dynamic XY Scattered Chart. The chart came out fantastic but opps with a flaw. The X-axis and the legend use to toggle and I had to manually right click on chart , then choosing Select Data option from context menu and then clicking switch row/column. Well the uses were not happy with this workaround. After googling finally I found the solution. Its just one line of code in vba.

If you want X-Axis to show column data then use the below statement in your VBA


ActiveChart.PlotBy = xlColumns

and if you want X-Axis to show row data then use the below statement in your VBA


ActiveChart.PlotBy = xlRows

Older Posts »

Categories