ocilib_demo.c

oci的源码,可以在任何平台上编译,相当方便实用

    obj  = OCI_ObjectCreate(cn, OCI_TypeInfoGet(cn, MT("test_t"), OCI_TIF_TYPE));

    OCI_ObjectSetInt(obj, MT("VAL_INT"), 1);
    OCI_ObjectSetDouble(obj, MT("VAL_FLT"), 3.14);
    OCI_ObjectSetString(obj, MT("VAL_STR"), DT("USB KEY 2go"));
    OCI_ObjectSetRaw(obj, MT("VAL_RAW"), DT("AAAAA"), 5 * sizeof(dtext));

    date = OCI_DateCreate(cn);
    OCI_DateSysDate(date);
    OCI_ObjectSetDate(obj, MT("VAL_DATE"), date);

    obj2 = OCI_ObjectCreate(cn, OCI_TypeInfoGet(cn, MT("TYPE_T"), OCI_TIF_TYPE));
    OCI_ObjectSetInt(obj2, MT("ID"), 1);

    OCI_ObjectSetString(obj2, MT("NAME"), DT("USB KEY 2go"));
    OCI_ObjectSetObject(obj, MT("VAL_OBJ"), obj2);

    lob = OCI_LobCreate(cn, OCI_CLOB);
    OCI_ObjectSetLob(obj, MT("VAL_LOB"), lob);

    file = OCI_FileCreate(cn, OCI_BFILE);
    OCI_FileSetName(file, MT("mydir"), MT("myfile"));
    OCI_ObjectSetFile(obj, MT("VAL_FILE"), file);

    OCI_Prepare(st, MT("insert into test_object values(:obj)"));
    OCI_BindObject(st, MT(":obj"), obj);
    OCI_Execute(st);

    print_frmt("\n%d row(s) inserted\n", OCI_GetAffectedRows(st));

    OCI_Commit(cn);

    OCI_DateFree(date);
    OCI_LobFree(lob);
    OCI_FileFree(file);
    OCI_ObjectFree(obj2);
    OCI_ObjectFree(obj);

#endif
}

/* ------------------------------------------------------------------------ *
 * test_object_fetch
 * ------------------------------------------------------------------------ */

void test_object_fetch(void)
{
#ifdef OCI_CHARSET_ANSI

    OCI_Lob *lob;
    OCI_Date *date;
    OCI_File *file;
    OCI_Object *obj;
    OCI_Object *obj2;

    print_text("\n>>>>> TEST OBJECT FETCHING \n\n");

    OCI_ExecuteStmt(st, MT("select val from test_object for update"));

    rs = OCI_GetResultset(st);

    /* print resultset content */
    while (OCI_FetchNext(rs))
    {
        obj  = OCI_GetObject(rs, 1);

        print_frmt("val_int        : %i\n",  OCI_ObjectGetInt(obj, MT("VAL_INT")));
        print_frmt("val_flt        : %g\n",  OCI_ObjectGetDouble(obj, MT("VAL_FLT")));
        print_text("val_str        : "); print_dstr(OCI_ObjectGetString(obj, MT("VAL_STR")));
        print_text("\n");

        temp[OCI_ObjectGetRaw(obj, MT("VAL_RAW"), temp, 20)] = 0;
        print_text("val_raw        : "); print_dstr(temp);
        print_text("\n");

        date = OCI_ObjectGetDate(obj, MT("VAL_DATE"));
        OCI_DateToText(date, MT("YYYY-MM-DD HH24:MI:SS"), SIZE_STR, str);
        print_text("val_date       : "); print_mstr(str);
        print_text("\n");

        lob = OCI_ObjectGetLob(obj, MT("VAL_LOB"));

        OCI_LobWrite(lob, DT("Hello from Mars !"), 17);
        OCI_LobSeek(lob, 0, OCI_SEEK_SET);

        temp[OCI_LobRead(lob, temp, SIZE_STR)]=0;

        print_text("val_clob       : "); print_dstr(temp);
        print_text("\n");

        file = OCI_ObjectGetFile(obj, MT("VAL_FILE"));
        print_text("val_file.dir   : "); print_mstr(OCI_FileGetDirectory(file));
        print_text("\n");
        print_text("val_file.name  : "); print_mstr(OCI_FileGetName(file));
        print_text("\n");

        obj2 = OCI_ObjectGetObject(obj, MT("VAL_OBJ"));
        print_frmt("val_obj.code   : %i\n", OCI_ObjectGetInt(obj2, MT("ID")));
        print_text("val_obj.name   : "); print_dstr(OCI_ObjectGetString(obj2, MT("NAME")));
        print_text("\n");

        printf("\n");;

    }

    OCI_Commit(cn);

#endif

}

