Specifies the Oracle external type of a SQL or PL/SQL bind variable. Not available at design time and read/write at run time.
Read-only for the OraParamArray object. Specify the ServerType property during the AddTable method.
Used to specify the external data type of SQL or PL/SQL (in/out) bind variables. This is necessary because no local parsing of the SQL statement or PL/SQL block is done to match the data types of placeholders in the SQL statement or PL/SQL block.
After an OraParameter object has been set to ServerType BLOB, CLOB, BFILE, OBJECT, REF, VARRAY, or NESTED TABLE, it cannot be changed to any other ServerType property.
The following Oracle external data types are supported.
| Constant | Value | Internal Data Type |
|---|---|---|
ORATYPE_VARCHAR2 |
1 |
VARCHAR2 |
ORATYPE_NUMBER |
2 |
NUMBER |
ORATYPE_SINT |
3 |
SIGNED INTEGER |
ORATYPE_FLOAT |
4 |
FLOAT |
ORATYPE_STRING |
5 |
Null Terminated STRING |
ORATYPE_LONG |
8 |
LONG |
ORATYPE_VARCHAR |
9 |
VARCHAR |
ORATYPE_DATE |
12 |
DATE |
ORATYPE_RAW |
23 |
RAW |
ORATYPE_LONGRAW |
24 |
LONG RAW |
ORATYPE_UINT |
68 |
UNSIGNED INTEGER |
ORATYPE_CHAR |
96 |
CHAR |
ORATYPE_CHARZ |
97 |
Null Terminated CHAR |
ORATYPE_BFLOAT |
100 |
BINARY_FLOAT |
ORATYPE_BDOUBLE |
101 |
BINARY_DOUBLE |
ORATYPE_CURSOR |
102 |
PLSQL CURSOR |
ORATYPE_MLSLABEL |
105 |
MLSLABEL |
ORATYPE_OBJECT |
108 |
OBJECT |
ORATYPE_REF |
110 |
REF |
ORATYPE_CLOB |
112 |
CLOB |
ORATYPE_BLOB |
113 |
BLOB |
ORATYPE_BFILE |
114 |
BFILE |
ORATYPE_TIMESTAMP |
187 |
TIMESTAMP |
ORATYPE_TIMESTAMPTZ |
188 |
TIMESTAMP WITH TIMEZONE |
ORATYPE_INTERVALYM |
189 |
INTERVAL YEAR TO MONTH |
ORATYPE_INTERVALDS |
190 |
INTERVAL DAY TO SECOND |
ORATYPE_TIMESTAMPLTZ |
232 |
TIMESTAMP WITH LOCAL TIME ZONE |
ORATYPE_VARRAY |
247 |
VARRAY |
ORATYPE_TABLE |
248 |
NESTED TABLE |
ORATYPE_RAW_BIN |
2000 |
RAW |
These values can be found in the ORACLE_BASE\\ORACLE_HOME\oo4o\oraconst.txt file.
This example demonstrates the Add and Remove parameter methods, the ServerType parameter property, and the ExecuteSQL database method to call a stored procedure and function (located in ORAEXAMP.SQL). Copy and paste this code into the definition section of a form. Then, press F5.
Sub Form_Load ()
'Declare variables as OLE Objects.
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
'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