json_value.extended_str is empty and "ORA-22922: nonexistent LOB value" error.

[anpegar]

Hi folk !!

First of all, thank you to Jonas Krogsboell for his great package JSON.

I have a problem when I try to get the "json_value.extended_str" property from my JSON.
The property "ContentBase64" has a file in Base64 and its size is greater than 32k (32767).
I think that the problem is that the JSON package has not a method to get a CLOB and for this reason in the json_value constructor the code is saved the data in "json_value.extended_str".

My JSON is:

{"Error":null,
"Attachments":[{"Content":null,
"ContentBase64":"\/9j\/4AAQSkZJRgABAQEAYABgAAD\/2w... too large",
"Name":"404.jpg"},
{"Content":null,
"ContentBase64":"fXCseOf1quVk86PQKK5G4+NH ... too large",
"Name":"button.png"}]
}

And my pls/sql code:

DECLARE
--
V_ATTACH_CLOB CLOB := EMPTY_CLOB();
V_ATTACH_VALUE JSON_VALUE;
V_ATTACH_JS JSON;
V_DATA_LIST JSON_LIST;
V_CONTENT_JS JSON;
V1 JSON_VALUE;
V_CLOB CLOB := EMPTY_CLOB();
--
BEGIN
--
ASP_ENTORNO_ASP.INIT(1);
--
SELECT TEXTO
INTO V_ATTACH_CLOB
FROM TRAZA3;
--
V_ATTACH_JS := JSON(V_ATTACH_CLOB);
V_ATTACH_VALUE := V_ATTACH_JS.get('Attachments');
V_DATA_LIST := JSON_LIST(V_ATTACH_VALUE);
--
FOR I IN 1..V_DATA_LIST.COUNT LOOP
--
V_CONTENT_JS := JSON(V_DATA_LIST.get(I));
V1 := V_CONTENT_JS.GET('ContentBase64');
--
V1.to_clob(V_CLOB); -- This instruction fails, show the error ORA-22922: nonexistent LOB value
--
END LOOP;
--
END;
/

The error occurred:

ORA-22922: nonexistent LOB value
ORA-06512: at "SYS.DBMS_LOB", line 560
ORA-06512: at "JSON_PRINTER", line 299
ORA-06512: at "JSON_VALUE", line 155
ORA-06512: at line 29

When I check out the "json value.extended str" property, is empty.
I've also done a test with PL/SQL Developert but I lose the clue in json_value(object_or_array sys.anydata) method.

What is the error? What am I doing wrong?

Thanks in advance and any help will be very apreciated.

[anpegar]

I've continued researching and I've seen that only fails when I try to get a json property that is in an array. For example, if my JSON is {"fooBase64":"\/9j\/4AAQSkZJRgABAQEAYABgAAD\/2..."} when I execute myjson.get("fooBase64") it works fine but when my json is {"fooArrBase64":[{"fooBase64":"xasaee.."},{"fooBase64":"xasaee.."}]} it doesn't work fine.

I think that the problem could be in the method "json.to_json_value", I don't understand the sentence "json_value(sys.anydata.convertobject(self));" and I think that when this is executed the data disappears.

Thanks again.

[Herb Swift]

No guarantee that this relates to, or fixes, what you've run into, but a
few years ago I found and fixed a problem I was having with null clobs
http://sourceforge.net/p/pljson/discussion/935365/thread/4cacb4bb/. It
isn't in 1.04 and there hasn't been a release since I posted this. So
you'll need to do the replace yourself. If it solves the problem you're
encountering, then awesome.

--
Herb Swift™ (sæ•só•nus ra•pí•dus™)

"Keep your stick on the ice and keep reaching for the stars."
-- Red Green, ret. April 7, 2006 and Casey Kasem, d. June 15, 2014

On Tue, Nov 18, 2014 at 10:48 AM, anpegar anpegar@users.sf.net wrote:

