SQL & PL/SQL

The script is as follows:

------------------------- (A_moid in number,
A_moclass in number,
A_parameterid in number,
A_parametersetid in number)
as
-- Update UDA_Parameter_Set

BEGIN
-- Declare variables below for UDA_Parameter_Set cursor
Declare
A_Class_PARAMETERSETID number(10);
A_EXTDATASOURCEID NUMBER(10);
A_UDANAME CHAR(32 byte);
A_UDATIMESPAN CHAR(32 byte);
A_UDATIMESPANVALUE number;
A_UDATMPLTCLASS number(10);
A_UDATMPLTSET number(10);
A_UDAEXTERNAL CHAR(32 byte);
A_UDAEXTERNALOBJECTNAME VARCHAR2(254 byte);
A_UDATMPLTMODEL number(10);
A_UDAEXTMEASTABLENAME VARCHAR2(254 byte);
A_UDAEXTDATECOLNAME CHAR(32 byte);
A_UDAEXTOBJECTCOLNAME VARCHAR2(254 byte);
A_UDAEXTMEASSETTABLENAME VARCHAR2(254 byte);
A_UDAEXTENDDATECOLNAME CHAR(32 byte);
A_UDAEXTOBJECTCOLDATATYPE VARCHAR2(254 byte);
A_UDAEXTJOINCOLNAME VARCHAR2(254 byte);
A_UDATYPE CHAR(32 byte);
-- Declare variables below for UDA_Parameter cursor
A_PARMNAME CHAR(32 byte);
A_PARMTYPE CHAR(32 byte);
A_PARMDESCRIPTION varchar2(254 byte);
A_PARMCOMMENT varchar2(254 byte);
A_PARMDATATYPE CHAR(32 byte);
A_PARMFIXED number;
A_PARMPRECISION number;
A_PARMHIGHLIMIT float(126);
A_PARMLOWLIMIT float(126);
A_PARMDEFAULTVALUENUM float(126);
A_PARMDEFAULTVALUECHAR varchar2(254 byte);
A_PARMDEFAULTVALUEDATE date;
A_PARMROLLOVER CHAR(32 byte);
A_PARMREADINGTYPE CHAR(32 byte);
A_PARMMISSINGRESULTACTION CHAR(32 byte);
A_PARMMISSINGRESULTVALUE float(126);
A_PARMUOM number(10);
A_PARMPOLLUTANT number(10);
A_PARMFUEL number(10);
A_PARMEQUIPMENT number(10);
A_PARMSCC CHAR(32 byte);
A_PARMEXTPARAMETERCOLNAME CHAR(32 byte);
A_PARMREPORTCOLLABEL CHAR(32 byte);
A_PARMREPORTCOLUNITS CHAR(32 byte);
A_PARMDISPLAYFORMAT CHAR(32 byte);
A_PARMTMPLTPARMID number(10);
A_PARMPICKLIST number(10);
A_parameterid1 number := A_parameterid;
A_parametersetid1 number := A_parametersetid;



cursor Cur_UDA_PARAMETER_SET is
select PARAMETERSETID,EXTDATASOURCEID, UDANAME, UDATIMESPAN, UDATIMESPANVALUE,
UDATMPLTCLASS, UDATMPLTSET, UDAEXTERNAL, UDAEXTERNALOBJECTNAME, UDATMPLTMODEL, UDAEXTMEASTABLENAME,
UDAEXTDATECOLNAME, UDAEXTOBJECTCOLNAME, UDAEXTMEASSETTABLENAME, UDAEXTENDDATECOLNAME,
UDAEXTOBJECTCOLDATATYPE, UDAEXTJOINCOLNAME, UDATYPE from UDA_Parameter_Set where UDATMPLTCLASS =
A_MOCLASS;

