OCI_ERROR Error: ORA-01465

[cfc]

Michael,

I am using Oracle and download the latest version of SVN and now I have problems with queries where the answer is a field of type long and has data, if the field is empty does not generate error.

The error was detected when looking for the metadata and the data field has DATA_DEFAULT.

LOG:

6/28/2012 8:10:57 cat: Execute, proto: oracle-9i, msg: select ID_OTMASTER, OTFOLIO, OTCREATEDDATE
from OT_MASTER
order by desc OTFOLIO

6/28/2012 8:10:58 cat: Execute, proto: oracle-9i, msg: SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE, DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL, DATA_DEFAULT, NULL , NULL, NULL, column_id, NULLABLE FROM WHERE SYS.ALL_TAB_COLUMNS OWNER LIKE '%' AND TABLE_NAME LIKE 'OT_MASTER' AND COLUMN_NAME LIKE '%'
6/28/2012 8:10:58 cat: Other, proto: oracle-9i, msg: FETCH ROW, errcode: 1465, error: OCI_ERROR: ORA-01465: invalid hex number

Then check it with a query on a table that has this type of data in a field.

  CREATE TABLE "MP9_FRIAR_RE". "EQU_LOCATION"
   ("ID_LOCATION" NUMBER (9.0) NOT NULL ENABLE,
    "ID_IMAGEBUILDING" NUMBER (9.0) DEFAULT (0),
    "PR_PARENT" NUMBER (9.0),
    "PR_NAME" VARCHAR2 (50),
    "PR_BDATE" DATE,
    "PR_EDATE" DATE,
    "Pr_info" LONG,
    "LOCATION" VARCHAR2 (255)

LOG:

6/28/2012 9:27:39 cat: Execute, proto: oracle-9i, msg: SELECT a.id_location, a.id_imagebuilding, a.pr_parent, a.pr_name,
       a.pr_bdate, a.pr_edate, a.pr_info, a.location
  FROM mp9_friar_re.equ_location to

6/28/2012 9:27:40 cat: Execute, proto: oracle-9i, msg: SELECT a.id_location, a.id_imagebuilding, a.pr_parent, a.pr_name,
       a.pr_bdate, a.pr_edate, a.pr_info, a.location
  FROM mp9_friar_re.equ_location to

6/28/2012 9:27:40 cat: Other, proto: oracle-9i, msg: FETCH ROW, errcode: 1465, error: OCI_ERROR: ORA-01465: invalid hex number

6/28/2012 9:27:40 cat: Other, proto: oracle-9i, msg: FETCH ROW, errcode: 1465, error: OCI_ERROR: ORA-01465: invalid hex number


Which detects that the problem is generated in line 300 of ZdbcOracleResultSet:

stAsciiStream: CurrentVar.ColType: = stUnicodeStream;

Changing ColType of stAsciiStream to stUnicodeStream when CurrentVar.DataType = SQLT_LNG.

Bypass this works, but it sure affects other data types.

Perhaps only if force should CurrentVar.DataType <> SQLT_LNG.

Greetings.
cfc.

[EgonHugeist]

cfc,

I'm sorry writing from my smartphone. I'm not at home today.

If this is a text-lob and you did open the connection with an unicode characterset then this exceptions and your fix is right and my check for the cient-encoding is missing in this case. I'll check this tonight or tomorrow.

Question: which clientcodeppage did you use here? And which NLS_LAN is is set for you database?

I'm realy no Oracle hero. So what is "long" for a type? Is it a Text-Lob?

Michael

[cfc]

Michael,

The LONG and LONG RAW datatypes have been deprecated in favour of LOBs for many Oracle versions. But, for example, when Zeos retrieve metadata from database use this filed type.

NLS_LANG=AMERICAN_AMERICA.UTF8
clientcodepage is blank in ZConection

i'm testing with clientcodepage=UTF8 and fail
with clientcodepage=US7ASCII no error.

cfc.

[EgonHugeist]

cfc,

Can you tell me if the LONG RAW is equal out of that rule?

Ok i did a fix for that. Also i did fix that type on the ZDbcOraclUtils where the FieldTypes where assigned from the MetaInformations. Good job!

Patch done Rev. 1422. Confirmed?

Michael