Writing PL/SQL Rules

PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural language extension to SQL. It is used to develop stored procedures, functions, triggers, and packages that can extend the scope and size of applications and create powerful business logic on the Oracle platform.

PL/SQL applications are used in various industries, from financial to customer service, to automate various processes, such as data entry and retrieval, decision-making, and data analysis.

Restrictions

PL/SQL code blocks must be closed with a period (.)
All PL/SQL program units must begin and end with a keyword such as FUNCTION, PROCEDURE, or BEGIN.
All SQL commands must end in a semi-colon (;).
All PL/SQL declarations must end with a semi-colon (;).
Every PL/SQL program unit must contain at least one executable statement.
Every PL/SQL statement must contain a keyword.
PL/SQL keywords must be written in uppercase.
All variables must be declared before they are used.
PL/SQL Statements must be well-formed and must adhere to a logical structure.
All comments must begin with the /* and end with the */.
Do not use input parameters through the body of the rule. This is a bad style of code, and it is inconvenient during refactoring
Always initialize a local variable!
Do not reuse a local variable for different situations. For example:
1. Bad style: SELECT COL_CASEID INTO v_result FROM TBL_CASE;
2. Good style: SELECT COL_CASEID INTO v_caseId FROM TBL_CASE;

Recommendations

	Description	Example
Rule Name	Rule name should follow UPPER CASE PREFIX (DCM, CLB, SMPL, QA) with underscore with words in camel case. The name of the rule should reflect a rule functionality. Do not use numbers inside a rule name. DCM_Rule12, SMPL_Rule5 - are bad styles.	`QA_actionNqFn, DCM_CSCUseCache`
Rule Description	Each rule should contain a detailed description explaining rule's purpose and rule implementation comments.	This function copies cache data into cache tables and back by Case Id
Rules Parameters	The currently proposed naming convention is UpperCamelCase. The argument is that AppBase capitalizes all SQL output parameters to UPPERCASE.	`Input, CaseId, TaskId`
Rules Placeholders	Rule placeholders should use the UPPER_UNDERSCORE_CASE naming convention.	`@RULEPLACEHOLDER@`
SQL code variable names	The internal variable names should be next: v_<entityNameOrSomething>. ie, first goes prefix that reflects "variable," the following word describes an attribute name or calculated value, logical purpose, etc.	`v_caseTypeId, v_caseId, v_owner`
SQL code variable type	Variable type should follow: UPPER CASE;	`NUMBER; NVARCHAR2(255);`
SQL code the common structure of rule/function	This is just a recommendation. The common structure of the rule/function should be as next. `DECLARE` `<declare local variable section>` `BEGIN` `<inputparemetersinit section>` `<init local variables section>` `<check input parameters section>` `<main section>` `<exit section>` `END;`	`DECLARE` `v_caseid NUMBER;` `v_owner NVARCHAR2(255);` `BEGIN` `v_caseid := :CaseId;` `v_owner := SYS_CONTEXT('CLIENTCONTEXT','AccessSubject');` `IF v_caseId IS NULL THEN EXIT;` `END IF;` `--<do something>` `END;`

Best Practices

Try to handle errors as much as possible.
Try to avoid using numbers as part of the variable name. v_result2, v_result15. Give your variables the context to improve code readability.
Add comments. Use single comments for 1 row and multiple for many rows. Do not use a single comment for multiple.
Do not write a SQL code "too wide". The maximum length is 150 symbols.
Add empty rows between logical operations (code blocks).
Use v_result as a result of function execution. This is good for debugging and fast refactoring.
Do not use many variables with the same name, such as, v_res, v_res1, v_resultOfFunc, etc.

Sample Code

Below is an example of a PL/SQL application that can be used to create a function:

PL/SQL Rule Example

SQL

