Symptom

ORA-24334: no descriptor for this position.

Only happens when using bind variables.

Fails with the O90, O10 and ORA drivers.

Environment

PowerBuilder

Reproducing the Issue

Passing datetime value that includes milliseconds as a parameter to a field that is expecting an Oracle Date datatype will reproduce the problem.

Cause

The dbtrace.log file showed the following:

(1e96048): DBPARM=StaticBind =0, DelimitIdentifier = 'NO', DateTime=' "to_date (" ''''dd-mmm-yyyy

hh:mm:ss'''' ", ''dd-mon-yyyy hh24:mi:ss'')" ' , disablebind=0(DBI_CONNECT) (0.000 MS / 0.012 MS)

...(1e93c30): DATETIME Length=0 ID=:28

(1e93c30): DATETIME Length=0 ID=:29

...(1e93c30): DATETIME Length=0 ID=:54 (NULL)

The problem has to do with milliseconds and the Oracle Date vs. Timestamp datatypes. The milliseconds portion of the data doesn't work with the Date datatype and causes the ORA-24334 message.

Resolution

Use the timestamp database parameter which specifies whether PowerBuilder should map DateTime and Time datatypes to the Oracle TimeStamp or Date datatype.

For this issue the value being passed needed to be mapped to the Oracle Date datatype and set as follows:

timestamp=0

The following is from the PowerBuilder help:

Specifies whether PowerBuilder should map DateTime and Time datatypes to the Oracle TimeStamp datatype.

Values are:

0 Map DateTime and Time datatypes to the Oracle Date datatype.

1 (Default) Map DateTime and Time datatypes to the Oracle TimeStamp datatype

Oracle9i and later databases and the O90 and O10 interfaces support the Oracle timestamp datatype. This datatype includes the date and the time including milliseconds. The existing Oracle Date datatype does not include millisecond information. In a DataWindow object, both the Oracle Timestamp and Date datatypes are mapped to the PowerBuilder DateTime datatype, which supports millisecond information.

If you use the O90 or O10 interface with an Oracle9i or higher server, DateTime and Time datatypes are mapped to the Oracle TimeStamp datatype by default. If you want DateTime and Time to be mapped to the Oracle Date datatype, set the TimeStamp database parameter to 0.

2
1