Need help with a CLOB

[Cisco]

I have a procedure that I need to call, the procedure is as follows:
procedurelargetext, :smalltext, :success)

this procedure takes a CLOB, VARCHAR2, BOOLEAN.

I need help witting to the CLOB variable.
I have about 500 kb of text that need to be written to the CLOB.
How can I achieve that?

 

[Marco Kalter]

See this topic for detailed information.

 

[Cisco]

The problem is that I do not have a CLOB column.

The CLOB variable will have a huge amount of xml that the procedure will use to manipulate different tables...

 

[Cisco]

OK, so I was able to figure out how to do it.
You can actually use a CLOB to transport data back and forth with out having a CLOB type column.
Here is what I am doing:
I have a procedure that takes XML as IN variable ( the XML could be anywhere from 1k to ?? )so using a varchar2 was no help.
I use a clob, the procedure parses the xml and does what ever it needs to be done.

So no CLOB column involved here.
Should I post here how I got around the issue?

 

[Marco Kalter]

If you think it is useful information for others, then by all means, post it...

------------------
Marco Kalter
Allround Automations

 

[Cisco]

OK, so here is what I did.
I have 2 procedures in my pakcage like this:

PROCEDURE InitClob( Clobname CLOB)IS
BEGIN
dbms_lob.CreateTemporary( Clobname, FALSE);
END;

PROCEDURE FreeClob( Clobname CLOB)IS
BEGIN
dbma_lob.FreeTemporary(Clobname);
END;

Those procedures live in the server ( lets say the package name is MyPack.

In the client App ( Delphi )
I do

MyClob: TLobLocator;

MyClob:= TLOBLocator.Create(OracleSession1,otCLOB);
MyClob.SetEmpty;

MyPack.InitClob(MyClob);

Now I assign 100 kb of aml to Myclob.

Myclob.Seek(0, soFromBeginning);
Myclob.Write(buff, size);

By now I can use MyClob to send the XMl to my procedure

MyPakc.DoXmlProcedure(MyClob);

I do not need the clob anymore, so

MyPack.FreeClob(MyClob);

Myclob.Free;

I hope this will give you an idea in how to use a clob without having a clob column...

 

[mtoon]

Thanks for the post! It really helped, but I was wondering about FreeClob... I call my function, passing the temporary clob in and the xml parser seems to work fine, but when I call FreeClob, I get an error back from DOA (3.4.6): ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275. That's coming from SYS.DBMS_LOB. Also, I had to change CLOBNAME CLOB to CLOBNAME OUT CLOB to compile. Is this correct?
Thank you- Malcolm

 

[Cisco]

I did not see nay problems..

The InitClob and FreeClob procedures need to have IN OUT parameters..

Can you post a little bit of your code so I can see what is that you are doing ...

You can also email me if you do nto want to post it here..

fjuarez@appropriatesolutions.com

 

[mtoon]

ah- i added the IN OUT to the InitClob and FreeClob procs. Reimported and recompiled.. everything works fine now. Thanks again for the post!

 

[Cisco]

Originally posted by Cisco:
OK, so here is what I did.
I have 2 procedures in my pakcage like this:

PROCEDURE InitClob( Clobname CLOB)IS
BEGIN
dbms_lob.CreateTemporary( Clobname, FALSE);
END;

PROCEDURE FreeClob( Clobname CLOB)IS
BEGIN
dbma_lob.FreeTemporary(Clobname);
END;

The procedures should be like this:

PROCEDURE InitClob( Clobname IN OUT CLOB)IS
BEGIN
dbms_lob.CreateTemporary( Clobname, FALSE);
END;

PROCEDURE FreeClob( Clobname IN OUT CLOB)IS
BEGIN
dbma_lob.FreeTemporary(Clobname);
END;