Executing Dynamic PL/SQL with IN and OUT Parameters Using EXEC_SQL (Doc ID 605765.1)

Last updated on MAY 24, 2023

Applies to:

Goal

 This sample code uses the EXEC_SQL package and allows to execute a dynamic call to a procedure, passing IN and retrieving OUT parameters ( string or number) to the procedure.

More on the EXEC_SQL package can be found in the Forms Builder on-line help.

The Exception handler is quite useful as it allows to trap an underlying ORA-06502 that would only appear as:
FRM-40735: WHEN-BUTTON-PRESSED trigger raised un handled exception ORA-306500
Please beware the EXEC_SQL.BIND_VARIABLE built-in procedure has a 5th. parameter Out_Value_Size that represents the maximum OUT value size in bytes expected for the VARCHAR2 OUT or IN/OUT variables. If no size is specified, the current length of the Value parameter is used.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.