Performance Programming in PL/SQL using Oracle 10G

CREATE OR REPLACE PROCEDURE preformance_compare(iterations PLS_INTEGER)
IS

/* =======================================================================================================================
PROGRAM ID : Procedure preformance_compare.
PROGRAM DESC : This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.
-----------------------------------------------------------------------------------------------------------------------
VERSION DATE CATCH STRING MODIFIED BY REASON
1.0 19.02.2013 Muhammad Asif To measure the Performance of different Oracle Control Structure For While Loop and Hints etc*/
--=================================================================================================--


TYPE NumTab IS TABLE OF t1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF t1.pname%TYPE INDEX BY PLS_INTEGER;

pnums NumTab;
Pnames NameTab;

CURSOR CR1(p_iterations IN NUMBER) IS
SELECT *
FROM t2
WHERE ROWNUM <= p_iterations ;

TYPE MyType IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
COLLType MyType;

a INTEGER;
b INTEGER;
c INTEGER;
d INTEGER;
e INTEGER;
f INTEGER;
g INTEGER;
h INTEGER;
i BINARY_INTEGER := 1;
ln_pnum NUMBER;
lv_pname VARCHAR2(1000);
BEGIN
a:= dbms_utility.get_time;

FOR j IN 1.. iterations LOOP --Load index by table
pnums(j) := j;
pnames(j) := 'Part No. '|| TO_CHAR(j);
END LOOP;

a:= dbms_utility.get_time;
FOR i IN 1..iterations Loop --use FOR loop
INSERT INTO t1 VALUES (pnums(i),pnames(i));
END LOOP;

b := dbms_utility.get_time;

FORALL i IN 1.. iterations -- user FORALL statment
INSERT INTO t2 VALUES (pnums(i), pnames(i));

COMMIT;
c:= dbms_utility.get_time;


OPEN CR1(iterations);
FETCH CR1 BULK COLLECT INTO COLLType;
CLOSE CR1;
FOR CR1 IN 1..CollType.Count LOOP
INSERT INTO t3 VALUES (COLLType(CR1).pnum, COLLType(CR1).pname);
END LOOP;

d:= dbms_utility.get_time;

OPEN CR1(iterations);
LOOP
FETCH CR1 INTO ln_pnum,lv_pname;
EXIT WHEN CR1%NOTFOUND;
INSERT INTO t4 VALUES (ln_pnum,lv_pname);
END LOOP;
e:= dbms_utility.get_time;
BEGIN
WHILE pnums(i) IS NOT NULL LOOP
INSERT INTO t5 VALUES (ln_pnum,lv_pname);
i := i + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
f:= dbms_utility.get_time;
FORALL i IN 1.. iterations -- user FORALL statment
INSERT /*+APPEND*/ INTO t2 VALUES (pnums(i), pnames(i));

COMMIT;
g:= dbms_utility.get_time;

dbms_output.put_line('This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.' );

dbms_output.put_line('Total No of Records Inserted : '||iterations);
dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('FOR ALL loop with /*+APPEND*/ Hint in Insert : '|| TO_CHAR((g - f) / 100));
dbms_output.put_line('FOR ALL loop: '|| TO_CHAR((c - b) / 100));
dbms_output.put_line('WHILE LOOP: '|| TO_CHAR((f - e) / 100));
dbms_output.put_line('FOR loop: '|| TO_CHAR((b - a) / 100));
dbms_output.put_line('FOR BULK Collect loop: '|| TO_CHAR((d - c) / 100));
dbms_output.put_line('LOOP: '|| TO_CHAR((e - d) / 100));

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' ERROR !!!!! ' || '\' || SQLERRM);

END preformance_compare;

--Script

drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
CREATE TABLE t1 (pnum INTEGER, pname VARCHAR2(4000));
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE TABLE t4 AS SELECT * FROM t1;
CREATE TABLE t5 AS SELECT * FROM t1;
set serveroutput on

exec preformance_compare(150000);

SELECT COUNT(1) FROM t1;
SELECT COUNT(1) FROM t2;
SELECT COUNT(1) FROM t3;
SELECT COUNT(1) FROM t4;
SELECT COUNT(1) FROM t5;
SELECT count(1) FROM t2
where rownum <= 50000 ;


--OutPut


