ExecuteSQL Method

Applies To

OraDatabase Object

Description

Executes a single non-SELECT SQL statement or a PL/SQL block.

Usage

rowcount = oradatabase.ExecuteSQL(sql_statement)
rowcount = oradatabase.DbExecuteSQL(sql_statement)  

Arguments

The arguments for the method are:

Remarks

Executes a SQL statement and returns the number of rows processed by that statement.

The sql_statement argument can be one continuous line with no breaks. If it is necessary to break the line, be sure to use line feeds (ASCII 10). Do not use carriage returns (ASCII 13), because the underlying Oracle Database functions treat carriage returns as null terminators.

Executing the SQL statement generates a commit to the database by default. To avoid this, use the BeginTrans method on the session object before using the ExecuteSQL method.

You can use PL/SQL bind variables in conjunction with the OraParameters collection.

When executing PL/SQL blocks or calling stored procedures, you must include a BEGIN and END statement around your call as if you were executing an anonymous PL/SQL block. This is equivalent to the EXECUTE command of SQL*Plus and SQL*DBA.

Normal dynaset operations can be adversely affected, if in transactional mode, a database commit is issued. This can happen if a SQL commit statement, a Data Control Language (DCL), or Data Definition Language (DDL) command is issued. DCL and DDL SQL commands, such as CREATE, DROP, ALTER, GRANT, and REVOKE always force a commit, which in turn commits everything done before them. See the Oracle Database SQL Language Reference for more details about DCL, DDL, and transactions.

Data Type

Long Integer

Examples

Example: ExecuteSQL

This example uses the Add and Remove parameter methods, the ServerType parameter property, and the ExecuteSQL database method to call the stored procedure GetEmpName and the stored function GetSal. Before running the example, run the ORAEXAMP.SQL file to create GetEmpName and GetSal as well as other necessary object types and LOBs in Oracle Database. Then, copy and paste this OO4O code example into the definition section of a form and run the program.

Sub Form_Load ()
 
'Declare variables 
 Dim OraSession As OraSession
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDatabase 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Create the OraDatabase Object.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 'Add EMPNO as an Input/Output parameter and set its initial value.
 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT
 OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER
 
 'Add ENAME as an Output parameter and set its initial value.
 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT
 OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2
 
 'Add SAL as an Output parameter and set its initial value.
 OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT
 OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER
 
 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.
 ' This Stored Procedure can be found in the file ORAEXAMP.SQL.
 OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
 'Display the employee number and name.
 
 'Execute the Stored Function Employee.GetSal to retrieve SAL.
 ' This Stored Function can be found in the file ORAEXAMP.SQL.
 OraDatabase.ExecuteSQL ("declare SAL number(7,2); Begin" & _  
                   ":SAL:=Employee.GetEmpSal (:EMPNO); end;")
 
 'Display the employee name, number and salary.
 MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & _
           OraDatabase.Parameters("EMPNO").value & ",Salary=" & _
           OraDatabase.Parameters("SAL").value
 
 'Remove the Parameters.
 OraDatabase.Parameters.Remove "EMPNO"
 OraDatabase.Parameters.Remove "ENAME"
 
 OraDatabase.Parameters.Remove "SAL"
End Sub