PL/SQL

Note: This document is recognized for including obsolete content and is due for being updated. IFS/Design and Rational Rose are no longer used in development of IFS Applications 8. IFS Windows Client and IFS Client Developer are not in use.

Contents

Package conventions

All PL/SQL-procedures and functions will be placed in a package tightly connected to the logical unit. The packages are put in ordinary text files containing all packages (by default) and for ordinary logical units the specification and implementation part of the package are separated into two different files. They are named like <LU>.api and <LU>.apy according to the name conventions above. They should be placed in a directory connected to the component.

File contents

The contents of the two files for PL/SQL-code often referred to as API-file and APY-file is described in separate examples below. The guidelines are described in more detail under IFS/Design.

Documentation

-- Insert___
--    Insert a new LU-instance into the database and return the values
--    for OBJID and OBJVERSION.

Output and logging

The command SHOW ERROR should always be included at the end of each package file.

Coding Structure

It is important that the components within an application are developed using the same techniques even on the code level. Hopefully this will result in better quality and less bugs. This section will handle problems like how declarations are made and how to name the parameters as well as the naming of exception labels. A general description of how a procedure and function is built is given below:

PROCEDURE <proc name> 
   <argument> ) 
IS 
   <local variables> 
   <local cursors> 
BEGIN 
   <procedure code> 
EXCEPTION 
   <exception code> 
END <proc name>;
FUNCTION <func name> (
   <argument> ) RETURN <type>
IS
   <local variables> 
   <local cursors>
BEGIN 
   <function code>
    RETURN(<ret value>);
EXCEPTION 
   <exception code>
END <func name>;

The following indentation rules shall be used (see example):

These blocks should be at the same level CREATE PACKAGE
IS
BEGIN
EXCEPTION
END
These blocks should be indented 3 spaces CURSOR
LOOP
IF
FOR
WHEN

Example

CREATE OR REPLACE PACKAGE BODY Test_API IS

PROCEDURE Get_Looping_Object (
   key_ IN VARCHAR2,
   status_code_ IN VARCHAR2 )
IS
   index_ NUMBER := 3;
   objrec_ object_view%ROWTYPE;
   temp_ VARCHAR2(20);
   serious_err EXCEPTION;
   CURSOR get_row IS
      SELECT *
      FROM object_view
      WHERE objkey = key_
      AND status = status_code_;
BEGIN
   OPEN get_row;
   FETCH get_row INTO objrec_;
   CLOSE get_row;
   LOOP
      SELECT index_+ 1
      INTO index_
      FROM dual;
      IF (sql%NOTFOUND) THEN
         RAISE serious_err;
      END IF;
      IF (index_ = 7) THEN
         EXIT;
      END IF;
   END LOOP;
EXCEPTION
   WHEN serious_err THEN
      Error_SYS.Appl_General(lu_name_, 'SERERR: Serious loop error');
END Get_Looping_Object;

Parameters and Variables

Parameters

Variables

Name conventions

Syntax recommendation

This example is primarily intended to show how the programming standards are used in actual PL/SQL code.

Example of guidelines

$ cat AmBookingRule.api

DEFINE LU  = AmBookingRule
DEFINE PKG = AM_BOOKING_RULE_API

PROMPT Creating &PKG specification

CREATE OR REPLACE PACKAGE &PKG IS

TYPE Codestr_Rec_Type IS RECORD (
   code_a CHAR(10),
   code_b CHAR(10),
   code_c CHAR(10),
   code_d CHAR(10),
   code_e CHAR(10),
   code_f CHAR(10),
   code_g CHAR(10),
   code_h CHAR(10),
   code_i CHAR(10),
   code_j CHAR(10));

TYPE CodePart_Tab_Type IS TABLE OF CHAR(10)
   INDEX BY BINARY_INTEGER;

TYPE CtrlTyp_Tab_Type IS TABLE OF CHAR(10)
   INDEX BY BINARY_INTEGER;

TYPE CtrlVAL_Tab_Type IS TABLE OF CHAR(10)
   INDEX BY BINARY_INTEGER;

