Setting precision of OCI binding

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 6 Aug 2003 12:08:11 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703FEFD3F@lnewton.leeds.lfs.co.uk>

Hi Ben,

check out attributes OCI_ATTR_FSPRECISION and OCI_ATTR_LFPRECISION - should be what you need.

Cheers,
Norm.

PS. Would this have been better posted in C.D.O.Server rather than misc ?

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----
From: Ben Hutchings [mailto:do-not-spam-benh_at_bwsint.com] Posted At: Tuesday, August 05, 2003 7:29 PM Posted To: misc
Conversation: Setting precision of OCI binding Subject: Setting precision of OCI binding

I am using Oracle 9.2.0.1 (same version on both server and client) and programming with OCI.

I wish to insert a value into a column with the type INTERVAL DAY(7) TO SECOND(6). (This should be able to hold the interval between any two valid dates.) I use a statement with placeholders and bind the interval value with OCI calls along the following lines:

    dvoid * desc;
    OCIInterval * interval;
    OCIInd * ind;
    OCIBind * bind;

    Check(

        OCIDescriptorAlloc(
            env, &desc, OCI_DTYPE_INTERVAL_DS, 0, NULL),
        err);

    interval = (OCIInterval *)desc;
    Check(

        OCIIntervalSetDaySecond(
            env, err,
            days, hours, minutes, seconds, nanoseconds,
            interval),
        err);

    ind = 0;

    bind = 0;

    Check(

        OCIBindByPos(
            statement, &bind, err, 1,
            &interval, sizeof(interval),
            SQLT_INTERVAL_DS, &ind,
            NULL, NULL, 0, NULL,
            OCI_DEFAULT),
        err);

These execute without error, but when I call OCIStmtExecute I get this error message:

    ORA-01873: the leading precision of the interval is too small

(Note, the values given to OCIIntervalSetDaySecond were days=0, hours=1, minutes=0, seconds=0, nanoseconds=0.)

Presumably I need to specify the leading precision for the interval type after calling OCIBindByPos. However, I do not see how to do this. There doesn't seem to be any way to set precision or scale, except for packed decimal types. What am I missing? Received on Wed Aug 06 2003 - 06:08:11 CDT