Skip to content

Examples of Refactoring PL/SQL Code

This page includes some common hints when refactoring plsql code in files that are new in a customization.

In the code for unpack_check_insert___ and unpack_check_update___ it is important not to add extra checks in the loop. It should come before the line ptr_:=NULL; or before line EXCEPTION. This to avoid overtake of the method.

Observe: Template is on the left side and the file to be refactored on the right side in the pictures below.

Avoid extra code in unpack loop

ptr_ := NULL;  
   WHILE (Client_SYS.Get_Next_From_Attr(attr_, ptr_, name_, value_)) LOOP  
      Trace_SYS.Field(name_, value_);  
      IF (name_ = 'PART_NO') THEN  
         IF (instr(value_, '%') > 0) THEN  
            Error_SYS.Item_General(lu_name_, 'PART_NO', 'NOPERCENTSIGN: The field [:NAME] cannot contain % sign.');  
         ELSE  
            newrec_.part_no := value_;  
         END IF;  
         Part_Catalog_API.Exist(newrec_.part_no);

The extra check for instr(value_, '%') > 0) should be after the unpack loop. Remember to change value_ to newrec_.part_no when you have the check after the loop.

Avoid extra code in unpack loop (example 2)

This will lead to a bad overtake  of the code when splitting.

Misplacement of code after the loop

This will lead to a bad overtake  of the code when splitting. In this case the code should be just before EXCEPTION

Correct placement of additional code after BEGIN, but before ptr_:= NULL;

This will lead to a good override of the code when splitting and not a bad overtake.

Correct placement of extra code before EXCEPTION

This will lead to a good override of the code when splitting and not a bad overtake.

Value_

Value_ is in the new template VARCHAR2(4000) it was before VARCHAR2(2000). Only to replace the value to the one on the left hand side.

Objversion has changed place in Insert___ and Update___

&TABLE in template but &VIEW in original file

According to the template we should go towards the &TABLE instead of the &VIEW. In this case you need to change to &TABLE. Make sure that you get the column names right after you transformed it to table instead of the view. The column name could be different in the table and the view.

&TABLE in the template but real table name in the original file

In this case you should change to &TABLE if it is the same as the table name.

Clob

Set of rowstate_ in Finit_State_Machine

It is very common that the rowstate is set as in the left example below. With the new code generation template it should be set as on the right side. This is only to change to the new way in order to avoid an overtake.

Custom Rowversion

You might experience that you get a line like -- Set custom Rowversion. In this case it is the model that is not correct and you should change the model. But investigate, because you might want to use standard rowversion instead of a custom rowversion.  In the Example below the model should have "number" instead.

In the model

CLOB

Clob  can't be included in the insert___ method. Instead you have to have a method that insert___ the CLOB the first time. In Insert___ you write empty_clob(). Clob should always be NOT NULL in the database.

BLOB

Blob  can't be included in the insert___ method. Instead you have to have a method that insert___ the BLOB  for the first time. In Insert___ you write empty_blob(). Blob should always be NOT NULL in the database.

Basic Data Translation

In order to avoid overtake on Basic_Data_Translation you need to set a property in the model.

View looks like this (no changes needed, but only for you to understand)

Row Level Security

In many places we have an extra where clause to check whether a user is allowed to access the data or not. We have it e.g. for site, company and employee data. The way to not get an overtake on the view in such instance is to set a property on the attribute.

Dynamic Reference (Conditional Compilation)

In certain occasions you have a reference to an attribute in e.g. Unpack_Check_Update___, but the reference is only valid if the other component is installed. In order to solve where you have this dynamic reference you need to set a property to the attribute in the model.

Micro Cache

Micro Cache will be generated in the auto generated part of the new file and in order to  get support for it in the generation step you need to add i in the model.