Convert an SQL Query as JSON in Oracle 11g within Oracle APEX

Question

Within Oracle APEX v4.2 I have a fairly large table with around 40 columns and up to 50 rows where I need to fetch using SQL and convert each row to a JSON object.

I am using Oracle 11gR2 and need to use this JSON object within javascript in order to perform some graphing requirements.

With Oracle SQL query having a limitation of varchar2(4000) inside APEX, how I can I accomplish the conversion of all rows into JSON? I was looking at a result as follows to use in JavaScript, i.e.:

[{"col1": "value1", "col2": "value2", ....., "col40": "value40"}, {"col1": "value21", "col2": "value22", ....., "col40": "value400"}, etc]

Use clob data type instead of varchar2. Clob size can be several gigabytes. — user272735
– user272735, Commented May 27, 2016 at 3:40
The 4k limitation is for storage, in plsql the max length is 32k — Typo
– Typo, Commented May 27, 2016 at 10:43
Thanks. Ended up writing a function that basically constructed my JSON object via a cursor which I returned to a page item in Oracle APEX using varchar2(32767). I will be answering my own question based on this. — tonyf
– tonyf, Commented May 27, 2016 at 15:01

tonyf · Accepted Answer · 2016-05-27 15:02:38Z

1

Ended up writing a function that basically constructed my JSON object via a cursor which I returned to a page item in Oracle APEX using varchar2(32767) within my pl/sql function.

answered May 27, 2016 at 15:02

tonyf

35.8k54 gold badges168 silver badges265 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bren Over a year ago

Can you share the function?

Collectives™ on Stack Overflow

Convert an SQL Query as JSON in Oracle 11g within Oracle APEX

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related