Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Generating JSON from refcursor in R12.2 without APEX_JSON

Jeremy TreagueJul 21 2017 — edited Jul 25 2017

Below is an example of how JSON could be generated based on the content of a cursor through the use of the APEX_JSON package that is part of APEX 5:

https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#ref-cursor-to-json

In v12.2 of the Oracle Database there are new types and functions introduced for working with JSON data:

https://oracle-base.com/articles/12c/sql-json-functions-12cr2

Has anyone had experience with generating JSON in 12.2 based on a cursor through the use of these new functions (i.e. without installing APEX 5 and using APEX_JSON package)?  If so, can you share an example?

Thus far I have been able to do it by opening the cursor and iterating over values to construct an instance of the JSON_OBJECT_T type.  But I would like to know if there if there is a way to do this without manually constructing this type - similar to how it could be done using a call such as: APEX_JSON.write('employees', l_cursor);

Comments

i don't see that behavior, also on 4.1.3

monitor_session_query.jpg

1 person found this helpful
Buntoro

Hi Jeff,

It seems to be program specific problem.

We have an old ETL Tools called Sagent and it seems all queries fired from that tool are coming in this one-line format.

Notice the horizontal scroll bar at the bottom.

Please don't mind the Master layout of the Session Monitor, which I adjust to be more informational with Gauge Bar and so on.

pastedImage_2.png

For the time being, I need to open another worksheet, copy and paste that line and use Ctrl + F7 to re-format.

pastedImage_4.png

Thank you for clarifying.

Regards,

Buntoro

Buntoro

Hi Jeff,

On second thought.

I need to unmark as correct.

Probably this problem happened on DML command (insert / update / delete) only.

Here is another example, query coming from user tool (PL/SQL Developer).

pastedImage_0.png

I try to dig into the query for "Active SQL" and try to execute it myself on worksheet.

Notice the SID = 2024 which I get from the above.

I thought that it will return one line, which justify why SQL Dev show single line output.

However, as you can see it is not.

pastedImage_1.png

The question still hold.

Regards,

Buntoro

you customized the report...does it do this on the original Tools > Monitor Session screen?

Buntoro

I copy the Report from the Template (All Reports - Data Dictionary Reports - Database Administration - Sessions - Sessions).

And paste it under User Defined Reports, and name it Session Browser instead.

Every time I open Tools - Monitor Sessions, it seems to be pointing out to the User Defined Reports - Session Browser (the one that I modified).

Regards,

Buntoro

thatJeffSmith-Oracle

what's the sql behind your report? compare it to mine in the screenshot up top

tools > monitor sessions going to your custom report...that would be weird. maybe something else is happening

Buntoro

Hi Jeff,

Mine is :

select replace(q.SQL_TEXT, chr(0), chr(10) || chr(13)) sql_text

  from gv$session s, v$sqltext_with_newlines q

where  s.sql_address = q.address

  and  s.sql_hash_value = q.hash_value

  and  s.sid = :SID

order by q.piece

I also tried yours too. But on detail tab produces the same result.

select replace(q.SQL_FULLTEXT,chr(0)) sql_text

  from gv$session s, gv$sql q

where  s.sql_address = q.address

  and  s.sql_hash_value = q.hash_value

  and  s.sid = :SID

FYI, if executed on Worksheet

My query will produce multiple rows result

Your query will produce single line result

Nevertheless, both produce the same single line output on Detail tab.

Regards,

Buntoro

1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 22 2017
Added on Jul 21 2017
4 comments
887 views