/* ------------------------------------------------------------------------ *
 * test_scrollable_cursor
 * ------------------------------------------------------------------------ */

void test_scrollable_cursor(void)
{
    if (OCI_GetVersionConnection(cn) >= OCI_9)
    {
        print_text("\n>>>>> TEST SCROLLABLE CURSORS \n\n");

        OCI_SetFetchMode(st, OCI_SFM_SCROLLABLE);

        OCI_ExecuteStmt(st, MT("select table_name from user_tables where ")
                            MT("table_name like 'TEST_%' order by table_name"));

        rs = OCI_GetResultset(st);

        if (OCI_FetchLast(rs))
            print_frmt("... Resultset contains %i rows\n\n", OCI_GetRowCount(rs));

        print_text("... Go to row 1\n");
        if (OCI_FetchFirst(rs))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        print_frmt("... Enumerate from row 2 to row %i\n", OCI_GetRowCount(rs));
        while (OCI_FetchNext(rs))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        print_frmt("... Enumerate from row %i back to row 1\n", OCI_GetRowCount(rs)-1);
        while (OCI_FetchPrev(rs))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        print_text("... Go to the 3th row\n");
        if (OCI_FetchSeek(rs, OCI_SFD_ABSOLUTE,  3))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        print_text("... Fetch the next 2 rows\n");
        while ((OCI_GetCurrentRow(rs) < 5) && OCI_FetchNext(rs))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        OCI_SetFetchMode(st, OCI_SFM_DEFAULT);
    }
}

/* ------------------------------------------------------------------------ *
 * test_collection
 * ------------------------------------------------------------------------ */

void test_collection(void)
{
    OCI_TypeInfo *type;
    OCI_Coll     *coll;
    OCI_Iter     *iter;
    OCI_Elem     *elem;
    int i, n;

    print_text("\n>>>>> TEST VARRAY BINDING \n\n");

    type = OCI_TypeInfoGet(cn, MT("T_TAB1_EMP"), OCI_TIF_TYPE);
    coll = OCI_CollCreate(type);

    OCI_Prepare(st, MT("begin")
                    MT("    select employees into :tab_emp ")
                    MT("    from test_coll_varray ")
                    MT("    where departement = :id; ")
                    MT("end;"));

    OCI_BindColl(st, MT(":tab_emp"), coll);
    OCI_BindInt(st, MT(":id"), &i);

    i = 1;

    OCI_Execute(st);

    iter = OCI_IterCreate(coll);
    elem = OCI_IterGetNext(iter);

    print_frmt("Department ID #%d\n\n", i);

    while (elem != NULL)
    {
        print_text("... Employee : ");
        print_dstr(OCI_ElemGetString(elem));
        print_text("\n");

        elem = OCI_IterGetNext(iter);
    }

    OCI_IterFree(iter);
    OCI_CollFree(coll);

    print_text("\n>>>>> TEST VARRAY FETCHING WITH ITERATOR \n\n");

    OCI_ExecuteStmt(st, MT("SELECT * from test_coll_varray"));

    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        coll = OCI_GetColl(rs, 2);

        iter = OCI_IterCreate(coll);
        elem = OCI_IterGetNext(iter);

        print_frmt("\nDepartment ID #%d\n\n", OCI_GetInt(rs, 1));

        while (elem != NULL)
        {
            print_text("... Employee : ");
            print_dstr(OCI_ElemGetString(elem));
            print_text("\n");

            elem = OCI_IterGetNext(iter);
        }

        OCI_IterFree(iter);
    }


    print_text("\n>>>>> TEST NESTED TABLE FETCHING WITH INDEX ACCESS \n\n");

    OCI_ExecuteStmt(st, MT("SELECT * from test_coll_nested"));

    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        coll = OCI_GetColl(rs, 2);

        print_frmt("\nDepartment ID #%d\n\n", OCI_GetInt(rs, 1));

        n = OCI_CollGetSize(coll);

        for(i = 1; i <= n; i++)
        {
            elem = OCI_CollGetAt(coll, i);

            print_text("... Employee : ");
            print_dstr(OCI_ElemGetString(elem));
            print_text("\n");

        }
    }
}

/* ------------------------------------------------------------------------ *
 * test_ref
 * ------------------------------------------------------------------------ */

