Re: PRO*C/C++ EMBEDDED PL/SQL BLOB QUESTION

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 08 Oct 1998 16:18:34 GMT
Message-ID: <361ce5be.7156710@192.86.155.100>

A copy of this was sent to drexler_jerry_at_htc.honeywell.com (Jerry Drexler) (if that email address didn't require changing) On Wed, 07 Oct 1998 22:25:43 GMT, you wrote:

>I am trying to use the function "DBMS_LOB.WRITE" in a pro c/c++ program to
>store a BLOB and received an error while precompiling. Someone at Oracle
>suggested that OCI is the only way to do this using OciLobWrite().
>Is this true?

no its not true at all.

What error are you recieving? when you incorporate pl/sql into pro*c like that with the EXEC SQL EXECUTE syntax, you must use:

sqlcheck=semantics userid=user/pass

on the proc command line, else it will error out.

Here is an example piece of code that I know works just fine in pro*c:

static void process()
{
typedef struct TAGmy_varraw
{

    long len;
    unsigned char arr[32000];
}

    my_varraw;     

    int i;
EXEC SQL BEGIN DECLARE SECTION;
    EXEC SQL TYPE my_varraw IS LONG VARRAW(32000);     int x;
    my_varraw y;
EXEC SQL END DECLARE SECTION; /*
SQL> desc demo

 Name                            Null?    Type
  ------------------------------- -------- ----
  X                                        NUMBER(38)
  Y                                        BLOB

*/

    memset( y.arr, 'a', 32000 );
    y.len = 32000;

    x = 1;

    EXEC SQL EXECUTE
    declare

        l_lob   blob;
        l_amt   number default 32000;
    begin
        insert into demo ( x, y )
        values ( :x, empty_blob() )
        return y into l_lob;

        dbms_lob.write( l_lob, l_amt, 1, :y );
        commit;

    end;
    END-EXEC;
}

>
>Here is an example of my code :
>
>
>
> EXEC SQL EXECUTE
> DECLARE
> locator_var BLOB;
> amount_var INTEGER;
> offset_var INTEGER;
> BEGIN
> amount_var :=1024;
> offset_var := 1;
> INSERT INTO BUNK VALUES(
> :l_ObjectID,
> :l_BunkName,
> :l_BunkShort,
> :l_BunkDouble,
> EMPTY_BLOB());
> SELECT C_BUNKARRAY INTO locator_var FROM BUNK
> WHERE C_OBJECT_ID = :l_ObjectID FOR UPDATE;
> DBMS_LOB.WRITE(locator_var,amount_var,offset_var,:mybuffer);
> COMMIT;
> END;
> END-EXEC;
>
>using the table
>
>SQLWKS> describe bunk
>Column Name Null? Type
>------------------------------ -------- ----
>C_OBJECT_ID NUMBER
>C_BUNKNAME VARCHAR2(30)
>C_BUNKSHORT NUMBER
>C_BUNKDOUBLE NUMBER
>C_BUNKARRAY BLOB(4000)
>
>
>Thanks!
>
>Jerry Drexler
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Oct 08 1998 - 11:18:34 CDT