ExecuteSQL Method

See Also

Example

Applies To

OraDatabase

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

sql_statement

Any valid Oracle non-SELECT SQL statement.

Remarks

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

The sql_statement 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 BeginTrans on the session object before using ExecuteSQL.

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" 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: ExecuteSQL should be used with care since any SQL statement or PL/SQL block that is executed can adversely affect currently open dynasets. This is especially 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 either a SQL commit command or 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. Consult the Oracle9i SQL Reference for more details about DCL, DDL and transactions.

Data Type

Long Integer