dbms_lob.close ?!?!?!
Rajeshwaran, Jeyabal, February 25, 2017 - 10:43 am UTC
Team,
Any reason to miss the " dbms_lob.close(b); " in the above program?
since it is a "BFILE" - so closing them using DBMS_LOB doesn't matter much ?
demo@ORA12C> create table t (x clob check(x IS JSON) );
Table created.
demo@ORA12C> declare
2 l_file bfile := bfilename('DATA_PUMP_DIR','json.dat');
3 c clob;
4 begin
5 dbms_lob.open(l_file);
6 dbms_lob.createtemporary(c,true);
7 dbms_lob.loadfromfile(c,l_file,
8 dbms_lob.getlength(l_file) );
9 insert into t(x) values(c);
10 commit;
11 dbms_lob.close(l_file);
12 dbms_lob.freetemporary(c);
13 end;
14 /
PL/SQL procedure successfully completed.
February 26, 2017 - 1:59 am UTC
Tardiness on my part - also the brevity of my answer due to an inclination to match the brevity of detail in the question :-)
Follow up last post
A reader, February 26, 2017 - 6:34 am UTC
You could reply. It was lunch time and I had closed the session afterwhile😎
"All files opened during a user session are implicitly closed at the end of the session. However, Oracle strongly recommends that you close the files after both normal and abnormal termination of operations on the BFILE."
Binish George, June 05, 2019 - 3:48 pm UTC
Hi,
I tried to load the json file using the method explained here but when I check the table I see the data has been loaded in a different language altogether (chinese I guess). Not sure why this is happening. Any thoughts?
Binish
June 06, 2019 - 4:48 pm UTC
A character set conversion problem at a guess. But you'd need to share details of what exactly has happened for us to give effective help...
Very informative post
John, June 11, 2019 - 11:38 am UTC
I like this post. It is very useful for me. It is giving more information and ideas for my work. I hope you will keep this website updated so users can come and read interesting stories. I am very impressed by the way you share nice and valuable content.
Thanks Regards
https://esmartprints.com/product-category/pop-up-banners/
JSON_TRANSFORM in Oracle 21c
Rajeshwaran, Jeyabal, October 19, 2021 - 6:08 am UTC
Team,
Given this JSON, how do i increment the quantity by two in each level?
tried the below using json_transform but ended up with error.
is that possible using json_transform function? kinldy help.
demo@XEPDB1> select json_serialize(y pretty) y
2 from t
3 where x =2;
Y
----------------------------------------
{
"produce" :
[
{
"fruit" : "apple",
"quantity" : 10
},
{
"fruit" : "orange",
"quantity" : 15
}
]
}
demo@XEPDB1> select json_serialize(
2 json_transform( y, set '$.produce[*].quantity' =
3 '$.produce[*].quantity' + 2 ) pretty )
4 from t
5 where x =2;
'$.produce[*].quantity' + 2 ) pretty )
*
ERROR at line 3:
ORA-01722: invalid number
demo@XEPDB1>
October 21, 2021 - 1:56 am UTC
Picking loosely related questions in order to post a new question in is not how the site works, because we lose the granularity for future community members to find answers.
(But yes it can be done)
JSON_TRANSFORM in Oracle 21c
Rajeshwaran, Jeyabal, October 19, 2021 - 7:08 am UTC
Team,
Was able to get this done using JSON object types from PL/SQL.
would like to know if the same can be done using JSON_TRANSFROM function in sql?
demo@XEPDB1> create or replace function update_json( p_input json )
2 return json
3 as
4 l_data json_object_t;
5 l_size number := 0;
6 l_ele json_element_t;
7 l_array json_array_t;
8 l_obj json_object_t;
9 l_qty number := 0;
10 begin
11 l_data := json_object_t( p_input );
12
13 if l_data.has('produce') then
14 l_ele := l_data.get('produce');
15 if l_ele.is_array then
16 l_size := l_ele.get_size()-1;
17 l_array := json_array_t( l_ele );
18 for i in 0..l_size
19 loop
20 l_obj := treat( l_array.get(i) as json_object_t );
21 l_qty := l_obj.get_Number('quantity');
22 l_obj.put( 'quantity', l_qty+2 );
23 end loop;
24 end if;
25 end if;
26 return l_data.to_json;
27 end;
28 /
Function created.
demo@XEPDB1> select json_serialize(y)
2 from t t1
3 where x =2;
JSON_SERIALIZE(Y)
--------------------------------------------------------------------------------------
{"produce":[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]}
demo@XEPDB1> select update_json(y)
2 from t t1
3 where x =2;
UPDATE_JSON(Y)
--------------------------------------------------------------------------------
{"produce":[{"fruit":"apple","quantity":12},{"fruit":"orange","quantity":17}]}
demo@XEPDB1>
October 21, 2021 - 1:56 am UTC
Picking loosely related questions in order to post a new question in is not how the site works, because we lose the granularity for future community members to find answers.
JSON_TRANSFORM in Oracle 21c
Rajeshwaran Jeyabal, October 21, 2021 - 3:52 am UTC
Thanks Connor, will take care of this in future and will make this as a new question for further follow up.
October 22, 2021 - 3:49 am UTC
Appreciate your understanding