Re: Newbie question regarding LOB

From: Dave Hau <davehau-no-spam-123_at_no-spam.netscape.net>
Date: Wed, 12 Nov 2003 07:22:53 GMT
Message-ID: <hdlsb.234$if3.194@newssvr24.news.prodigy.com>

If you just want to pass the LOB data to the stored procedure, you don't need to have any LOB code on the OCI side at all. Just pass a VARCHAR2 buffer to the stored procedure, and let the stored proc take care of selecting the lob locator from the table, and writing the buffer into the LOB, like this:

CREATE OR REPLACE PROCEDURE insert_to_database (my_id IN INTEGER, buffer IN VARCHAR2(32767)) IS

    lob_var BLOB;
BEGIN
    SELECT lob_column INTO lob_var FROM mytable WHERE id = my_id FOR UPDATE;

    DBMS_LOB.OPEN (lob_var, DBMS_LOB.LOB_READWRITE)
    DBMS_LOB.WRITE (lob_var, 32767, 1, buffer);
    DBMS_LOB.CLOSE (lob_var);

END; If you want to write code for handling LOBs, whether in PL/SQL, OCI etc., you should read the Oracle9i Application Developer's Guide - Large Objects (LOBs) in detail:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96591/toc.htm

HTH,
Dave

"Shankar" <shanky191_at_rediffmail.com> wrote in message news:24707dc9.0311111954.2c73c66a_at_posting.google.com...
> Thanks a lot for your help.
>
> Dave, a further question. In my program, i plainly invoke a stored
> procedure on the database which does the job of inserting the LOB,
> thereby shielding my application from SQL completely.
>
> BEGIN insert_to_database(:lob_var) END;
>
> Do i still need to execute a select statement to fetch the Lob
> Locator? In other words, if i just do a OCIDescriptorAlloc followed by
> OCIBindByName and OCIStmtExecute(which actually invokes the stored
> procedure), will it suffice? If so, does it pose any additional
> requirements on the stored procedure??
>
> Thanks and Regards,
> Shankar
>
>
>
> shanky191_at_rediffmail.com (Shankar) wrote in message
news:<24707dc9.0311101903.3ded3065_at_posting.google.com>...
> > I intend to write a BLOB to database by binding a variable of type
> > OCILobLocator through OCIBindByName and then OCIExecuteStmt....I get
> > this data from another module as a character array. How do i copy the
> > contents of the array into my OCILobLocator?
> >
> > char buff[MAXSIZE]; ---> contains the data i wish to write (MAXSIZE is
> > HUGE!)
> > OCILobLocator *lob_ptr;
> >
> > Now to copy buff to lob_ptr????
Received on Wed Nov 12 2003 - 01:22:53 CST