rowcount = oradatabase.ExecuteSQL(sql_statement) rowcount = oradatabase.DbExecuteSQL(sql_statement)
The arguments for the method are:
| Arguments | Description |
|---|---|
sql_statement |
Any valid Oracle non-SELECT SQL statement. |
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.
Note:
TheExecuteSQL method should be used with care because any SQL statement or PL/SQL block that is executed can adversely affect open dynasets. This is true if the OraDatabase object used for the ExecuteSQL method is the same as the one that was used to create the dynaset. Use a different OraDatabase object if you are unsure.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.
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