Oracle Forms: How To Create Progress Bar In Oracle Forms

Purpose

Create progress bar in oracle forms

Step1:

Create New Form using scott user.

Step2:

Create 2 blocks

1.       Non-Database Block. Named as progress bar

2.       Non-Database Block. But same as EMP Table

Step3:

Create Package Specification with below mention Code:

PACKAGE progress_bar IS

  ----------------------------------------------- Public Variable

bar_id ITEM; -- id of the bar

view_id VIEWPORT ; -- id of the canvas

full_width NUMBER; -- full width before modifying

return_item VARCHAR2(32); -- return item

Initialized BOOLEAN;

--------------------------------------------- Public Procedures

PROCEDURE Initialize_progress_bar ;

PROCEDURE Show_progress_bar (percent IN NUMBER := 0);

PROCEDURE Hide_progress_bar ;

END progress_bar;

Create Package Body with below mention Code:

----------Package body-----------------------

PACKAGE BODY progress_bar IS

    PROCEDURE Show_bar (percent IN NUMBER := 0) IS

     bar_width NUMBER;     IT_ID1 ITEM;

    BEGIN

        bar_width := ROUND((progress_bar.full_width)/ 100 * percent,2);

        SET_ITEM_PROPERTY(progress_bar.bar_id,WIDTH, bar_width);

        COPY(RTRIM(TO_CHAR(ROUND(percent,0)) || '%'),'progress_bar.percent');

        SYNCHRONIZE;

        /*IF percent >= 100 THEN -- job completed

            progress_bar.Hide_progress_bar;

        END IF;*/

    END Show_bar;

    PROCEDURE Initialize_progress_bar IS

        it_id varchar2(200);IT_ID1 ITEM;

    BEGIN

        /*IF progress_bar.Initialized THEN

            MESSAGE('A progress bar is already initialized');

            RAISE FORM_TRIGGER_FAILURE;

            ELS*/IF progress_bar.full_width IS NULL THEN

            progress_bar.bar_id :=FIND_ITEM('progress_bar.bar');

            progress_bar.full_width := TO_NUMBER(GET_ITEM_PROPERTY(progress_bar.bar_id, WIDTH));

            IT_ID1 := Find_Item('progress_bar.bar');

            SET_ITEM_PROPERTY(it_id1,VISIBLE,PROPERTY_TRUE);

            IT_ID1 := Find_Item('progress_bar.PERCENT');

            SET_ITEM_PROPERTY(it_id1,VISIBLE,PROPERTY_TRUE);

        END IF;

        IF progress_bar.Return_item IS NULL THEN

            it_id := :SYSTEM.TRIGGER_ITEM;

            progress_bar.Return_item :=NAME_IN('SYSTEM.TRIGGER_ITEM');

        END IF;

        progress_bar.Initialized := TRUE;

        Show_bar(0);

    END;

    PROCEDURE Show_progress_bar (percent IN NUMBER := 0) IS

        BEGIN

            IF NOT progress_bar.Initialized THEN

                MESSAGE('No progress bar has been initialized');

                RAISE FORM_TRIGGER_FAILURE;

            END IF;

            Show_bar(percent);

        END ;

    PROCEDURE Hide_progress_bar IS

        IT_ID1 ITEM;

        BEGIN

            IT_ID1 := Find_Item('progress_bar.bar');

            SET_ITEM_PROPERTY(it_id1,VISIBLE,PROPERTY_FALSE);

            IT_ID1 := Find_Item('progress_bar.PERCENT');

            SET_ITEM_PROPERTY(it_id1,VISIBLE,PROPERTY_FALSE);            -- SET_ITEM_PROPERTY(progress_bar.bar_id,WIDTH, 0);

            progress_bar.full_width := NULL;

            GO_ITEM(progress_bar.return_item);-- GO_ITEM(:SYSTEM.TRIGGER_ITEM);

            progress_bar.Return_item := NULL;

            progress_bar.Initialized := FALSE;--SET_WINDOW_PROPERTY('PROCESSING_bar',VISIBLE,PROPERTY_FALSE);

        END Hide_progress_bar;

    END;

----------End of Package body-----------------------

Step4:

Create When-Button-Pressed Trigger on Button “PB_POPULATE_Data” in Progress_Bar Block:

DECLARE

CURSOR C1 IS select * from emp e;

P_PERCENT NUMBER(3):=0;

C_DONE NUMBER:=1;

BEGIN

    select count(*) INTO :GLOBAL.C_TOT from emp e;

                        PROGRESS_BAR.INITIALIZE_PROGRESS_BAR;

                        Go_Block('EMP');

                        First_Record;

                              FOR CL_REC IN C1 LOOP

                        -- :global.c_totis the total number of records to be processed.

                        --c_done total number of records processed.

                              :EMPNO    := CL_REC.EMPNO;

                              :ENAME    := CL_REC.ENAME;

                              :JOB      := CL_REC.JOB;

                              :MGR      := CL_REC.MGR;

                              :HIREDATE := CL_REC.HIREDATE;

                              :SAL      := CL_REC.SAL;

                              :COMM     := CL_REC.COMM;

                              :DEPTNO   := CL_REC.DEPTNO;

                              Next_Record;

                              C_DONE:=C_DONE+1;

                              P_PERCENT := TRUNC(C_DONE*100/:GLOBAL.C_TOT);

                                    IF P_PERCENT >= 100 THEN

                                          P_PERCENT := 100;

                                    END IF;

                              --commit;

                              PROGRESS_BAR.SHOW_PROGRESS_BAR(P_PERCENT);

                              END LOOP;  

                              First_Record;

message('Process has been completed successfully');

END;

Step5:

Compile all and run the form.

Click to download complete sample package

Oracle PL/SQL: Dynamic DML Execution in REALTIME

Purpose:
To create dynamic DML in real time and execute it with multiple criteria.

In this practice we only create Insert command and execute it in the Block.

---SampleTable

CREATE TABLE SAMPLETABLE

(

  ITEMCODE NUMBER(4),

  ITEMNAME VARCHAR2(100),

  DEPTNO   NUMBER(3),

  DNAME    VARCHAR2(100),

  STOCK    NUMBER

);

---SampleTable

---Sample Records for SampleTable

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1001, 'Item 1', 111, 'Department 111', 21);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1002, 'Item 2', 111, 'Department 111', 44);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1003, 'Item 3', 111, 'Department 111', 400);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1004, 'Item 4', 111, 'Department 111', 235);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1005, 'Item 5', 112, 'Department 112', 567);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1006, 'Item 6', 112, 'Department 112', 11);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1007, 'Item 2', 112, 'Department 112', 234);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1008, 'Item 1', 112, 'Department 112', 0);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1001, 'Item 1', 113, 'Department 113', 21);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1002, 'Item 2', 113, 'Department 113', 44);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1003, 'Item 3', 113, 'Department 113', 400);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1004, 'Item 4', 113, 'Department 113', 235);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1005, 'Item 5', 113, 'Department 113', 567);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1006, 'Item 6', 113, 'Department 113', 11);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1007, 'Item 2', 113, 'Department 113', 234);

insert into Sampletable (ITEMCODE, ITEMNAME, DEPTNO, DNAME, STOCK)

values (1008, 'Item 1', 113, 'Department 113', 0);

---Sample Records for SampleTable

---Dynamic_DML_LOG

Create Table Dynamic_DML_LOG(

Date$        Date Default Sysdate,

StartOn      Number(6) Default To_Char(Sysdate,'hhmiss'),

EndsOn       Number(6) ,

Remarks      Varchar2(4000)

);

---Dynamic_DML_LOG

--Dynamic_DML Procedure

Create Or Replace Procedure Dynamic_Dml(Table_Name  Varchar2,

                                        Column_Name Varchar2,

                                        Criteria    Varchar2,

                                        Switchto    Varchar2) Is

  Vtab       Varchar2(4000) := Upper(Table_Name);

  Vcol       Varchar2(4000) := Upper(Column_Name);

  Vcriteria  Varchar2(4000) := Criteria;

  Vswitchto  Varchar2(4000) := Switchto;

  Cntr       Number := 0;

  Chk_Tab    Number := 0;

  Chk_Col    Number := 0;

  Val        Varchar2(4000);

  Verrors    Varchar2(4000);

  Start_Time Number := 0;

  End_Time   Number := 0;

  Cursor c(Tab_Name Varchar2) Is

    Select t.Column_Name

      From User_Tab_Cols t

     Where t.Table_Name = Tab_Name

     Order By t.Column_Id;

