ServerType Property

Applies To

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