This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.
Total No of Records Inserted : 150000
Execution Time (secs)
FOR ALL loop with /*+APPEND*/ Hint in Insert : .24
FOR ALL loop: .31
WHILE LOOP: 6.26
FOR loop: 6.17
FOR BULK Collect loop: 6.61
LOOP: 9.25

How to convert d2k report in excel

How to convert d2k report in excel

first create a  RPT2XLS.pll  as under below :

first step :-

/start****************RPT2XLS(package space)@***********************/

PACKAGE RPT2XLS IS

BOLD constant binary_integer := 1;

ITALIC constant binary_integer := 2;

UNDERLINE constant binary_integer := 4;

PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,

FontName in varchar2 DEFAULT null,

FontSize in binary_integer DEFAULT null,

FontStyle in binary_integer DEFAULT null,

FontColor in binary_integer DEFAULT null,

BgrColor in binary_integer DEFAULT null,

Format in varchar2 DEFAULT null  );

PROCEDURE new_line;

PROCEDURE run;

PROCEDURE release_memory;

END;

/end****************RPT2XLS(package space)@***********************/

/start****************RPT2XLS(package body)@***********************/

PACKAGE BODY RPT2XLS IS

TYPE ExcelCell IS RECORD(RowNo binary_integer,

ColNo binary_integer,

Val varchar2(2000),

FontName varchar2(20),

FontSize binary_integer,

FontStyle binary_integer,

FontColor binary_integer,

BgrColor binary_integer,

Format varchar2(60) DEFAULT null );

TYPE ExcelCells IS TABLE OF ExcelCell;

Cell ExcelCells := ExcelCells();

CurrentRow binary_integer := 1;

PROCEDURE new_line IS

BEGIN

CurrentRow := CurrentRow + 1;

END;

PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,

FontName in varchar2 DEFAULT null,

FontSize in binary_integer DEFAULT null,

FontStyle in binary_integer DEFAULT null,

FontColor in binary_integer DEFAULT null,

BgrColor in binary_integer DEFAULT null,

Format in varchar2 DEFAULT null) IS

EGIN

Cell.Extend;

Cell(Cell.Last).RowNo := CurrentRow;

Cell(Cell.Last).ColNo := ColNo;

Cell(Cell.Last).Val := CellValue;

Cell(Cell.Last).FontName := FontName;

Cell(Cell.Last).FontSize := FontSize;

Cell(Cell.Last).FontStyle := FontStyle;

Cell(Cell.Last).FontColor := FontColor;

Cell(Cell.Last).BgrColor := BgrColor;

Cell(Cell.Last).Format := Format;

END;

PROCEDURE run IS

Application OLE2.OBJ_TYPE;

Workbooks OLE2.OBJ_TYPE;

Workbook OLE2.OBJ_TYPE;

Worksheets OLE2.OBJ_TYPE;

Worksheet OLE2.OBJ_TYPE;

WorkCell OLE2.OBJ_TYPE;

WorkColumn OLE2.OBJ_TYPE;

WorkFont OLE2.OBJ_TYPE;

WorkInterior OLE2.OBJ_TYPE;

ArgList OLE2.LIST_TYPE;

BEGIN

Application := OLE2.create_obj('Excel.Application');

OLE2.set_property(Application, 'Visible', 1);

Workbooks := OLE2.get_obj_property(Application, 'Workbooks');

Workbook := OLE2.invoke_obj(WorkBooks, 'Add');

Worksheets := OLE2.get_obj_property(Workbook, 'Worksheets');

Worksheet := OLE2.get_obj_property(Application, 'ActiveSheet');

for i in Cell.First .. Cell.Last

loop

if Cell(i).Val is not null then

ArgList := OLE2.create_arglist;

OLE2.add_arg(ArgList, Cell(i).RowNo);

ole2.add_arg(ArgList, Cell(i).ColNo);

WorkCell := OLE2.get_obj_property(Worksheet, 'Cells', ArgList);

ole2.destroy_arglist(ArgList);

ole2.set_property(WorkCell, 'Value', Cell(i).Val);

ole2.set_property(WorkCell, 'NumberFormat', Cell(i).Format);

WorkFont := OLE2.get_obj_property(WorkCell, 'Font');

WorkInterior := ole2.Get_Obj_Property(WorkCell, 'Interior');

if Cell(i).FontName is not null then

OLE2.set_property(WorkFont, 'Name', Cell(i).FontName);

end if;

if Cell(i).FontSize is not null then