I've continued researching and I've seen that only fails when I try to get
a json property that is in an array. For example, if my JSON is
{"fooBase64":"\/9j\/4AAQSkZJRgABAQEAYABgAAD\/2..."} when I execute
myjson.get("fooBase64") it works fine but when my json is {"fooArrBase64":
[{"fooBase64":"xasaee.."},{"fooBase64":"xasaee.."}]} it doesn't work
fine.

I think that the problem could be in the method "json.to_json_value", I
don't understand the sentence
"json_value(sys.anydata.convertobject(self));" and I think that when this
is executed the data disappears.

Thanks again.

json_value.extended_str is empty and "ORA-22922: nonexistent LOB value"
error.
https://sourceforge.net/p/pljson/discussion/935365/thread/6159f5bd/?limit=25#9f7d

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/pljson/discussion/935365/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[anpegar]

Hi Herb !!
Thank you for your reply ... at least I know that this foro is not dead ;-)

I've already read your post about null CLOBs and how to fix it inside the
CONSTRUCTOR FUNCTION json_value(str CLOB, esc BOOLEAN DEFAULT TRUE) ... but this is not the problem because I put a trace to read the property "extended_str" and exist data when the constructor is invoked.

The problem is when the property has a CLOB and it is in a JSON that is inside of array (json_list).
I continue thinking that the problem is here "json_value(sys.anydata.convertobject(self));" because when I read the property of the json_value result, the properties str or extended_str are empty.

It is a shame that the author has not continued publishing new versions because the package is wonderful and Oracle has nothing native to work with JSON ... that I know.

Thanks.
Toni.

[Jonas Krogsboell]

Hi all

I Will look into all the reported issues when i return from my vacation

[anpegar]

Ok, thank you very much for your attention and I looking forward to hearing from you as soon as possible.

I hope you have a great vacation !!

Sincerily,
Toni.

PSD: About what date will you come back from your vacation?

[Jonas Krogsboell]

Perhaps you should look at https://sourceforge.net/p/pljson/code/HEAD/tree/svn/examples/ex11.sql

It seems that you're missing dbms_lob.createtemporary(V_CLOB, true);

[anpegar]

Hi Jonas !!
Thank you very much for your reply but it doesn't work :-(
I've attached the complete JSON that I've tested and this is my code:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
--
V_ATTACH_CLOB CLOB := EMPTY_CLOB();
V_ATTACH_VALUE JSON_VALUE;
V_ATTACH_JS JSON;
V_DATA_LIST JSON_LIST;
V_CONTENT_JS JSON;
V_FILE_VALUE JSON_VALUE;
V_MY_FILE CLOB := EMPTY_CLOB();
--
BEGIN
--
SELECT TEXTO
INTO V_ATTACH_CLOB
FROM TRAZA3;
--
V_ATTACH_JS := JSON(V_ATTACH_CLOB);
V_ATTACH_VALUE := V_ATTACH_JS.get('Attachments');
V_DATA_LIST := JSON_LIST(V_ATTACH_VALUE);
--
FOR I IN 1..V_DATA_LIST.COUNT LOOP
--
V_CONTENT_JS := JSON(V_DATA_LIST.get(I));

V_FILE_VALUE := V_CONTENT_JS.GET('ContentBase64');
--
dbms_lob.createtemporary(V_MY_FILE, true);
--
-- This instruction fails, 
-- show the error ORA-22922: nonexistent LOB value
--
V_FILE_VALUE.to_clob(V_MY_FILE,true); 
--
dbms_output.put_line(dbms_lob.getlength(V_MY_FILE));
dbms_lob.freetemporary(V_MY_FILE);
--

END LOOP;
--
END;
/

Could you test it?

Thanks in advance.
Toni.

[anpegar]

Hi Jonas !!

Have you been able to seen the issue? Any idea?

Thanks in advance.
Toni.

[Jonas Krogsboell]

Sorry, I've been quite busy and will be for a while.