Cursor Cur_UDA_Parameter is
select PARMNAME, PARMTYPE, PARMDESCRIPTION, PARMCOMMENT, PARMDATATYPE, PARMFIXED,
PARMPRECISION, PARMHIGHLIMIT, PARMLOWLIMIT, PARMDEFAULTVALUENUM, PARMDEFAULTVALUECHAR,
PARMDEFAULTVALUEDATE, PARMROLLOVER, PARMREADINGTYPE, PARMMISSINGRESULTACTION,
PARMMISSINGRESULTVALUE, PARMUOM, PARMPOLLUTANT, PARMFUEL, PARMEQUIPMENT, PARMSCC,
PARMEXTPARAMETERCOLNAME, PARMREPORTCOLLABEL, PARMREPORTCOLUNITS, PARMDISPLAYFORMAT,
PARMTMPLTPARMID, PARMPICKLIST
from UDA_PARAMETER where PARAMETERSETID = A_Class_PARAMETERSETID;


BEGIN


open Cur_UDA_PARAMETER_SET;
Loop
Fetch Cur_UDA_PARAMETER_SET into A_Class_PARAMETERSETID, A_EXTDATASOURCEID,
A_UDANAME, A_UDATIMESPAN, A_UDATIMESPANVALUE, A_UDATMPLTCLASS,
A_UDATMPLTSET, A_UDAEXTERNAL, A_UDAEXTERNALOBJECTNAME, A_UDATMPLTMODEL, A_UDAEXTMEASTABLENAME,
A_UDAEXTDATECOLNAME, A_UDAEXTOBJECTCOLNAME, A_UDAEXTMEASSETTABLENAME, A_UDAEXTENDDATECOLNAME,
A_UDAEXTOBJECTCOLDATATYPE, A_UDAEXTJOINCOLNAME, A_UDATYPE;

Exit when Cur_UDA_PARAMETER_SET%notfound;

BEGIN

INSERT INTO UDA_PARAMETER_SET(PARAMETERSETID, EXTDATASOURCEID, UDANAME, UDATIMESPAN,
UDATIMESPANVALUE, UDATMPLTCLASS, UDATMPLTSET, UDAEXTERNAL, UDAEXTERNALOBJECTNAME,
UDATMPLTMODEL, UDAEXTMEASTABLENAME, UDAEXTDATECOLNAME, UDAEXTOBJECTCOLNAME,
UDAEXTMEASSETTABLENAME, UDAEXTENDDATECOLNAME, UDAEXTOBJECTCOLDATATYPE, UDAEXTJOINCOLNAME,
UDATYPE) VALUES (A_parametersetid1, A_EXTDATASOURCEID, A_UDANAME, A_UDATIMESPAN, A_UDATIMESPANVALUE,
0,A_UDATMPLTSET, A_UDAEXTERNAL, A_UDAEXTERNALOBJECTNAME, A_UDATMPLTMODEL, A_UDAEXTMEASTABLENAME,
A_UDAEXTDATECOLNAME, A_UDAEXTOBJECTCOLNAME, A_UDAEXTMEASSETTABLENAME, A_UDAEXTENDDATECOLNAME,
A_UDAEXTOBJECTCOLDATATYPE,A_UDAEXTJOINCOLNAME,A_UDATYPE );

-- Update UDA_Parameter

Begin

