Using Dynamic PL/SQL through DBMS_SQL

This section provides some useful guidelines when developing business logic using DBMS_SQL.

Contents

• Programming guidelines

Programming guidelines

The purpose of this section is to offer some programming guidelines when developing business logic, including the usage of DBMS_SQL. Please note that Dynamic PL/SQL is quite complex and often requires input and advice from a senior software engineer when using the interfaces:

• Please be careful when using semi-colons in the calls to parse. The error messages can be very strange and a seemingly relatively easy problem may take several hours to solve.
• It is good practice to follow some naming conventions between PL/SQL-variables and the corresponding bind variables. An example is that the PL/SQL variable cust_id_ and the bind variable :cust_id_ correspond to each other.
• OUT-parameters from PL/SQL-calls are available through variable_value.
• Result sets from columns in a SELECT-statement may be available through column_value.
• Return values from execute can be strange. The values are different depending on the functionality contained within the statement (i.e. SELECT, DELETE).
• Ordinary SQL-statements may be run by using execute and fetch or the combined version of these two execute_and_fetch.
• Please be careful with the length parameter to bind_variable. Otherwise the complete string may not be handled.
• Please be careful to choose the correct interface of the overloaded procedures bind_variable, column_value etc.

Examples

Below are some examples on how to use Oracle package DBMS_SQL to run business logic within the server environment of Foundation1.

This first example includes a dynamic SELECT-statement and the second example includes a dynamic stored procedure call.

CREATE OR REPLACE FUNCTION Get_Number_Of_Customers (
		company_id_ IN VARCHAR2 ) RETURN NUMBER
IS
	temp_ NUMBER;
	c1_ NUMBER;
	stmt_ VARCHAR2(2000);
	dummy_ NUMBER;
BEGIN
	stmt_ := 'SELECT COUNT(*) FROM DEMO_CUSTOMER '||
		'WHERE COMPANY_ID = :comp';
	c1_ := dbms_sql.open_cursor;
	dbms_sql.parse(c1_, stmt_, dbms_sql.native);
	dbms_sql.define_column(c1_, 1, temp_);
	dbms_sql.bind_variable(c1_, 'comp', company_id_);
	dummy_ := dbms_sql.execute(c1_);
	dummy_ := dbms_sql.fetch_rows(c1_);
	dbms_sql.column_value(c1_, 1, temp_);
	dbms_sql.close_cursor(c1_);
	RETURN(temp_);
EXCEPTION
WHEN OTHERS THEN
	IF (dbms_sql.is_open(c1_)) THEN
		dbms_sql.close_cursor(c1_);
	END IF;
	RETURN(0);
END Get_Number_Of_Customers;
/
SHOW ERROR

CREATE OR REPLACE FUNCTION Get_Number_Of_Customers_2 (
	company_id_ IN VARCHAR2 ) RETURN NUMBER
IS
	temp_ NUMBER;
	c1_ NUMBER;
	stmt_ VARCHAR2(2000);
	dummy_ NUMBER;
BEGIN
	stmt_ := 'BEGIN :count := Get_Number_Of_Customers(:comp); END;';
	c1_ := dbms_sql.open_cursor;
	dbms_sql.parse(c1_, stmt_, dbms_sql.native);
	dbms_sql.bind_variable(c1_, 'count', temp_);
	dbms_sql.bind_variable(c1_, 'comp', company_id_);
	dummy_ := dbms_sql.execute(c1_);
	dbms_sql.variable_value(c1_, 'count', temp_);
	dbms_sql.close_cursor(c1_);
	RETURN(temp_);
EXCEPTION
WHEN OTHERS THEN
	IF (dbms_sql.is_open(c1_)) THEN
		dbms_sql.close_cursor(c1_);
	END IF;
	RETURN(0);
END Get_Number_Of_Customers_2;
/
SHOW ERROR