CreateSQL Method

Applies To

Description

Executes the SQL statement and creates an OraSQLStmt object from the specified SQL statement and options.

Usage

Set orasqlstmt = oradatabase.CreateSQL(sql_statement, options)

Arguments

The arguments for the method are:

Arguments	Description
`sql_statement`	Any valid Oracle SQL statement.
`options`	A bit flag indicating the status of any optional states of the `OraSQLStmt` object. You can combine one or more options by adding their respective values.

Constants

The options flag values are:

Constant	Value	Description
`ORASQL_NO_AUTOBIND`	`&H1&`	Do not perform automatic binding of database parameters.
`ORASQL_FAILEXEC`	`&H2&`	Raise error and do not create SQL statement object.
`ORASQL_NONBLK`	`&H4&`	Execute SQL in a nonblocking state.

These values can be found in the oraconst.txt file.

Remarks

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.

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

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 CreateSQL method.

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.

If the ORASQL_FAILEXEC option is used, an error is raised during SQLstmt object creation failure (on SQLstmt object refresh). The SQLstmt object is not created and cannot be refreshed.

Note:

Use the CreateSQL method with care, because any SQL statement or PL/SQL block that is executed might cause errors afterward when you use the Edit method on open dynasets.

Data Type

String

Examples

This example demonstrates the use of parameters, the CreateSQL method, the Refresh method, and the SQL property for OraSQLStmt object. Copy and paste this code into the definition section of a form. Then, press F5.

Sub Form_Load ()
 
 'Declare variables 
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraSqlStmt As OraSQLStmt 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 OraDatabase.Parameters.Add "EMPNO", 7369, 1
 OraDatabase.Parameters("EMPNO").ServerType = 2  'ORATYPE_NUMBER 
 
 OraDatabase.Parameters.Add "ENAME", 0, 2
 OraDatabase.Parameters("ENAME").ServerType = 1  'ORATYPE_VARCHAR2  
 
 Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName" & _ 
          "(:EMPNO, :ENAME); end;", 0&) 
 
 'Notice that the SQL statement is NOT modified.
 MsgBox OraSqlStmt.SQL
 
 'Should display SMITH
 MsgBox OraDatabase.Parameters("ENAME").Value  
 
 'Change the value of the empno parameter.
 OraDatabase.Parameters("EMPNO").Value = 7499
 
 'Refresh the sqlstmt
 OraSqlStmt.Refresh
 
 'Should display ALLEN
 MsgBox OraDatabase.Parameters("ENAME").Value   
 
 'Notice that the SQL statement is NOT modified.
 MsgBox OraSqlStmt.SQL  
 
 'Remove the parameter.
 OraDatabase.Parameters.Remove ("job")
 
 End Sub