void test_ref(void)
{
#ifdef OCI_CHARSET_ANSI

    OCI_Ref    *ref;
    OCI_Object *obj;

    print_text("\n>>>>> TEST REF FETCHING \n\n");

    OCI_ExecuteStmt(st, MT("select ref(e) from test_table_obj e"));
    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        ref = OCI_GetRef(rs, 1);

        obj = OCI_RefGetObject(ref);

        print_frmt("%i - ", OCI_ObjectGetInt(obj, MT("ID")));
        print_mstr(OCI_ObjectGetString(obj, MT("NAME")));
        print_text("\n");
    }

    print_text("\n>>>>> TEST REF PL/SQL BINDING \n\n");

    ref = OCI_RefCreate(cn, OCI_TypeInfoGet(cn, MT("type_t"), OCI_TIF_TYPE));

    OCI_Prepare(st, MT("begin ")
                    MT("  select ref(e) into :r from test_table_obj e where e.id = 1; ")
                    MT("end; "));

    OCI_BindRef(st, MT(":r"), ref);
    OCI_Execute(st);

    obj = OCI_RefGetObject(ref);
    print_frmt("%i - ", OCI_ObjectGetInt(obj, MT("ID")));
    print_mstr(OCI_ObjectGetString(obj, MT("NAME")));
    print_text("\n");

    OCI_RefFree(ref);

#endif
}

/* ------------------------------------------------------------------------ *
 * test_directpath
 * ------------------------------------------------------------------------ */

void test_directpath(void)
{
   /* Some OCI Direct path function fails (segfault) if the OCI version of the
      client is different than the server one.
      It happens with OCI 8i and 9i client. Apparently Oracle 10g and 11g seems to
      have fixed these problems.
      Anyway, we run this test case only if the major versions of client and server
      match
   */

   if (OCI_GetOCIRuntimeVersion() == OCI_GetServerMajorVersion(cn))
   {
        OCI_DirPath *dp;
        OCI_TypeInfo *tbl;

        dtext val1[SIZE_COL1+1];
        dtext val2[SIZE_COL2+1];
        dtext val3[SIZE_COL3+1];

        int i = 0, nb_rows = SIZE_ARRAY;

       /* commit any previous pending modifications */

        OCI_Commit(cn);

        print_text("\n>>>>> TEST DIRECT PATH \n\n");

        tbl = OCI_TypeInfoGet(cn, MT("test_directpath"), OCI_TIF_TABLE);
        dp  = OCI_DirPathCreate(tbl, NULL, NUM_COLS, nb_rows);

        /* optional attributes to set */

        OCI_DirPathSetBufferSize(dp, 64000);
        OCI_DirPathSetNoLog(dp, TRUE);
        OCI_DirPathSetParallel(dp, TRUE);

        if(OCI_GetOCIRuntimeVersion() >= OCI_9)
        {
            OCI_DirPathSetCacheSize(dp, 100);
        }

        /* describe the target table */

        OCI_DirPathSetColumn(dp, 1, MT("VAL_INT"),  SIZE_COL1, NULL);
        OCI_DirPathSetColumn(dp, 2, MT("VAL_STR"),  SIZE_COL2, NULL);
        OCI_DirPathSetColumn(dp, 3, MT("VAL_DATE"), SIZE_COL3, MT("YYYYMMDD"));

        /* prepare the load */

        OCI_DirPathPrepare(dp);

        nb_rows = OCI_DirPathGetMaxRows(dp);

        for (i = 1; i <= nb_rows; i++)
        {
            /* fill test values */

            sprint_dt(val1, SIZE_COL1+1, DT("%04d"), i);
            sprint_dt(val2, SIZE_COL2+1, DT("value %05d"), i);
            sprint_dt(val3, SIZE_COL3+1, DT("%04d%02d%02d"), (i%23)+1 + 2000,
                                                             (i%11)+1,
                                                             (i%23)+1);

            OCI_DirPathSetEntry(dp, i, 1, val1, (unsigned int) dtslen(val1), TRUE);
            OCI_DirPathSetEntry(dp, i, 2, val2, (unsigned int) dtslen(val2), TRUE);
            OCI_DirPathSetEntry(dp, i, 3, val3, (unsigned int) dtslen(val3), TRUE);
        }

        /* load data to the server */

        OCI_DirPathConvert(dp);
        OCI_DirPathLoad(dp);

        /* commits changes */

        OCI_DirPathFinish(dp);

        print_frmt("%03d row(s) processed\n", OCI_DirPathGetAffectedRows(dp));
        print_frmt("%03d row(s) loaded\n", OCI_DirPathGetRowCount(dp));

        /* free direct path object */

        OCI_DirPathFree(dp);
   }
}