Re: passing CLOB in Stored Procedure as IN/OUT

From: Vladimir M. Zakharychev <>
Date: Mon, 23 Jun 2008 23:29:36 -0700 (PDT)
Message-ID: <>

On Jun 23, 12:22 pm, wrote:
> Hi,
> I am trying to get an answer for 'Is it required to create a temporary
> lob before doing an in/out operation on a lob being passed as in/out
> parameter in a stored-procedure?"
> I have seen some of the OCI samples, wherein temporary lob is created
> using OCILobCreateTemporary call and then we use OCILobWrite or
> OCILobRead call but could not find a reason to do so.
> I think it is logical to create a temporary lob for a table having
> CLOB column rather for stored-procedure.
> any suggestions will be helpful
> Thanks,
> Sachin

No, it's not necessary to always create a temporary LOB: you can pass a valid LOB locator pointing to some existing LOB in a table just the same. For example, you can create a new row in a table with LOB column defaulting to empty_{c|b}lob(), select it for update and then pass the locator to your stored procedure (or just select existing LOB for update.) The only requirement for LOB I/O is that the LOB locator being read/written is valid (the LOB it points to exists;) it makes no difference if the LOB is temporary or permanent.


   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm) Received on Tue Jun 24 2008 - 01:29:36 CDT

Original text of this message