set BFILE column to NULL with OCILobFileSetName( )

From: Kevin W. <kcw3388_at_hotmail.com>
Date: 16 Oct 2002 17:03:10 -0700
Message-ID: <69087156.0210161603.4baf6669_at_posting.google.com>

Hi,

I have difficulty to use OCILobFileSetName( ).

My test table is very simple.
create table de_lob_s1

    (b_file BFILE default NULL,
    c integer );

Here is my sql statement "insert into de_lob_s1 values (:1, :2)" I use the following calls to insert my records.

1) OCIStmtPrepare ( ), prepare the sql statement
2) OCIDescriptorAlloc ( ), alloc the b_file LOB locator
3) OCILobFileSetName ( ), set the alias, and filename
4) OCIBindByPos   ( ), bind the LOB locator and c.
5) OCIStmtExecute ( )

Everything seems fine as long as I got something for the alias and filename. But I don't know how to insert NULL to my "b_file" column.

I have tried the following methods:
1) I tried OCIAttrSet ( ) to empty out the LOB locator,

   OCI_ATTR_LOBEMPTY. It works fine but the LOB locator    is invalid when I try to read the record back. 2) set alias, filename to zero byte

   OCILobFileSetName ( envhp, errhp,lobl

"", 0, "", 0);

   But I got invalid OCI parameter error message. 3) set alias, filename to NULL

   OCILobFileSetName ( envhp, errhp,lobl

                       NULL, 0, NULL, 0);

   I got invalid OCI parameter error message. 4) set alias, filename to zero byte, alias & filename size to 1

   OCILobFileSetName ( envhp, errhp,lobl

"", 1, "", 1);

   But the b_file is not NULL when I read it back.

I also set the NULL indicator to -1 when I do the binding but it still does not help.

I tried all the possibility that I can think of but none of them work.

But if I do this with sqlplus.
sqlplus> insert into de_lob_s1 values (NULL, 1); sqlplus> insert into de_lob_s1 values (BFILENAME('',''), 2);

Both b_file are good, NULL, when I read it back.

I can work around it with two separate sql statements. One for NULL and one for not NULL but if I got two, three or more BFILE columns, then it is going to be a problem.

Also, I was thinking to insert a NULL to my BFILE column first and update it later but this idea won't fly if the "b_file BFILE not NULL".

Does anyone got some ideas how to do it?

Any suggestions/comments are welcome.

Kevin W Received on Thu Oct 17 2002 - 02:03:10 CEST