OLE2.set_property(WorkFont, 'Size', Cell(i).FontSize);

end if;

if mod(Cell(i).FontStyle, 2) = 1  then

OLE2.set_property(WorkFont, 'Bold', 1);

end if;

if mod(Cell(i).FontStyle, 4) > 2  then

OLE2.set_property(WorkFont, 'Italic', 1);

end if;

if mod(Cell(i).FontStyle, 8) > 4  then

OLE2.set_property(WorkFont, 'Underline', 2);

end if;

 if Cell(i).FontColor is not null then

OLE2.set_property(WorkFont, 'ColorIndex', Cell(i).FontColor);

end if;

if Cell(i).BgrColor is not null then

OLE2.set_property(WorkInterior, 'ColorIndex', Cell(i).BgrColor);

end if;

OLE2.release_obj(WorkInterior);

OLE2.release_obj(WorkFont);

OLE2.release_obj(WorkCell);

end if;

end loop;

ArgList := ole2.create_arglist;

ole2.add_arg(ArgList, 'A:Z'); 

WorkColumn := ole2.Get_Obj_Property(WorkSheet, 'Columns', ArgList); 

ole2.destroy_arglist(ArgList);

ole2.invoke(WorkColumn, 'AutoFit');

OLE2.release_obj(WorkColumn);

OLE2.release_obj(Worksheet);

OLE2.release_obj(Worksheets);

OLE2.release_obj(Workbook);

OLE2.release_obj(Workbooks);

OLE2.release_obj(Application);

END;

PROCEDURE release_memory IS

BEGIN

Cell := ExcelCells();

SYS.DBMS_SESSION.free_unused_user_memory;

END;

END;

/end****************RPT2XLS(package body)@***********************/

Second Step:

Use RPT2XLZ.PLL in report builder 

attach lib fiel in reort builder 

attach lib file in reort builder

After report trigger 

function AfterReport return boolean is

begin

rpt2xls.release_memory;

return (TRUE);

end;

format trigger 

function F_21FormatTrigger return boolean is

begin

--  RPT2XLS.PUT_CELL(1,:countasper);--,null,null,03,null,null,null);

--RPT2XLS.PUT_CELL(2,:p_date);

RPT2XLS.PUT_CELL(3,'Grand Total',null,null,03,null,null,null);

RPT2XLS.PUT_CELL(4,:sumoftotalweight1);

RPT2XLS.PUT_CELL(5,:sumofoutcenwtsoc);

RPT2XLS.PUT_CELL(6,:sumtotalwt);

--RPT2XLS.PUT_CELL(7,:govt_rule);

RPT2XLS.PUT_CELL(8,:sum_soc_comm_tot);

RPT2XLS.PUT_CELL(9,:sum_cf_4);

RPT2XLS.PUT_CELL(10,:sum_cf_4);

RPT2XLS.PUT_CELL(11,:sum_cf_4);

RPT2XLS.PUT_CELL(12,:sum_cf_4);

rpt2xls.new_line;

return (TRUE);

end;

when button pressed

procedure U_1ButtonAction is

begin

rpt2xls.run;

end;

then compile report and run properly 

Report Convert to Excel Sheet RPT2XLS

first of all you should create library

or you should create two packages in Programm Unit

name is

1. RPT2XLS (Package Spec)

2. RPT2XLS (Package Body)

and in each you should describe the row and column and each thing of related to excell.

then you go to LAYOUT MODEL

and create a button name is U_EXCEL1

and type is =  pl/sql

and call 

RPT2XLS.RUN;

Then you define to each field that where you put like

example

------------ --------- Code for Report Header (ABDUL KHALIQUE 08/08/08)--- --------- --------- ---

 RPT2XLS.put_ cell(1, 'GL/Account Code :', FontSize => 8,FontStyle => RPT2XLS.BOLD+ RPT2XLS.ITALIC+ RPT2XLS.UNDERLINE) ;

Report Layout Modes | Confine Mode & Flex Mode

Confine Mode & Flex Mode

Changing the current mode (Confine or Flex)

To set or override Confine mode: 

·         In the Paper Layout view, click the Confine On button or the Confine Off button in the toolbar to toggle the mode:

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

To set or override Flex mode: 

·         In the Paper Layout view, click the Flex On button or the Flex Off button in the toolbar to toggle the mode:

On: parent borders "stretch" when child objects are moved against them. The child object maintains the same distance from the side it moves against.

