Using ExecuteSQL and CreateSQL to Execute PL/SQL Blocks

In OO4O, you can use the ExecuteSQL or CreateSQL methods of the OraDatabase to execute PL/SQL blocks. The following example shows how a PL/SQL block is executed.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

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

'Add EMPNO as an Input parameter and set its initial value.

EmpDb.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT

EmpDb.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

'Add ENAME as an Output parameter and set its initial value.

EmpDb.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT

EmpDb.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

'Add SAL as an Output parameter

EmpDb.Parameters.Add "SAL", 0, ORAPARM_OUTPUT

EmpDb.Parameters("SAL").ServerType = ORATYPE_NUMBER

'Add COMMISSION as an Output parameter and set its initial value.

EmpDb.Parameters.Add "COMMISSION", 0, ORAPARM_OUTPUT

EmpDb.Parameters("COMMISSION").ServerType = ORATYPE_NUMBER

EmpDb.ExecuteSQL ("BEGIN SELECT ename, sal, comm INTO :ENAME, :SAL,

:COMMISSION FROM emp WHERE empno = :EMPNO; END;")

'display the values of Ename, Sal, Commission parameters

MsgBox "Name: " & EmpDb.Parameters("ENAME").Value

MsgBox "Salary " & EmpDb.Parameters("SAL").Value

MsgBox "Commission: " & EmpDb.Parameters("COMMISSION").Value

The following example executes a PL/SQL block that calls a stored procedure using the CreateSQL method in OO4O. The procedure takes a department number as input and returns the name and location of the department.

The following script is used for creating the stored procedure in the employee database.

CREATE OR REPLACE PACKAGE Department as

PROCEDURE GetDeptName (inDeptNo IN NUMBER, outDeptName OUT VARCHAR2,

outDeptLoc OUT VARCHAR2);

END Department;

/

CREATE OR REPLACE PACKAGE BODY Department as

PROCEDURE GetDeptName(inDeptNo IN NUMBER, outDeptName OUT VARCHAR2,

outDeptLoc OUT VARCHAR2) is

BEGIN

SELECT dname, loc into outDeptName, outDeptLoc from DEPT

WHERE deptno = inDeptNo;

END;

END Department;

/

The following example executes the procedure created just described to get the name and location of the department where deptno is 10.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

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

empDb.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT

empDb.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER

empDb.Parameters.Add "DNAME", 0, ORAPARM_OUTPUT

empDb.Parameters("DNAME").ServerType = ORATYPE_VARCHAR2

empDb.Parameters.Add "DLOC", 0, ORAPARM_OUTPUT

empDb.Parameters("DLOC").ServerType = ORATYPE_VARCHAR2

Set PlSqlStmt = empDb.CreateSQL("Begin Department.GetDeptname

(:DEPTNO, :DNAME, :DLOC); end;", 0&)

'Display Department name and location

MsgBox empDb.Parameters("DNAME").Value & " " &

empDb.Parameters("DLOC").Value