Bulk Insert of Blob data type in Oracle

Discussion in 'C++' started by Niraj, Mar 2, 2004.

  1. Niraj

    Niraj Guest

    Hi,
    I am trying to do bulk insert of binary data (array of bytes) in an
    Oracle table. The data type of the table is BLOB. I am using Oracle
    Objects for OLE (OO4O) in C++.

    The binary data that I have to insert is in the form of byte array.
    My problem is that the bulk inserts happen only as long as the length
    of array is less than 4K. Any data more than 4K gets truncated.

    Is it possible to do bulk inserts in a BLOB field with data more that
    4K per record ? Here is the code that works fine only as long as byte
    array is less that 4000 bytes:


    OStartup();

    ODatabase conn;
    conn.Open("tnsname", "user", "password");


    if (conn.IsOpen())
    {

    long recordCount = 10;

    OParameterCollection params = conn.GetParameters();
    OParamArray blobParam =
    params.AddTable("PARAMBLOB", OPARAMETER_INVAR,
    OTYPE_RAW_BIN, recordCount, 4000);

    //lets put 10 dumy records
    int bufSize = 4000;
    for (int i=0;i<recordCount;i++)
    {
    //simulate a byte array of size bufSize.
    //This is normally more than 4000 bytes in real life
    BYTE * byteArray = new BYTE[bufSize];
    for (int j=0;j<bufSize;j++)
    {
    byteArray[j] = 60;//an arbit value

    }

    //copy it over to a char buffer
    char *buff = (char *)malloc(bufSize);

    memcpy(buff, byteArray, bufSize);

    blobParam.SetValue(buff, bufSize, i);

    free(buff);

    free(byteArray);

    }

    oresult result =
    conn.ExecuteSQL("insert into BLOB_TABLE (BLOB_FIELD) values
    :)PARAMBLOB)");

    blobParam.Close();
    params.Close();

    conn.Close();
    }

    OShutdown();



    Any help will be greatly appreciated.
    Thanks
    Niraj, Mar 2, 2004
    #1
    1. Advertising

  2. * (Niraj) schriebt:

    Off-topic in [comp.lang.c++].

    Please don't cross-post to groups unrelated to the question.
    Alf P. Steinbach, Mar 3, 2004
    #2
    1. Advertising

  3. Niraj

    Jim Kennedy Guest

    "Niraj" <> wrote in message
    news:...
    > Hi,
    > I am trying to do bulk insert of binary data (array of bytes) in an
    > Oracle table. The data type of the table is BLOB. I am using Oracle
    > Objects for OLE (OO4O) in C++.
    >
    > The binary data that I have to insert is in the form of byte array.
    > My problem is that the bulk inserts happen only as long as the length
    > of array is less than 4K. Any data more than 4K gets truncated.
    >
    > Is it possible to do bulk inserts in a BLOB field with data more that
    > 4K per record ? Here is the code that works fine only as long as byte
    > array is less that 4000 bytes:
    >
    >
    > OStartup();
    >
    > ODatabase conn;
    > conn.Open("tnsname", "user", "password");
    >
    >
    > if (conn.IsOpen())
    > {
    >
    > long recordCount = 10;
    >
    > OParameterCollection params = conn.GetParameters();
    > OParamArray blobParam =
    > params.AddTable("PARAMBLOB", OPARAMETER_INVAR,
    > OTYPE_RAW_BIN, recordCount, 4000);
    >
    > //lets put 10 dumy records
    > int bufSize = 4000;
    > for (int i=0;i<recordCount;i++)
    > {
    > //simulate a byte array of size bufSize.
    > //This is normally more than 4000 bytes in real life
    > BYTE * byteArray = new BYTE[bufSize];
    > for (int j=0;j<bufSize;j++)
    > {
    > byteArray[j] = 60;//an arbit value
    >
    > }
    >
    > //copy it over to a char buffer
    > char *buff = (char *)malloc(bufSize);
    >
    > memcpy(buff, byteArray, bufSize);
    >
    > blobParam.SetValue(buff, bufSize, i);
    >
    > free(buff);
    >
    > free(byteArray);
    >
    > }
    >
    > oresult result =
    > conn.ExecuteSQL("insert into BLOB_TABLE (BLOB_FIELD) values
    > :)PARAMBLOB)");
    >
    > blobParam.Close();
    > params.Close();
    >
    > conn.Close();
    > }
    >
    > OShutdown();
    >
    >
    >
    > Any help will be greatly appreciated.
    > Thanks


    You have defined the record to be only 4,000 bytes long in the parameter. A
    BLOB can hold up to 4 gigs so you shouldn't have trouble fitting something
    larger in it.
    Change:
    > params.AddTable("PARAMBLOB", OPARAMETER_INVAR,
    > OTYPE_RAW_BIN, recordCount, 4000);

    to
    > params.AddTable("PARAMBLOB", OPARAMETER_INVAR,
    > OTYPE_RAW_BIN, recordCount,

    SOMETHING_LARGE_ENOUGH_TO_HOLD_LARGEST_VALUE);

    To test you could change the 4000 to 3000 and I bet it will be truncated to
    3,000 bytes not 4,000 bytes.
    Jim
    Jim Kennedy, Mar 3, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sean
    Replies:
    6
    Views:
    395
  2. =?Utf-8?B?U2hpanUgUG95aWxpbA==?=

    BULK INSERT WITH ADO.NET & ORACLE

    =?Utf-8?B?U2hpanUgUG95aWxpbA==?=, Apr 25, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    6,272
    Sathya.Pai
    Sep 9, 2009
  3. Replies:
    2
    Views:
    562
    =?iso-8859-1?q?Stephan_Br=F6nnimann?=
    Jan 19, 2006
  4. AB@
    Replies:
    2
    Views:
    1,354
  5. Feyruz
    Replies:
    4
    Views:
    2,156
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page