Prev: My Oracle DBA Blog.
Next: Primary Keys and Valid_From / Valid_To
From: sachin.garge on 23 Jun 2008 04:22
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
From: Vladimir M. Zakharychev on 24 Jun 2008 02:29
On Jun 23, 12:22 pm, sachin.ga...(a)gmail.com 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.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
 | 
Pages: 1
Prev: My Oracle DBA Blog.
Next: Primary Keys and Valid_From / Valid_To