Off: parent borders remain fixed when child objects are moved against them. 

Declaring an Autonomous Function in a Package

Example 6-43 Declaring an Autonomous Function in a Package

CREATE OR REPLACE PACKAGE emp_actions AS  -- package specification
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- package body
-- code for function raise_salary
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER IS
     PRAGMA AUTONOMOUS_TRANSACTION;
     new_sal NUMBER(8,2);
   BEGIN
     UPDATE employees SET salary = salary + sal_raise WHERE employee_id = emp_id;
     COMMIT;
     SELECT salary INTO new_sal FROM employees WHERE employee_id = emp_id;
     RETURN new_sal;
   END raise_salary;
END emp_actions;
/

Create GLOBAL TEMPORARY Table.

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

Reading Csv Files with Text_io in Oracle D2k Forms

Reading Csv Files with Text_io in Oracle D2k Forms

Below is the example to read and import comma delimited csv file in oracle forms with D2k_Delimited_String package. This package is available in D2kdlstr.pll library.

To download D2kdlstr.Pll Click Here

Create the following procedure in program unit of Oracle forms.

Procedure Import_csv_file (I_FILENAME IN VARCHAR2) Is
   -- Text File Type
   Infile        Text_Io.File_Type;
   Linebuf       Varchar2 (4000);
   V_Getstring   Varchar2 (100);

   -- Field Values Array
   Type Fieldvalue Is Table Of Varchar2(100)
      Index By Binary_Integer;

   Fv            Fieldvalue;
   Rec_Count Number := 0;
Begin
   Infile := Text_Io.Fopen (I_FILENAME, 'R');
   -- Read File

   Loop
           ---
           Rec_Count := Rec_Count + 1;
      Text_Io.Get_Line (Infile, Linebuf);
      Linebuf := Linebuf || ',';
         -- read from 1 to number of occurrences of comma or any other delimiter
         -- below giving example for 3 occurrences
         For I In 1 .. 3
         Loop
            Fv (I) := D2k_Delimited_String.Getstring (Linebuf, I, False, ',');
         End Loop;

         Begin
               ---
            Insert Into yourtable (col1, col2, col3)
                                    Values ( Fv(1), Fv(2), Fv(3));

         Exception
            When Others
            Then
               Message (Sqlerrm);
         End;
   End Loop;

   Text_Io.Fclose (Infile);
Exception
   When No_Data_Found Then
  
   -- End Of The Text File Reached.... Then Save...
       commit_form;
--  Message(Sqlerrm);
      Text_Io.Fclose (Infile);
      Message ('Import Completed.');
   When Others Then
      Text_Io.Fclose (Infile);
      message(sqlerrm);
End;

Using Text_IO To Read Files in Oracle D2k

Using Text_IO To Read Files in Oracle D2k

Problem

You want to read a file from D2k client and want to store its content in Oracle database. But if you will insert row by row from client to server it will take more time and increase lot of network traffic / round trips.

Solution

Solution for this problem is to store the content of the text file into array and then pass it to database procedure and insert record through that procedure. Here is the example step by step:

1)  Create a package in Oracle database.

Create or Replace Package DB_insert
as
Type textrow is table of Varchar2(1000)
    index by binary_integer;

Procedure Insert_into_table (iarray in textrow);
End;
 /

Create or Replace Package Body DB_insert
as
Procedure Insert_into_table(iarray in textrow)
is
Begin
   For i in 1..iarray.count loop
       Insert into Dummytbl values (iarray(i));
       -- you can extract the content from iarray(i) to insert values into multiple fields
       --
   End Loop;
 Commit;
End;
/

2)   Now in D2k write a procedure to read text file and store it into array and pass it to above package.

Procedure Read_File (ifilename in Varchar2)
is
 infile Text_IO.File_type;
irow DB_insert.textrow;
nelm number := 1;
Begin
   infile := text_io.fopen(ifilename, 'r');
Loop
     text_io.get_line(infile, irow(nelm));
     nelm := nelm + 1;
End Loop;
Exception
   when no_data_found then
     -- end of file reached
    text_io.fclose(infile);
   message('Read Completed.');
   -- pass it to database
   DB_insert.insert_into_table(irow);
  message('Data saved.');
  when others then
    if text_io.is_open(infile) then
       text_io.fclose(infile);
    end if;
   message(sqlerrm);
End;