How can I retrieve Oracle Field Type

[dados]

Hi,

If I have a statement in toraquery like this:
select 1 as num, 'a' as str from dual

Can I somehow get the oracle field type for those two fields?

[AlexP]

Hello,

For such a query, you can obtain External Data Types ( http://docs.oracle.com/cd/E11882_01/app ... tm#i423688 ) as OCI-Defined Constant, that are declared in the OraCall module. For this, you can use the following code:

Code: Select all

TOCICommand(TDBAccessUtils.GetICommand(OraQuery1)).GetOraType(TDBAccessUtils.GetIRecordSet(OraQuery1).FieldByName('STR').DataType,TDBAccessUtils.GetIRecordSet(OraQuery1).FieldByName('str').SubDataType)

P.S. Please describe in more details what type you need to obtain and for what: there may be a more correct approach.

[dados]

Thanks AlexP

I'm getting the same external type id (22 SQLT_BDOUBLE) for both internal type BINARY_FLOAT and BINARY_DOUBLE

from doc:
Native Float and Native Double

The native float (SQLT_BFLOAT) and native double (SQLT_BDOUBLE) data types represent
the single-precision and double-precision floating-point values. They are represented
natively, that is, in the host system's floating-point format.

These external types were added in release 10.1 to externally represent the
BINARY_FLOAT and BINARY_DOUBLE internal data types. Thus, performance for the
internal types is best when used in conjunction with external types native float
and native double respectively. This draws a clear distinction between the existing
representation of floating-point values (SQLT_FLT) and these types.

For best performance, use external types SQLT_BFLOAT and SQLT_BDOUBLE in conjunction with the BINARY_FLOAT and BINARY_DOUBLE data types.

[dados]

Another strange behaviour

Table created like this

create table test_types (
f_TEXT VARCHAR2(20),
f_TEXT4000 VARCHAR2(4000),
f_NVARCHAR2 NVARCHAR2(2000),
f_ROWID ROWID,
f_UROWID UROWID
);

Tested on Oracle 11.2 and 12.1 using both OCI and direct connection always returns the same result:
F_TEXT -> (DataType=1, SubDataType=1) returns OraType=5 SQLT_STR)
F_TEXT4000 -> (DataType=20, SubDataType=1) returns OraType=5 SQLT_STR)
F_NVARCHAR2 -> (DataType=20, SubDataType=120) returns OraType=5 SQLT_STR)
F_ROWID -> (DataType=1, SubDataType=100) returns OraType=5 SQLT_STR)
F_UROWID -> (DataType=20, SubDataType=114) returns OraType=5 SQLT_STR)

Shouldn't you be able to get the correct external types?

[AlexP]

Hello,

These methods are designed for internal usage, therefore data types can differ from the real Oracle types. Oracle returns the SQLT_STR type for VARCHAR2 and NVARCHAR2, the NVARCHAR attribute is defined by retrieving the OCI_ATTR_CHARSET_FORM attribute. On real tables, to define the column type, you should use the ALL_COLUMS view.