Begin

  Start_Time := To_Char(Sysdate, 'hhmiss');

  ---checking table existance

  Select Count(*)

    Into Chk_Tab

    From User_Tab_Cols t

   Where t.Table_Name = Vtab;

  If Chk_Tab = 0 Then

    Verrors  := 'Table Not Found In DataBase';

    End_Time := To_Char(Sysdate, 'hhmiss');

    Insert Into Dynamic_Dml_Log

      (Date$, Starton, Endson, Remarks)

    Values

      (Sysdate,

       Start_Time,

       End_Time,

       'Error with Table=' || Vtab || ' Column=' || Vcol || ' Criteria=' ||

       Vcriteria || ' Value Switch To=' || Vswitchto || ' :: ' || Verrors);

  End If;

  ---checking table existance

  ---checking column existance 

  If Chk_Tab > 0 Then

    Select Count(*)

      Into Chk_Col

      From User_Tab_Cols t

     Where t.Table_Name = Vtab

       And t.Column_Name = Vcol;

 

    If Chk_Col = 0 Then

      Verrors  := 'Coulmn Not Found In Table';

      End_Time := To_Char(Sysdate, 'hhmiss');

      Insert Into Dynamic_Dml_Log

        (Date$, Starton, Endson, Remarks)

      Values

        (Sysdate,

         Start_Time,

         End_Time,

         'Error with Table=' || Vtab || ' Column=' || Vcol || ' Criteria=' ||

         Vcriteria || ' Value Switch To=' || Vswitchto || ' :: ' || Verrors);

    End If;

  End If;

  ---checking column existance 

  If Chk_Tab > 0 And Chk_Col > 0 Then

    Val := 'INSERT INTO ' || Vtab || '(';

    ---LOOP FOR INSERT TO TABLE COLUMNS

    For i In c(Vtab) Loop

      Val := Val || Chr(10) || i.Column_Name || ',';

    End Loop;

    Val := Substr(Val, 1, Instr(Val, ',', -1) - 1);

    Val := Val || Chr(10) || ')' || Chr(10) || '  SELECT';

    ---LOOP FOR INSERT VALUES FROM TABLE COLUMNS

    For v In c(Vtab) Loop

      If Vcol = v.Column_Name Then

        Val := Val || Chr(10) || q'<'>' || Vswitchto || q'<'>' || ',';

      Else

        Val := Val || Chr(10) || v.Column_Name || ',';

      End If;

    End Loop;

    Val := Substr(Val, 1, Instr(Val, ',', -1) - 1);

    Val := Val || Chr(10) || ' FROM ' || Vtab || ' Where ' || Vcol || '=' ||

           q'<'>' || ':$CriValue$' || q'<'>';

    Val := Replace(Val, ':$CriValue$', Vcriteria);

    Execute Immediate (Val);

    End_Time := To_Char(Sysdate, 'hhmiss');

    Insert Into Dynamic_Dml_Log

      (Date$, Starton, Endson, Remarks)

    Values

      (Sysdate,

       Start_Time,

       End_Time,

       'Record(s) Inserted Successfully Table=' || Vtab || ' Column=' || Vcol ||

       ' Criteria=' || Vcriteria || ' Value Switch To=' || Vswitchto);

  End If;

Exception

  When Others Then

    Verrors  := Sqlerrm;

    End_Time := To_Char(Sysdate, 'hhmiss');

    Insert Into Dynamic_Dml_Log

      (Date$, Starton, Endson, Remarks)

    Values

      (Sysdate,

       Start_Time,

       End_Time,

       'Error with Table=' || Vtab || ' Column=' || Vcol || ' Criteria=' ||

       Vcriteria || ' Value Switch To=' || Vswitchto || ' :: ' || Verrors);

End;

/

--Dynamic_DML Procedure

Select * From Sampletable;

---------------------------------------------------

Begin

  Dynamic_DML('Sampletable','ITEMcode','1001','1000');

  Commit;

End;

/

---------------------------------------------------

Select * From Sampletable;

Select * From Dynamic_DML_LOG;