PROCEDURE Get_Control_Type (
   rcode_            OUT VARCHAR2,
   control_type_tab_ OUT CtrlTyp_Tab_Type,
   company_          IN  VARCHAR2,
   str_code_         IN  VARCHAR2 );

PROCEDURE Build_Codestr_Rec (
   rcode_             OUT    VARCHAR2,
   codestring_rec     IN OUT Codestr_Rec_Type,
   company_           IN     VARCHAR2,
   str_code_          IN     VARCHAR2,
   control_type_tab_  IN     CtrlTyp_Tab_Type,
   control_value_tab_ IN     CtrlVal_Tab_Type );

END &PKG;
/
SHOW ERROR

UNDEFINE LU
UNDEFINE PKG 
 
$ cat AmBookingRule.apy

-----------------------------------------------------------------
--
-- Package: Booking_Rule_API
--
-- Purpose: Load package for Accounting Module Booking Rules
--
-- Date    Sign  History
-- ------  ----  ------------------------------------------------
-- 951005  ERFO  Testcode to show PL/SQL programming guidelines
--
-----------------------------------------------------------------

DEFINE LU  = AmBookingRule
DEFINE PKG = AM_BOOKING_RULE_API

PROMPT Creating &PKG implementation

CREATE OR REPLACE PACKAGE BODY &PKG IS

-----------------------------------------------------------------
-- Purpose: Get the control type for all specified string codes
-----------------------------------------------------------------

PROCEDURE Get_Control_Type (
   rcode_            OUT VARCHAR2,
   control_type_tab_ OUT CtrlTyp_Tab_Type,
   company_objid_    IN  VARCHAR2,
   str_code_         IN  VARCHAR2 )
IS
   rows_         NUMBER := 0;
   company_desc_ ac_company.company_desc%TYPE;
   CURSOR company IS
      SELECT company_desc
      FROM   ac_company
      WHERE  company = company_objid_
      AND    company_desc IS NOT NULL;
BEGIN
   rcode_ := 'SUCCESS';
   OPEN company;
   FETCH company INTO company_desc_;
   IF (company%NOTFOUND) THEN
      rcode_ := 'NO_COMPANY_FOUND';
   END IF;
   FOR control_type_rec IN (
      SELECT *
      FROM  mj_str
      WHERE company = company_objid_
      AND   str_code = str_code_) 
   LOOP
      rows_ := rows_ + 1;
      control_type_tab_(rows_) := control_type_rec.control_type;
   END LOOP;
EXCEPTION
   WHEN no_data_found THEN
      NULL;
END Get_Control_Type;

-----------------------------------------------------------------
-- Purpose: Build a record of codestrings for specified
--          control tables
------------------------------------------------------------------

PROCEDURE Build_Codestr_Rec (
   rcode_             OUT    VARCHAR2,
   codestring_rec_    IN OUT Codestr_Rec_Type,
   company_           IN     VARCHAR2,
   str_code_          IN     VARCHAR2,
   control_type_tab_  IN     CtrlTyp_Tab_Type,
   control_value_tab_ IN     CtrlVal_Tab_Type )
IS
   ind1_          INTEGER := 0;
   ind2_          INTEGER;
   control_value_ VARCHAR(10);
   control_type_  VARCHAR(10);
   code_part_     VARCHAR(2);
   code_part_tab_ CodePart_Tab_Type;
BEGIN
   rcode_ := 'SUCCESS';
   LOOP
      control_type_  := control_type_tab_(ind1_);
      control_value_ := control_value_tab_(ind1_);
      Get_Codepart_Tab(rcode_, company_, str_code_, control_type_, 
                       code_part_tab_);
      LOOP
         ind2_ := 0;
         code_part_ := code_part_tab_(ind2_);
         Load_Codepart_Value(rcode_, code_part_, control_value_,  
                             codestring_rec_); 
         ind2_ := ind2_ + 1;
      END LOOP;
      ind1_ := ind1_ + 1;
   END LOOP;
EXCEPTION
   WHEN no_data_found THEN      
      NULL;
END Build_Codestr_Rec;

END &PKG;
/
SHOW ERROR

UNDEFINE LU
UNDEFINE PKG