Mixing
Extracting very long string from JSON to CLOB
0
I'm trying to extract a very long string into clob from json_object_t and got some weird database behaviour (12.2c) with json_object_t.get_clob(key) method.
There is a sample code than does following:
DECLARE
l_data CLOB := '{"text": "very long string about 1M chars"}';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
BEGIN
l_json := json_object_t.parse(l_data);
l_text := l_json.get_clob('text');
dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
END;
When string length in a 'text' key is less than 32k chars, get_clob method works just fine and shows appropriate result, but with longer strings it produces an empty clob with zero length, just like get_string, but without 'character string buffer too small' exception.
I've tried to get same data via json_table query, but it cannot extract data to clob column at all, only varchar/number is allowed.
Is that a bug or am I doing something wrong? Is there any other ways to extract long strings from JSON keys?
json string oracle plsql
asked Jul 24 '17 at 9:57
cainecaine
35
add a comment |
0
I'm trying to extract a very long string into clob from json_object_t and got some weird database behaviour (12.2c) with json_object_t.get_clob(key) method.
There is a sample code than does following:
DECLARE
l_data CLOB := '{"text": "very long string about 1M chars"}';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
BEGIN
l_json := json_object_t.parse(l_data);
l_text := l_json.get_clob('text');
dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
END;
When string length in a 'text' key is less than 32k chars, get_clob method works just fine and shows appropriate result, but with longer strings it produces an empty clob with zero length, just like get_string, but without 'character string buffer too small' exception.
I've tried to get same data via json_table query, but it cannot extract data to clob column at all, only varchar/number is allowed.
Is that a bug or am I doing something wrong? Is there any other ways to extract long strings from JSON keys?
json string oracle plsql
asked Jul 24 '17 at 9:57
cainecaine
35
add a comment |
0
0
0
I'm trying to extract a very long string into clob from json_object_t and got some weird database behaviour (12.2c) with json_object_t.get_clob(key) method.
There is a sample code than does following:
DECLARE
l_data CLOB := '{"text": "very long string about 1M chars"}';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
BEGIN
l_json := json_object_t.parse(l_data);
l_text := l_json.get_clob('text');
dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
END;
When string length in a 'text' key is less than 32k chars, get_clob method works just fine and shows appropriate result, but with longer strings it produces an empty clob with zero length, just like get_string, but without 'character string buffer too small' exception.
I've tried to get same data via json_table query, but it cannot extract data to clob column at all, only varchar/number is allowed.
Is that a bug or am I doing something wrong? Is there any other ways to extract long strings from JSON keys?
json string oracle plsql
asked Jul 24 '17 at 9:57
cainecaine
35
I'm trying to extract a very long string into clob from json_object_t and got some weird database behaviour (12.2c) with json_object_t.get_clob(key) method.
There is a sample code than does following:
DECLARE
l_data CLOB := '{"text": "very long string about 1M chars"}';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
BEGIN
l_json := json_object_t.parse(l_data);
l_text := l_json.get_clob('text');
dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
END;
When string length in a 'text' key is less than 32k chars, get_clob method works just fine and shows appropriate result, but with longer strings it produces an empty clob with zero length, just like get_string, but without 'character string buffer too small' exception.
I've tried to get same data via json_table query, but it cannot extract data to clob column at all, only varchar/number is allowed.
Is that a bug or am I doing something wrong? Is there any other ways to extract long strings from JSON keys?
json string oracle plsql
json string oracle plsql
asked Jul 24 '17 at 9:57
cainecaine
35
asked Jul 24 '17 at 9:57
cainecaine
35
edited Jul 24 '17 at 10:26
caine
asked Jul 24 '17 at 9:57
cainecaine
35
asked Jul 24 '17 at 9:57
cainecaine
35
asked Jul 24 '17 at 9:57
cainecaine
35
35
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
0
I work with Oracle Database JSON Store group and would be happy to assist you with this issue you're facing. Could you try the alternate get_Clob procedure instead of this function and tell us what the behavior is?
Signature:
MEMBER PROCEDURE get_Clob(key VARCHAR2, c IN OUT CLOB)
Please try this:
DECLARE
content_json CLOB := '{"value":"';
content_json_end CLOB := '"}';
content_tmp CLOB := 'ab';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
tmp clob;
BEGIN
-- 13 gives 16K
-- 14 gives 32K
FOR count IN 1 .. 14
loop
dbms_lob.append(content_tmp, content_tmp); -- a bad append for now
END loop;
dbms_lob.append(content_json, content_tmp);
dbms_lob.append(content_json, content_json_end);
l_json := json_object_t.parse(content_json);
l_json.get_clob('value', l_text); -- !!! TRY THIS PROC get_Clob
--l_text := l_json.get_clob('value');
dbms_output.put_line('Lob size in Kb: ');
dbms_output.put_line(dbms_lob.getLength(l_text) / 1024);
END;
/
Looking forward to your findings..
answered Aug 21 '17 at 22:06
SrikrishnanSrikrishnan
3572924
Hi, just wanted to check with you on this.. Did you give the other method a try? Did that work?
– Srikrishnan
Oct 24 '17 at 23:01
alas, same result: Lob size in Kb: 0. moreover, get_blob works fine - livesql.oracle.com/apex/livesql/file/…
– caine
Jan 18 '18 at 6:57
Caine, I am the guy who developed the JSON_OBJECT_T, etc methods. Unfortunately, I cannot reproduce your issue (tried a couple earlier releases as well). Please provide me with this info: 1) what database character set are you using SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 2) what version of the data base do you use? SELECT version FROM V$INSTANCE;
– Beda Hammerschmidt
Jan 19 '18 at 21:57
1
Have it reproduced now on the 12.2.0.1 database version with ALF32UTF8 character set. We have fixed this in the meantime. Please contact Oracle Support to get a backport to your database version. Thanks you.
– Beda Hammerschmidt
Jan 19 '18 at 23:03
add a comment |
0
This works as well. Instead using the the get_clob method, use c:
DECLARE
CURSOR crsrJSON IS
SELECT
json_object( 'employee_id' VALUE employee_id,
'first_name' VALUE first_name,
'last_name' VALUE last_name,
'email' VALUE email,
'phone_number' VALUE phone_number,
'hire_date' VALUE to_char(hire_date,'MM/DD/YYYY'),
'job_id' VALUE job_id,
'salary' VALUE nvl(salary,0),
'commission_pct' VALUE nvl(commission_pct,0),
'manager_id' VALUE NVL(manager_id,0),
'department_id' VALUE NVL(department_id,0),
'department_name' VALUE (select department_name from departments x where x.department_id = hr.department_id),
'job_title' VALUE (select job_title from jobs x where x.job_id = hr.job_id)) emp_data
FROM
employees hr;
js_array JSON_ARRAY_T := new JSON_ARRAY_T;
json_obj JSON_OBJECT_T := JSON_OBJECT_T();
json_clob CLOB := EMPTY_CLOB();
BEGIN
FOR data_rec IN crsrJSON LOOP
js_array.append(JSON_ELEMENT_T.parse(data_rec.emp_data));
END LOOP;
json_obj.put('data',js_array);
IF json_obj.has('data') THEN
json_clob := json_obj.to_clob;
DBMS_OUTPUT.PUT_LINE(json_clob);
ELSE
DBMS_OUTPUT.PUT_LINE('Nope');
END IF;
END;
edited Nov 20 '18 at 16:33
Hiten004
2,0111529
answered Nov 20 '18 at 16:22
Jason BennettJason Bennett
12
This works as well (related to my example code) if you want to pull out a long key value: (json_elem JSON_ELEMENT_T;) json_elem := json_obj.get('data'); DBMS_OUTPUT.PUT_LINE(json_elem.to_clob);
– Jason Bennett
Nov 20 '18 at 16:37
add a comment |
0
with data as
( select
xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
from tblname
)
select
rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
as very_long_json
from data;
edited Jan 21 at 13:13
JoSSte
98021531
answered Jan 21 at 12:27
Shahbaz AliShahbaz Ali
133
Just execute you will find your dream json :D
– Shahbaz Ali
Jan 21 at 13:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f45277547%2fextracting-very-long-string-from-json-to-clob%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I work with Oracle Database JSON Store group and would be happy to assist you with this issue you're facing. Could you try the alternate get_Clob procedure instead of this function and tell us what the behavior is?
Signature:
MEMBER PROCEDURE get_Clob(key VARCHAR2, c IN OUT CLOB)
Please try this:
DECLARE
content_json CLOB := '{"value":"';
content_json_end CLOB := '"}';
content_tmp CLOB := 'ab';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
tmp clob;
BEGIN
-- 13 gives 16K
-- 14 gives 32K
FOR count IN 1 .. 14
loop
dbms_lob.append(content_tmp, content_tmp); -- a bad append for now
END loop;
dbms_lob.append(content_json, content_tmp);
dbms_lob.append(content_json, content_json_end);
l_json := json_object_t.parse(content_json);
l_json.get_clob('value', l_text); -- !!! TRY THIS PROC get_Clob
--l_text := l_json.get_clob('value');
dbms_output.put_line('Lob size in Kb: ');
dbms_output.put_line(dbms_lob.getLength(l_text) / 1024);
END;
/
Looking forward to your findings..
answered Aug 21 '17 at 22:06
SrikrishnanSrikrishnan
3572924
Hi, just wanted to check with you on this.. Did you give the other method a try? Did that work?
– Srikrishnan
Oct 24 '17 at 23:01
alas, same result: Lob size in Kb: 0. moreover, get_blob works fine - livesql.oracle.com/apex/livesql/file/…
– caine
Jan 18 '18 at 6:57
Caine, I am the guy who developed the JSON_OBJECT_T, etc methods. Unfortunately, I cannot reproduce your issue (tried a couple earlier releases as well). Please provide me with this info: 1) what database character set are you using SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 2) what version of the data base do you use? SELECT version FROM V$INSTANCE;
– Beda Hammerschmidt
Jan 19 '18 at 21:57
1
Have it reproduced now on the 12.2.0.1 database version with ALF32UTF8 character set. We have fixed this in the meantime. Please contact Oracle Support to get a backport to your database version. Thanks you.
– Beda Hammerschmidt
Jan 19 '18 at 23:03
add a comment |
0
I work with Oracle Database JSON Store group and would be happy to assist you with this issue you're facing. Could you try the alternate get_Clob procedure instead of this function and tell us what the behavior is?
Signature:
MEMBER PROCEDURE get_Clob(key VARCHAR2, c IN OUT CLOB)
Please try this:
DECLARE
content_json CLOB := '{"value":"';
content_json_end CLOB := '"}';
content_tmp CLOB := 'ab';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
tmp clob;
BEGIN
-- 13 gives 16K
-- 14 gives 32K
FOR count IN 1 .. 14
loop
dbms_lob.append(content_tmp, content_tmp); -- a bad append for now
END loop;
dbms_lob.append(content_json, content_tmp);
dbms_lob.append(content_json, content_json_end);
l_json := json_object_t.parse(content_json);
l_json.get_clob('value', l_text); -- !!! TRY THIS PROC get_Clob
--l_text := l_json.get_clob('value');
dbms_output.put_line('Lob size in Kb: ');
dbms_output.put_line(dbms_lob.getLength(l_text) / 1024);
END;
/
Looking forward to your findings..
answered Aug 21 '17 at 22:06
SrikrishnanSrikrishnan
3572924
Hi, just wanted to check with you on this.. Did you give the other method a try? Did that work?
– Srikrishnan
Oct 24 '17 at 23:01
alas, same result: Lob size in Kb: 0. moreover, get_blob works fine - livesql.oracle.com/apex/livesql/file/…
– caine
Jan 18 '18 at 6:57
Caine, I am the guy who developed the JSON_OBJECT_T, etc methods. Unfortunately, I cannot reproduce your issue (tried a couple earlier releases as well). Please provide me with this info: 1) what database character set are you using SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 2) what version of the data base do you use? SELECT version FROM V$INSTANCE;
– Beda Hammerschmidt
Jan 19 '18 at 21:57
1
Have it reproduced now on the 12.2.0.1 database version with ALF32UTF8 character set. We have fixed this in the meantime. Please contact Oracle Support to get a backport to your database version. Thanks you.
– Beda Hammerschmidt
Jan 19 '18 at 23:03
add a comment |
0
0
0
I work with Oracle Database JSON Store group and would be happy to assist you with this issue you're facing. Could you try the alternate get_Clob procedure instead of this function and tell us what the behavior is?
Signature:
MEMBER PROCEDURE get_Clob(key VARCHAR2, c IN OUT CLOB)
Please try this:
DECLARE
content_json CLOB := '{"value":"';
content_json_end CLOB := '"}';
content_tmp CLOB := 'ab';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
tmp clob;
BEGIN
-- 13 gives 16K
-- 14 gives 32K
FOR count IN 1 .. 14
loop
dbms_lob.append(content_tmp, content_tmp); -- a bad append for now
END loop;
dbms_lob.append(content_json, content_tmp);
dbms_lob.append(content_json, content_json_end);
l_json := json_object_t.parse(content_json);
l_json.get_clob('value', l_text); -- !!! TRY THIS PROC get_Clob
--l_text := l_json.get_clob('value');
dbms_output.put_line('Lob size in Kb: ');
dbms_output.put_line(dbms_lob.getLength(l_text) / 1024);
END;
/
Looking forward to your findings..
answered Aug 21 '17 at 22:06
SrikrishnanSrikrishnan
3572924
I work with Oracle Database JSON Store group and would be happy to assist you with this issue you're facing. Could you try the alternate get_Clob procedure instead of this function and tell us what the behavior is?
Signature:
MEMBER PROCEDURE get_Clob(key VARCHAR2, c IN OUT CLOB)
Please try this:
DECLARE
content_json CLOB := '{"value":"';
content_json_end CLOB := '"}';
content_tmp CLOB := 'ab';
l_json json_object_t;
l_text CLOB := EMPTY_CLOB();
tmp clob;
BEGIN
-- 13 gives 16K
-- 14 gives 32K
FOR count IN 1 .. 14
loop
dbms_lob.append(content_tmp, content_tmp); -- a bad append for now
END loop;
dbms_lob.append(content_json, content_tmp);
dbms_lob.append(content_json, content_json_end);
l_json := json_object_t.parse(content_json);
l_json.get_clob('value', l_text); -- !!! TRY THIS PROC get_Clob
--l_text := l_json.get_clob('value');
dbms_output.put_line('Lob size in Kb: ');
dbms_output.put_line(dbms_lob.getLength(l_text) / 1024);
END;
/
Looking forward to your findings..
answered Aug 21 '17 at 22:06
SrikrishnanSrikrishnan
3572924
edited Sep 7 '17 at 18:19
answered Aug 21 '17 at 22:06
SrikrishnanSrikrishnan
3572924
answered Aug 21 '17 at 22:06
SrikrishnanSrikrishnan
3572924
answered Aug 21 '17 at 22:06
SrikrishnanSrikrishnan
3572924
3572924
Hi, just wanted to check with you on this.. Did you give the other method a try? Did that work?
– Srikrishnan
Oct 24 '17 at 23:01
alas, same result: Lob size in Kb: 0. moreover, get_blob works fine - livesql.oracle.com/apex/livesql/file/…
– caine
Jan 18 '18 at 6:57
Caine, I am the guy who developed the JSON_OBJECT_T, etc methods. Unfortunately, I cannot reproduce your issue (tried a couple earlier releases as well). Please provide me with this info: 1) what database character set are you using SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 2) what version of the data base do you use? SELECT version FROM V$INSTANCE;
– Beda Hammerschmidt
Jan 19 '18 at 21:57
1
Have it reproduced now on the 12.2.0.1 database version with ALF32UTF8 character set. We have fixed this in the meantime. Please contact Oracle Support to get a backport to your database version. Thanks you.
– Beda Hammerschmidt
Jan 19 '18 at 23:03
add a comment |
Hi, just wanted to check with you on this.. Did you give the other method a try? Did that work?
– Srikrishnan
Oct 24 '17 at 23:01
alas, same result: Lob size in Kb: 0. moreover, get_blob works fine - livesql.oracle.com/apex/livesql/file/…
– caine
Jan 18 '18 at 6:57
Caine, I am the guy who developed the JSON_OBJECT_T, etc methods. Unfortunately, I cannot reproduce your issue (tried a couple earlier releases as well). Please provide me with this info: 1) what database character set are you using SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 2) what version of the data base do you use? SELECT version FROM V$INSTANCE;
– Beda Hammerschmidt
Jan 19 '18 at 21:57
1
Have it reproduced now on the 12.2.0.1 database version with ALF32UTF8 character set. We have fixed this in the meantime. Please contact Oracle Support to get a backport to your database version. Thanks you.
– Beda Hammerschmidt
Jan 19 '18 at 23:03
Hi, just wanted to check with you on this.. Did you give the other method a try? Did that work?
– Srikrishnan
Oct 24 '17 at 23:01
Hi, just wanted to check with you on this.. Did you give the other method a try? Did that work?
– Srikrishnan
Oct 24 '17 at 23:01
alas, same result: Lob size in Kb: 0. moreover, get_blob works fine - livesql.oracle.com/apex/livesql/file/…
– caine
Jan 18 '18 at 6:57
alas, same result: Lob size in Kb: 0. moreover, get_blob works fine - livesql.oracle.com/apex/livesql/file/…
– caine
Jan 18 '18 at 6:57
Caine, I am the guy who developed the JSON_OBJECT_T, etc methods. Unfortunately, I cannot reproduce your issue (tried a couple earlier releases as well). Please provide me with this info: 1) what database character set are you using SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 2) what version of the data base do you use? SELECT version FROM V$INSTANCE;
– Beda Hammerschmidt
Jan 19 '18 at 21:57
Caine, I am the guy who developed the JSON_OBJECT_T, etc methods. Unfortunately, I cannot reproduce your issue (tried a couple earlier releases as well). Please provide me with this info: 1) what database character set are you using SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 2) what version of the data base do you use? SELECT version FROM V$INSTANCE;
– Beda Hammerschmidt
Jan 19 '18 at 21:57
1
1
Have it reproduced now on the 12.2.0.1 database version with ALF32UTF8 character set. We have fixed this in the meantime. Please contact Oracle Support to get a backport to your database version. Thanks you.
– Beda Hammerschmidt
Jan 19 '18 at 23:03
Have it reproduced now on the 12.2.0.1 database version with ALF32UTF8 character set. We have fixed this in the meantime. Please contact Oracle Support to get a backport to your database version. Thanks you.
– Beda Hammerschmidt
Jan 19 '18 at 23:03
add a comment |
0
This works as well. Instead using the the get_clob method, use c:
DECLARE
CURSOR crsrJSON IS
SELECT
json_object( 'employee_id' VALUE employee_id,
'first_name' VALUE first_name,
'last_name' VALUE last_name,
'email' VALUE email,
'phone_number' VALUE phone_number,
'hire_date' VALUE to_char(hire_date,'MM/DD/YYYY'),
'job_id' VALUE job_id,
'salary' VALUE nvl(salary,0),
'commission_pct' VALUE nvl(commission_pct,0),
'manager_id' VALUE NVL(manager_id,0),
'department_id' VALUE NVL(department_id,0),
'department_name' VALUE (select department_name from departments x where x.department_id = hr.department_id),
'job_title' VALUE (select job_title from jobs x where x.job_id = hr.job_id)) emp_data
FROM
employees hr;
js_array JSON_ARRAY_T := new JSON_ARRAY_T;
json_obj JSON_OBJECT_T := JSON_OBJECT_T();
json_clob CLOB := EMPTY_CLOB();
BEGIN
FOR data_rec IN crsrJSON LOOP
js_array.append(JSON_ELEMENT_T.parse(data_rec.emp_data));
END LOOP;
json_obj.put('data',js_array);
IF json_obj.has('data') THEN
json_clob := json_obj.to_clob;
DBMS_OUTPUT.PUT_LINE(json_clob);
ELSE
DBMS_OUTPUT.PUT_LINE('Nope');
END IF;
END;
edited Nov 20 '18 at 16:33
Hiten004
2,0111529
answered Nov 20 '18 at 16:22
Jason BennettJason Bennett
12
This works as well (related to my example code) if you want to pull out a long key value: (json_elem JSON_ELEMENT_T;) json_elem := json_obj.get('data'); DBMS_OUTPUT.PUT_LINE(json_elem.to_clob);
– Jason Bennett
Nov 20 '18 at 16:37
add a comment |
0
This works as well. Instead using the the get_clob method, use c:
DECLARE
CURSOR crsrJSON IS
SELECT
json_object( 'employee_id' VALUE employee_id,
'first_name' VALUE first_name,
'last_name' VALUE last_name,
'email' VALUE email,
'phone_number' VALUE phone_number,
'hire_date' VALUE to_char(hire_date,'MM/DD/YYYY'),
'job_id' VALUE job_id,
'salary' VALUE nvl(salary,0),
'commission_pct' VALUE nvl(commission_pct,0),
'manager_id' VALUE NVL(manager_id,0),
'department_id' VALUE NVL(department_id,0),
'department_name' VALUE (select department_name from departments x where x.department_id = hr.department_id),
'job_title' VALUE (select job_title from jobs x where x.job_id = hr.job_id)) emp_data
FROM
employees hr;
js_array JSON_ARRAY_T := new JSON_ARRAY_T;
json_obj JSON_OBJECT_T := JSON_OBJECT_T();
json_clob CLOB := EMPTY_CLOB();
BEGIN
FOR data_rec IN crsrJSON LOOP
js_array.append(JSON_ELEMENT_T.parse(data_rec.emp_data));
END LOOP;
json_obj.put('data',js_array);
IF json_obj.has('data') THEN
json_clob := json_obj.to_clob;
DBMS_OUTPUT.PUT_LINE(json_clob);
ELSE
DBMS_OUTPUT.PUT_LINE('Nope');
END IF;
END;
edited Nov 20 '18 at 16:33
Hiten004
2,0111529
answered Nov 20 '18 at 16:22
Jason BennettJason Bennett
12
This works as well (related to my example code) if you want to pull out a long key value: (json_elem JSON_ELEMENT_T;) json_elem := json_obj.get('data'); DBMS_OUTPUT.PUT_LINE(json_elem.to_clob);
– Jason Bennett
Nov 20 '18 at 16:37
add a comment |
0
0
0
This works as well. Instead using the the get_clob method, use c:
DECLARE
CURSOR crsrJSON IS
SELECT
json_object( 'employee_id' VALUE employee_id,
'first_name' VALUE first_name,
'last_name' VALUE last_name,
'email' VALUE email,
'phone_number' VALUE phone_number,
'hire_date' VALUE to_char(hire_date,'MM/DD/YYYY'),
'job_id' VALUE job_id,
'salary' VALUE nvl(salary,0),
'commission_pct' VALUE nvl(commission_pct,0),
'manager_id' VALUE NVL(manager_id,0),
'department_id' VALUE NVL(department_id,0),
'department_name' VALUE (select department_name from departments x where x.department_id = hr.department_id),
'job_title' VALUE (select job_title from jobs x where x.job_id = hr.job_id)) emp_data
FROM
employees hr;
js_array JSON_ARRAY_T := new JSON_ARRAY_T;
json_obj JSON_OBJECT_T := JSON_OBJECT_T();
json_clob CLOB := EMPTY_CLOB();
BEGIN
FOR data_rec IN crsrJSON LOOP
js_array.append(JSON_ELEMENT_T.parse(data_rec.emp_data));
END LOOP;
json_obj.put('data',js_array);
IF json_obj.has('data') THEN
json_clob := json_obj.to_clob;
DBMS_OUTPUT.PUT_LINE(json_clob);
ELSE
DBMS_OUTPUT.PUT_LINE('Nope');
END IF;
END;
edited Nov 20 '18 at 16:33
Hiten004
2,0111529
answered Nov 20 '18 at 16:22
Jason BennettJason Bennett
12
This works as well. Instead using the the get_clob method, use c:
DECLARE
CURSOR crsrJSON IS
SELECT
json_object( 'employee_id' VALUE employee_id,
'first_name' VALUE first_name,
'last_name' VALUE last_name,
'email' VALUE email,
'phone_number' VALUE phone_number,
'hire_date' VALUE to_char(hire_date,'MM/DD/YYYY'),
'job_id' VALUE job_id,
'salary' VALUE nvl(salary,0),
'commission_pct' VALUE nvl(commission_pct,0),
'manager_id' VALUE NVL(manager_id,0),
'department_id' VALUE NVL(department_id,0),
'department_name' VALUE (select department_name from departments x where x.department_id = hr.department_id),
'job_title' VALUE (select job_title from jobs x where x.job_id = hr.job_id)) emp_data
FROM
employees hr;
js_array JSON_ARRAY_T := new JSON_ARRAY_T;
json_obj JSON_OBJECT_T := JSON_OBJECT_T();
json_clob CLOB := EMPTY_CLOB();
BEGIN
FOR data_rec IN crsrJSON LOOP
js_array.append(JSON_ELEMENT_T.parse(data_rec.emp_data));
END LOOP;
json_obj.put('data',js_array);
IF json_obj.has('data') THEN
json_clob := json_obj.to_clob;
DBMS_OUTPUT.PUT_LINE(json_clob);
ELSE
DBMS_OUTPUT.PUT_LINE('Nope');
END IF;
END;
edited Nov 20 '18 at 16:33
Hiten004
2,0111529
answered Nov 20 '18 at 16:22
Jason BennettJason Bennett
12
edited Nov 20 '18 at 16:33
Hiten004
2,0111529
edited Nov 20 '18 at 16:33
Hiten004
2,0111529
edited Nov 20 '18 at 16:33
Hiten004
2,0111529
2,0111529
answered Nov 20 '18 at 16:22
Jason BennettJason Bennett
12
answered Nov 20 '18 at 16:22
Jason BennettJason Bennett
12
answered Nov 20 '18 at 16:22
Jason BennettJason Bennett
12
12
This works as well (related to my example code) if you want to pull out a long key value: (json_elem JSON_ELEMENT_T;) json_elem := json_obj.get('data'); DBMS_OUTPUT.PUT_LINE(json_elem.to_clob);
– Jason Bennett
Nov 20 '18 at 16:37
add a comment |
This works as well (related to my example code) if you want to pull out a long key value: (json_elem JSON_ELEMENT_T;) json_elem := json_obj.get('data'); DBMS_OUTPUT.PUT_LINE(json_elem.to_clob);
– Jason Bennett
Nov 20 '18 at 16:37
This works as well (related to my example code) if you want to pull out a long key value: (json_elem JSON_ELEMENT_T;) json_elem := json_obj.get('data'); DBMS_OUTPUT.PUT_LINE(json_elem.to_clob);
– Jason Bennett
Nov 20 '18 at 16:37
This works as well (related to my example code) if you want to pull out a long key value: (json_elem JSON_ELEMENT_T;) json_elem := json_obj.get('data'); DBMS_OUTPUT.PUT_LINE(json_elem.to_clob);
– Jason Bennett
Nov 20 '18 at 16:37
add a comment |
0
with data as
( select
xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
from tblname
)
select
rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
as very_long_json
from data;
edited Jan 21 at 13:13
JoSSte
98021531
answered Jan 21 at 12:27
Shahbaz AliShahbaz Ali
133
Just execute you will find your dream json :D
– Shahbaz Ali
Jan 21 at 13:14
add a comment |
0
with data as
( select
xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
from tblname
)
select
rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
as very_long_json
from data;
edited Jan 21 at 13:13
JoSSte
98021531
answered Jan 21 at 12:27
Shahbaz AliShahbaz Ali
133
Just execute you will find your dream json :D
– Shahbaz Ali
Jan 21 at 13:14
add a comment |
0
0
0
with data as
( select
xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
from tblname
)
select
rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
as very_long_json
from data;
edited Jan 21 at 13:13
JoSSte
98021531
answered Jan 21 at 12:27
Shahbaz AliShahbaz Ali
133
with data as
( select
xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
from tblname
)
select
rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
as very_long_json
from data;
edited Jan 21 at 13:13
JoSSte
98021531
answered Jan 21 at 12:27
Shahbaz AliShahbaz Ali
133
edited Jan 21 at 13:13
JoSSte
98021531
edited Jan 21 at 13:13
JoSSte
98021531
edited Jan 21 at 13:13
JoSSte
98021531
98021531
answered Jan 21 at 12:27
Shahbaz AliShahbaz Ali
133
answered Jan 21 at 12:27
Shahbaz AliShahbaz Ali
133
answered Jan 21 at 12:27
Shahbaz AliShahbaz Ali
133
133
Just execute you will find your dream json :D
– Shahbaz Ali
Jan 21 at 13:14
add a comment |
Just execute you will find your dream json :D
– Shahbaz Ali
Jan 21 at 13:14
Just execute you will find your dream json :D
– Shahbaz Ali
Jan 21 at 13:14
Just execute you will find your dream json :D
– Shahbaz Ali
Jan 21 at 13:14
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f45277547%2fextracting-very-long-string-from-json-to-clob%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