DECLARE
    --INPUT
    v_TaskId NVARCHAR2(255);
     
    --INTERNAL
    v_ignore INTEGER;
    v_CaseId INTEGER;
    v_CBD NVARCHAR2(255);
    v_ResolutionCode NVARCHAR2(255);
    v_ResolutionId INTEGER;
    v_targetStateCode NVARCHAR2(255);
     
    --LOGGING
    v_message NCLOB;
     
    --temp variables for returns
    v_tempErrMsg NCLOB;
    v_tempErrCd INTEGER;
    v_tempSccss NCLOB;
BEGIN
    --GET CASE ID
    v_Taskid := :TaskId;
    v_CaseId := f_DCM_getCaseIdByTaskId(v_Taskid);
     
    --GET CRN DATA
    BEGIN
        SELECT COL_CDB
        INTO   v_CBD
        FROM   TBL_CDM_CS_CASE_DATA_MODEL
        WHERE  COL_CS_CASE_DATA_MODELCASE = v_CaseId;
    EXCEPTION
    WHEN OTHERS THEN
        v_CBD := NULL; 
    END;
    v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'CBD values was ' || NVL(v_CBD, '-NULL-'));
     
    --CALCULATE CORRECT RESOLUTION ID
    BEGIN
        IF v_CBD IS NULL THEN
            v_ResolutionCode := 'TASK_MOVE_TO_IDENTIFY';
        ELSE
            v_ResolutionCode := 'TASK_MOVE_TO_INVESTIGATE';
        END IF;
         
        SELECT COL_ID
        INTO   v_ResolutionId
        FROM   TBL_STP_RESOLUTIONCODE
        WHERE  UPPER(COL_CODE) = v_ResolutionCode;
    EXCEPTION
    WHEN OTHERS THEN
        v_tempErrCd := 101;
        v_tempErrMsg := 'Could not find Resolution Code with code ' || v_ResolutionCode;
        GOTO cleanup;
    END;
    v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'Resolution ID for  ' || v_ResolutionCode || ' is ' || TO_CHAR(v_ResolutionId));
     
    --ROUTE TASK TO NEW STATE
    v_targetStateCode := 'root_TSK_Status_CLOSED_DEFAULT';
    v_ignore := f_DCM_taskTransitionManualFn(CUSTOMDATA => null,
                                             ErrorCode => v_tempErrCd,
                                             ErrorMessage => v_tempErrMsg,
                                             ResolutionId => v_ResolutionId,
                                             Target => v_targetStateCode,
                                             TaskId => v_Taskid,
                                             WorkbasketId => null);
     
     
    IF v_tempErrCd > 0 THEN
        GOTO cleanup;
    ELSE
        v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'SUCCESS: routed Task to new state');
    END IF;
     
    --CREATE HISTORY RECORD FOR SUCCESS
    v_ignore := f_HIST_createHistoryFn(
        AdditionalInfo => v_message, 
        IsSystem=>0,
        Message=> 'Succesfully routed Task with ' || 'CUST_routeByCBD',
        MessageCode => NULL,
        TargetID => v_Taskid,
        TargetType=>'TASK'
    ); 
     
    --RETURN RESULTS
    :ERRORCODE := 0;
    :ERRORMESSAGE := NULL;
    :SUCCESSRESPONSE := v_message; 
    RETURN;
     
     
    --ERROR BLOCK
    <<cleanup>> 
    v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'WARNING: Issue routing Task');
    v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'ERROR CODE: ' || v_tempErrCd);
    v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'ERROR MESSAGE: ' || v_tempErrMsg);
     
    v_ignore := f_HIST_createHistoryFn(
        AdditionalInfo => v_message, 
        IsSystem=>0,
        Message=> NULL,
        MessageCode => 'GenericEventFailure',
        TargetID => v_Taskid,
        TargetType=>'TASK'
    ); 
     
    :ERRORCODE := v_tempErrCd;
    :ERRORMESSAGE := v_message;
    :SUCCESSRESPONSE := NULL;  
 
    RETURN;
END;

Restrictions

Recommendations

Best Practices

Sample Code

Related Articles