open Cur_UDA_PARAMETER;
Loop
Fetch Cur_UDA_PARAMETER into A_PARMNAME, A_PARMTYPE, A_PARMDESCRIPTION,
A_PARMCOMMENT, A_PARMDATATYPE, A_PARMFIXED, A_PARMPRECISION, A_PARMHIGHLIMIT,
A_PARMLOWLIMIT, A_PARMDEFAULTVALUENUM, A_PARMDEFAULTVALUECHAR, A_PARMDEFAULTVALUEDATE,
A_PARMROLLOVER, A_PARMREADINGTYPE, A_PARMMISSINGRESULTACTION, A_PARMMISSINGRESULTVALUE,
A_PARMUOM, A_PARMPOLLUTANT, A_PARMFUEL, A_PARMEQUIPMENT, A_PARMSCC, A_PARMEXTPARAMETERCOLNAME,
A_PARMREPORTCOLLABEL, A_PARMREPORTCOLUNITS, A_PARMDISPLAYFORMAT, A_PARMTMPLTPARMID,
A_PARMPICKLIST;
Exit when Cur_UDA_PARAMETER_SET%notfound;
BEGIN
INSERT INTO UDA_PARAMETER(PARMORIGPARMID, PARAMETERSETID, PARMNAME, PARMTYPE,
PARMDESCRIPTION, PARMCOMMENT, PARMDATATYPE, PARMFIXED, PARMPRECISION, PARMHIGHLIMIT,
PARMLOWLIMIT, PARMDEFAULTVALUENUM, PARMDEFAULTVALUECHAR, PARMDEFAULTVALUEDATE,
PARMROLLOVER, PARMREADINGTYPE, PARMMISSINGRESULTACTION, PARMMISSINGRESULTVALUE, PARMUOM,
PARMPOLLUTANT, PARMFUEL, PARMEQUIPMENT, PARMSCC, PARMEXTPARAMETERCOLNAME,
PARMREPORTCOLLABEL, PARMREPORTCOLUNITS, PARMDISPLAYFORMAT, PARMTMPLTPARMID, PARMPICKLIST)
VALUES (A_parameterid1, A_parametersetid1, A_PARMNAME, A_PARMTYPE, A_PARMDESCRIPTION,
A_PARMCOMMENT, A_PARMDATATYPE, A_PARMFIXED, A_PARMPRECISION, A_PARMHIGHLIMIT,
A_PARMLOWLIMIT, A_PARMDEFAULTVALUENUM, A_PARMDEFAULTVALUECHAR,A_PARMDEFAULTVALUEDATE,
A_PARMROLLOVER, A_PARMREADINGTYPE, A_PARMMISSINGRESULTACTION,A_PARMMISSINGRESULTVALUE,
A_PARMUOM, A_PARMPOLLUTANT, A_PARMFUEL, A_PARMEQUIPMENT,A_PARMSCC,A_PARMEXTPARAMETERCOLNAME,
A_PARMREPORTCOLLABEL, A_PARMREPORTCOLUNITS, A_PARMDISPLAYFORMAT,A_PARMTMPLTPARMID,
A_PARMPICKLIST);

/*
UPDATE ID_HIGH_WATER_MARK SET IDHWMMAXIMUMVALUE = (A_parameterid1+1) WHERE
IDHWMATTRIBUTE
= 'PARAMETERID ' AND IDHWMMODULE = 1;
*/
UPDATE ID_HIGH_WATER_MARK SET IDHWMMAXIMUMVALUE = (A_parameterid1+1) WHERE
IDHWMATTRIBUTE
= 'PARAMETERID' AND IDHWMMODULE = 1;
-- Update UDA Parameter MO table

INSERT INTO UDA_PARAMETER_MO(PARAMETERID, CONSTMEASSET, PARMMO, PARMCALCEXPR,
PARMCALCMAPCOMPLETE, PARMMOORIGPARMID) VALUES (A_parameterid1, 0, A_moid, 0, 1,
A_parameterid1);

A_parameterid1 := A_parameterid1 + 1;

End;
End loop;
close Cur_UDA_Parameter;
end;
/*
UPDATE ID_HIGH_WATER_MARK SET IDHWMMAXIMUMVALUE = (A_parametersetid1 + 1) wHERE
IDHWMATTRIBUTE = 'PARAMETERSETID ' AND IDHWMMODULE = 1;
*/

UPDATE ID_HIGH_WATER_MARK SET IDHWMMAXIMUMVALUE = (A_parametersetid1 + 1) wHERE
IDHWMATTRIBUTE = 'PARAMETERSETID' AND IDHWMMODULE = 1;


A_parametersetid1 := A_parametersetid1 + 1;

End;
End loop;
close Cur_UDA_PARAMETER_SET;
End;
End;

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

Any suggestions to improve performance will be greatly appreciated.

Thanks in advance.

Regards,
Raj