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;