ServerType Property

Description

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.

Usage

oraparameter.ServerType = oracle_type

Data Type

Integer

Remarks

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.

Examples

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