Inserting Rows

The following example adds a new row to the employee table.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0)

EmpDb.ExecuteSQL ("INSERT INTO emp (empno, ename, job, mgr, deptno)

VALUES (1233, 'OERTEL', 'WRITER', 7839, 30) ")

Inserting multiple rows using parameter arrays

You may use parameter arrays to fetch, update, insert, or delete multiple rows in a table. Using parameter arrays for manipulating multiple rows, is more efficient than executing multiple statements that operate on individual rows.

The following example demonstrates how the AddTable method of the OraDatabase interface is used to create parameter arrays. The arrays are then populated with values, and used as place holders in the execution of an insert statement that inserts two rows into the emp table.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

Set EmpDb = OO4OSession.OpenDatabase("exampledb", "scott/tiger", 0)

'Creates parameter arrays for the empno, ename, job,

'and salary columns

EmpDb.Parameters.AddTable "EMPNO_ARRAY", ORAPARM_INPUT,

ORATYPE_NUMBER, 2

EmpDb.Parameters.AddTable "ENAME_ARRAY", ORAPARM_INPUT,

ORATYPE_VARCHAR2, 2, 10

EmpDb.Parameters.AddTable "JOB_ARRAY", ORAPARM_INPUT,

ORATYPE_VARCHAR2, 2, 9

EmpDb.Parameters.AddTable "MGR_ARRAY", ORAPARM_INPUT,

ORATYPE_NUMBER, 2

EmpDb.Parameters.AddTable "DEPT_ARRAY", ORAPARM_INPUT,

ORATYPE_VARCHAR2, 2, 10

Set EmpnoArray = EmpDb.Parameters("EMPNO_ARRAY")

Set EnameArray = EmpDb.Parameters("ENAME_ARRAY")

Set JobArray = EmpDb.Parameters("JOB_ARRAY")

Set MgrArray = EmpDb.Parameters("MGR_ARRAY")

Set DeptArray = EmpDb.Parameters("DEPT_ARRAY")

'Populate the arrays with values

EmpnoArray(0) = 1234

EnameArray(0) = "JORDAN"

JobArray(0) = "SALESMAN"

MgrArray(0) = 7839

DeptArray(0) = 30

EmpnoArray(1) = 1235

EnameArray(1) = "YOUNG"

JobArray(1) = "SALESMAN"

MgrArray(1) = 7839

DeptArray(1) = 30

'Insert two rows

EmpDb.ExecuteSQL ("INSERT INTO emp (empno, ename, job, mgr, deptno)

VALUES (:EMPNO_ARRAY,:ENAME_ARRAY,

:JOB_ARRAY,:MGR_ARRAY, :DEPT_ARRAY)")