Skip to content

Using Dynamic PL/SQL through DBMS_SQL

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

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 '||<br/>'WHERE COMPANY_ID = :comp';<br/>c1_ := dbms_sql.open_cursor;<br/>dbms_sql.parse(c1_, stmt_, dbms_sql.native);<br/>dbms_sql.define_column(c1_, 1, temp_);<br/>dbms_sql.bind_variable(c1_, 'comp', company_id_);<br/>dummy_ := dbms_sql.execute(c1_);<br/>dummy_ := dbms_sql.fetch_rows(c1_);<br/>dbms_sql.column_value(c1_, 1, temp_);<br/>dbms_sql.close_cursor(c1_);<br/>RETURN(temp_);<br/>EXCEPTION<br/>WHEN OTHERS THEN<br/>IF (dbms_sql.is_open(c1_)) THEN<br/>dbms_sql.close_cursor(c1_);<br/>END IF;<br/>RETURN(0);<br/>END Get_Number_Of_Customers;<br/>/<br/>SHOW ERROR<br/><br/>CREATE OR REPLACE FUNCTION Get_Number_Of_Customers_2 (<br/>company_id_ IN VARCHAR2 ) RETURN NUMBER<br/>IS<br/>temp_ NUMBER;<br/>c1_ NUMBER;<br/>stmt_ VARCHAR2(2000);<br/>dummy_ NUMBER;<br/>BEGIN<br/>stmt_ := 'BEGIN :count := Get_Number_Of_Customers(:comp); END;';<br/>c1_ := dbms_sql.open_cursor;<br/>dbms_sql.parse(c1_, stmt_, dbms_sql.native);<br/>dbms_sql.bind_variable(c1_, 'count', temp_);<br/>dbms_sql.bind_variable(c1_, 'comp', company_id_);<br/>dummy_ := dbms_sql.execute(c1_);<br/>dbms_sql.variable_value(c1_, 'count', temp_);<br/>dbms_sql.close_cursor(c1_);<br/>RETURN(temp_);<br/>EXCEPTION<br/>WHEN OTHERS THEN<br/>IF (dbms_sql.is_open(c1_)) THEN<br/>dbms_sql.close_cursor(c1_);<br/>END IF;<br/>RETURN(0);<br/>END Get_Number_Of_Customers_2;<br/>/<br/>SHOW ERROR<br/>