X

TimesTen In-Memory Database
for Extreme Performance

Best practices for Pro*C Indicator Variables and PLSQL Procedures

Doug Hood
Evangelist for Oracle TimesTen, Oracle In-Memory and Oracle NoSQL

Introduction

This blog provides some 'best practices' for exception handling and returning SQL error codes in PLSQL routines. This blog also covers using Pro*C indicator variables as inputs and outputs to a database. Some best practices for calling PLSQL routines from Pro*C are provided. Finally some anti patterns are discussed.

These techniques apply to both the Oracle Database (7-20c) and TimesTen (11.2-18c).

 

PLSQL Exception Handling

Many things can go wrong at run-time in a PLSQL program. PLSQL exception handling is a well defined way of handling known and un-known run-time errors. PLSQL exceptions can be defined at the block and procedure/function level. Using PLSQL exception handlers enables code that is easier to write and maintain. If you do not explicitly use exception handling, then un-handled exceptions will propagate up the stack until they are exposed to a host language program like SqlPlus or a Pro*C program. Un-handled exceptions may not provide much useful information and your progam will have already died at the point.

A trivial example of PLSQL exception handling for known and unknown exceptions:

BEGIN

   select emp_id, salary

   INTO myEmpId, MySalary

  from employees

  where emp_id = 1234;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

     dbms_output.put_line('Employee not found');

  WHEN OTHERS THEN

     dbms_output.put_line('An unknown error occurred')

END;

Some guidelines for avoiding and handling exceptions.

Using explicit PLSQL exception handlers allows developers to control program flow to retry or pass back error codes and error messages as needed.

 

PLSQL OUT parameters

PLSQL procedures and functions (+ package procedures/functions) allow you to have parameters which are of mode IN, OUT or INOUT.

The parameters tend to be scalar types. It is best practice to always return the error code and error text as OUT parameters for all PLSQL procedure, functions or package procedures or functions. This enables calling routines to know if there were any problems in the called routines and to pass back meaningful error codes and error messages.

It is best practice to always initialize the value of the ErrorCode and ErrorText OUT parameters as soon as a routine is entered. If your routine encounters a known or unknown exception then your code can determine the errorcode and associated error text and assign then to these OUT parameters. In both cased, you PLSQL code assigns deterministic values to these OUT parameters. eg

CREATE OR REPLACE PROCEDURE foo

  (errCode PLS_INTEGER,

   errText VARCHAR2) IS

BEGIN

   errCode := 0;

   errText := 'None';

   ...

EXCEPTION

  WHEN ... THEN ...

  WHEN OTHERS THEN

     errCode := SQLCODE;

     errText := SUBSTR(SQLERRM, 1, 128)

END;

The above pattern means that valid values of errCode and errText always exist. They either indicated that there was no problem or what the specific problem was.

 

Pro*C Indicator Variables

Indicators variables are used in Oracle languages like Pro*C, Pro*COBOL, Pro*ADA and OCI to help map the semantics of host language [eg C, COBOL or ADA] scalar variables and their associated database table types.

Indicator variables can be used to convey meaning for both input and output. eg a value of -1 on input means that the variable is NULL. On output a value of -1 means that the returned value was NULL. Output values of > 0 or -2 state that truncation occurred.

If you do not use indicator variables for values returned from the database, then you will not know whether the values are non-NULL, NULL or truncated. Best practice is to always use indicator variables for values returned from the database.

 

Pro*C calling Stored Procedures

The following is an example of a PLSQL procedure being called from Pro*C with output paramters:

  • errCode is the SQLCODE. This value cannot be NULL
  • errText is the SQL error text. This value cannot by NULL
  • theCommission is the optional commission value. It may be NULL in the database, but the OUT parameter will not be NULL

CREATE OR REPLACE getComm

(errCode PLS_INTEGER,

  errText VARCHAR2,

  theComm NUMBER) IS

BEGIN

  errCode := 0;

  errText := 'None';

  theComm := 0.0;

  SELECT commission

  INTO theComm

  FROM employees

  WHERE emp_id = 1234;

   IF theComm is NULL THEN

     DBMS_OUT.PUT_LINE('No commission for you');

   END IF:

EXCEPTION

  WHEN NOT_FOUND THEN

     errCode := SQLCODE;

    errText := 'Employee not found';

  WHEN OTHERS THEN

      errCode := SQLCODE;

      errText := SUBSTR(SQLERRM(1, 128);

END;

 

Anti Patterns

Anti-patterns are ineffective solutions where better/simpler solutions exists.

Anti Pattern 1

Using indicator variables for PLSQL routine OUT parameters for error codes and error text. As per the above examples, the OUT value of PLSQL routines for error codes and error text is deterministic, therefore using indicator variables for these values is an anti-pattern.

eg the following example of a Pro*C program calling a PLSQL procedure with indicator variables is un-needed and overly complex:

EXEC SQL EXECUTE

  BEGIN

      myPackage.myProcedure(:errCode:errCodeIndicatorVar , :errText:errTextindatorVar);

  END

END-EXEC

 

The simpler and more effective pattern is:

EXEC SQL EXECUTE

  BEGIN

      myPackage.myProcedure(:errCode, :errText);

  END

 

Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.