Re: Oracle 10g and Bfiles

Geoff wrote:
> Hello All,
>
> I have a very strange situation. I am using 'Oracle 10g Enterprise Edition
> Release 10.2.0.1.0 - Production' on winxp. I am trying to access tables
> through the OCI. I have the following table setup:
>
> SQL> describe test4;
>
> Name Null? Type
> ----------------------------------------- -------- -------------
> ID NUMBER(38)
> NAME VARCHAR2(30)
>
> . . . using the OCI, I am able to do a select statement, 'select id, name
> from test4', define (OCIBindByPos) variables for 'id' and 'name', execute
> the sql statement and look at the values in the table, no problem.
>
> When I try to do the same thing for the following table:
>
> SQL> describe test3;
>
> Name Null? Type
> ----------------------------------------- -------- ----------------
> ID NUMBER(38)
> THEBFILE BINARY FILE LOB
>
> . . . the bfile column is a problem. If I define (OCIBindByPos) a variable
> for 'id' and execute the sql statement, 'select id, thebfile from test3', I
> can get the value of 'id' no problem.
>
> . . . If I do the exact same thing but change the sql statement to 'select
> thebfile, id from test3' then I get nothing. Of course, I am changing the
> pos to 2 for this statement.
>
> . . . concerning bfiles, I am not able to get anything at all. I am
> following their C example by first allocating an OCI_DTYPE_LOB descriptor,
> doing an OCIDefineByPos so it is linked to the 'thebfile', execute the sql,
> but nothing. I am passing in the address of the descriptor to define as
> stated in the manual.
>
> Is there a problem working with lobs and bfiles and does anyone have any
> clue as to why lobs and bfiles can not be returned to the defined variable?
>
> Of course, I am not using C but using the latest version of IBM Smalltalk.
> Thanks.
>
> -g
>
>

 >. . . concerning bfiles, I am not able to get anything at all.  I am
 >following their C example by first allocating an OCI_DTYPE_LOB 
 >descriptor,

The BFILE descriptor type is OCI_DTYPE_FILE not OCI_DTYPE_LOB. Replace this in the OCIDescriptorAlloc call and retry.

To retrieve the BFILE name , you'll have to use the OCI Bfile API : OCILobFileOpen( ... ), and to check if the BFILE relly exists, use: OCILobFileExists( ... ).

The position of columns in the SELECT list though shouldn't matter, until you are defining the right type for the right position.

Rgds.
Amogh Received on Sun Apr 23 2006 - 02:39:51 CDT