Oracle Database Access Functions

Retrieving values from an Oracle database

Checking values in an Oracle database

About Oracle Database Access Functions

You use Oracle Database Access Functions to retrieve values and check the existence of values in an Oracle reference table. These functions are based on external calls that you must program beforehand. For more information, refer to AccountingIntegrator Rule Engine Exits and External Calls Manual.

The syntax for the function depends on which of the following modules you want to access:

  • Retrieving values from an Oracle database:
    • retrieving the first line of the result of a single-column select:
      $SEARCH
      ("LLT";"75";"Select")
    • retrieving the value returned by an Oracle stored procedure:
      $SEARCH
      ("LLT";"76";Argument&":Procedure")
  • Checking values in an Oracle database:
    • check the existence of a value in the list produced by a single-column select:
      $CHECK
      ("77";Argument&":Select")
    • check the existence of a value returned by an Oracle procedure:
      $CHECK
      ("78";Argument&":Procedure")

For more information, refer to Overview of $SEARCH and $CHECK Function.

Note: These are distinct from Table functions which you use for working with arguments and values in Table objects created within Composer.

$SEARCH("LLT";"75";"Select")

Description

$SEARCH("lllt";"75";"Select") Retrieves the first line of the result of a single-column select

Syntax

$SEARCH("lllt";"75";"Select")

Return

In the event of an SQL error, the Input-Event fails and Composer adds the Oracle error message to the rejection message.  

Note: It is recommended that you store your query phrases in an internal table. The query phrases are then validated before being stored and there is less risk of an error occurring when the phrase is entered.

Parameter

lllt

  • lll represents the length of the value returned by the call (the maximum length varies depending on the data type)
  • t represents the data type of the value returned by the call

75

corresponds to the specific predefined entry point for external calls for accessing data stored in an Oracle database.

Select

is the query to be executed written between double quotes. Since the (") character is used as a separator, you must not use it in the query.

Example

In this example, in the t_product table, the product description is read by means of the product_code contained in the CPROD field provided by the processed Input-Event.

$SEARCH ("050A"; "75"; "SELECT label FROM t_product WHERE product_code = "&CPROD)

$SEARCH("LLT";"76";Argument&":Procedure")

Description

$SEARCH("lllt";"76";Argument&":Procedure") retrieves the value returned by an Oracle stored procedure.

Use this command to retrieve the value returned by an Oracle procedure that has a prototype in the following format:

NAME_PROCEDURE     (IN Parameter_In VARCHAR

OUT Parameter_Out VARCHAR

OUT Return_Code NUMBER)

Syntax

$SEARCH("lllt";"76";Argument&":Procedure")

Return

In the event of an SQL error, the Input-Event fails and Composer adds the Oracle error message to the rejection message.

Parameter

lllt

76

corresponds to the specific predefined entry point for external calls for accessing data stored in an Oracle database.

Argument&":Procedure" is the concatenation of the value of Parameter_In and the name of the procedure preceded by a colon (:). Since the colon (:) and double quote (") characters are used as separators, you must not use them in the procedure or parameter.

Example

In this example, the product description is read by means of the product code contained in the CPROD field (retrieved from the Input-Event) using the proc_libprod procedure.

$SEARCH ("050A";"76"; CPROD&":proc_libprod")

$CHECK("77";Argument&":Select")

Description

$CHECK("77";Argument&":Select") checks the existence of a value in the list produced by a single-column select.

Syntax

$CHECK("77";Argument&":Select")

Return

In the event of an SQL error, the Input-Event fails and Composer adds the Oracle error message to the rejection message.  

Note: It is recommended that you store your query phrases in an internal table. The query phrases are then validated before being stored and there is less risk of an error occurring when the phrase is entered.

Parameter

77

corresponds to the specific predefined entry point for external calls for checking data stored in an Oracle database.

Argument&":Select" is the concatenation of the value to be checked and the query to be run, separated by a colon (:).Sincethe colon (:) and double quote (") characters are used as separators, you must not use them in the query.

Example

This example checks whether the product identified by its product code, which is contained in the CPROD field, is actually stored in the t_product table. If the product does not exist in the table, the Input-Event fails.

$CHECK ("77"; CPROD& ": SELECT code_product FROM t_product")

$CHECK("78";Argument&":Procedure")

Description

$CHECK("78";Argument&":Procedure") returns a value from an external reference table.

Syntax

$CHECK("78";Argument&":Procedure")

The parentheses, double quotes and semicolon are mandatory.

Return

In the event of an SQL error, the Input-Event fails and Composer adds the Oracle error message to the rejection message.

Parameter

78

corresponds to the specific predefined entry point for external calls for checking data stored in an Oracle database.

Argument&":Procedure" is the concatenation of the value to check and the procedure to execute, separated by a colon (:).Sincethe colon (:) and double quote (") characters are used as separators, you must not use them in the query.

Example

In this example, the Oracle proc_ctrlprod procedure is used to check for a product stored in the t_product table.

$CHECK ("78"; CPROD& ": proc_ctrlprod")

Back to top

Related Links