Using arrays in Oracle scripts

Question

I want to create a script file to INSERT/UPDATE values in to a table. For the sake of maintenance, I want to create an array (multi-dimensional possibly) and loop through it to insert the records. Here are the table columns:

TABLE NAME: REF_PROPERTY

REF_PROPERTY_ID Number PK
PACKAGE_ID Number FK
PROPERTY_NAME VarChar
PROPERTY_VALUE VarChar
OVERRIDE_VALUE Number

TABLE NAME: PACKAGES

PACKAGE_ID Number PK
PACKAGE_NAME VarChar
PACKAGE_VERSION VarChar

We have several packages (10-15) and each package has its own set of properties (40 - 50 properties); some are the same. I want to have one script file for all of these packages rather than separate script files for each of them.

What I want to do is something like this (writing this is sort of in non-PL/SQL format and more of a coding manner): Declare an array variable:

V_MYARR VARRAY;
myArr[1] = ['v2|v3|v4', 'property1', 'propertyValue1', 1];
myArr[2] = ['v1|v2|v3|v4', 'property2', 'propertyValue2', 0];
myArr[3] = ['v2|v3|v4|v5', 'property3', 'propertyValue3', 1];

By doing the above, I can maintain the properties and their values in the scripts. When we add a new property for any package version, we put it here and run the script.

Lets say I want to run the script for package version v2. Declare variables for the package name and version and get the package ID:

V_PKG_NAME VarChar := 'Package1'
V_PKG_VERSION VarChar := 'v2';
V_PKG_ID Number;
SELECT PACKAGE_ID INTO V_PKG_ID WHERE PACKAGE_NAME = V_PKG_NAME AND PACKAGE_VERSION = V_PKG_VERSION;

Now, I want to loop through the array, split up the first element by pipe delimiter (|), check if the version v2 exists there and if so, insert/update the values in the table.

for int arrRow in myArr
   --split the first element of myArr[arrRow][0] by | and store in a new array variable splitArr
   --loop through splitArr
   --if V_PKG_VERSION == splitArr[I] then insert.
v2 exists in myArr[arrRow][0] so the insert would be (check if value exists before insert):
INSERT INTO REF_PROPERTY VALUES (REF_PROP_SEQ.nextval, V_PKG_ID, myArr[arrRow][1], myArr[arrRow][2], myArr[arrRow][3]);

end loop;

Similarly, when I want to run the script for package version v3, I set the package version to v3 and then run the script.

V_PKG_VERSION VarChar := 'v3';

I hope this gives a good idea of what I am looking to do and if this is a good approach to minimalizing the number of script files. And finally, if something like this is doable in Oracle PL/SQL.

Thanks in advance!

Answer 1

If I were you, I'd set up either an insert or a merge statement that handles the insert/updates.

Something like:

merge into packages tgt
using (select 'Package1' package_name, 'v1' package_version from dual union all
       select 'Package1' package_name, 'v2' package_version from dual union all
       select 'Package1' package_name, 'v3' package_version from dual union all
       select 'Package1' package_name, 'v4' package_version from dual union all
       select 'Package2' package_name, 'v1' package_version from dual union all
       select 'Package2' package_name, 'v2' package_version from dual) src
  on (tgt.package_name = src.package_name and tgt.package_version = src.package_version)
when not matched then
  insert (tgt.package_id, tgt.package_name, tgt.package_version)
  values (packages_seq.nextval, src.package_name, src.package_version);

merge into ref_property tgt
using (select pkg.package_id,
              property_name,
              property_value,
              override_value
       from   packages pkg
              inner join (select 'Package1' package_name, 'v1' package_version, 'property2' property_name, 'propertyValue2' property_value, 0 override_value from dual union all
                          select 'Package1' package_name, 'v2' package_version, 'property1' property_name, 'propertyValue1' property_value, 1 override_value from dual union all
                          select 'Package1' package_name, 'v2' package_version, 'property2' property_name, 'propertyValue2' property_value, 0 override_value from dual union all
                          select 'Package1' package_name, 'v2' package_version, 'property3' property_name, 'propertyValue3' property_value, 1 override_value from dual union all
                          select 'Package1' package_name, 'v3' package_version, 'property1' property_name, 'propertyValue1' property_value, 1 override_value from dual union all
                          select 'Package1' package_name, 'v3' package_version, 'property2' property_name, 'propertyValue2' property_value, 0 override_value from dual union all
                          select 'Package1' package_name, 'v3' package_version, 'property3' property_name, 'propertyValue3' property_value, 1 override_value from dual union all
                          select 'Package1' package_name, 'v4' package_version, 'property1' property_name, 'propertyValue1' property_value, 1 override_value from dual union all
                          select 'Package1' package_name, 'v4' package_version, 'property2' property_name, 'propertyValue2' property_value, 0 override_value from dual union all
                          select 'Package1' package_name, 'v4' package_version, 'property3' property_name, 'propertyValue3' property_value, 1 override_value from dual union all
                          select 'Package1' package_name, 'v5' package_version, 'property3' property_name, 'propertyValue3' property_value, 1 override_value from dual union all
                          select 'Package1' package_name, 'v1' package_version, 'property1' property_name, 'propertyValue2' property_value, 0 override_value from dual union all
                          select 'Package1' package_name, 'v1' package_version, 'property4' property_name, 'propertyValue4' property_value, 1 override_value from dual union all
                          select 'Package1' package_name, 'v2' package_version, 'property4' property_name, 'propertyValue5' property_value, 0 override_value from dual) prop
                on (pkg.package_name = prop.package_name and pkg.package_version = prop.package_version))
  on (tgt.package_id = src.package_id and tgt.property_name = src.property_name)
when not matched then
  insert (tgt.package_id, tgt.property_name, tgt.property_value, tgt.override_value)
  values (src.package_id, src.property_name, src.property_value, src.override_value)
--optional:
when matched then
  update set tgt.property_value = src.property_value,
             tgt.override_value = src.override_value;

The advantage of doing it like this is that you can set different values for each property, in case there are changes between versions of the package.

By using MERGE, your script can be rerun without messing stuff up. I've added in the update clause on the second statement just in case you want the ability to update the previously set values.