Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 473 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How can I covert a SQL statement with a cursor into JSON

I have a requirement to convert a SQL Statement into JSON. That's easy enough to do with JSON_ARRAYAGG and JSON_OBJECT, however the query has a cursor in the middle and not sure of the proper syntax for converting cursor data in JSON... Please see query below...
SELECT
JSON_ARRAYAGG(
JSON_OBJECT('pr.ratingid' VALUE pr.ratingid, 'remote_external_rating_id' VALUE prr.paperrefnum,
'STATUS' VALUE pr.status,'patron_id' VALUE pr.ptnid, 'device_id' VALUE d.devid,
'device_asset_number' VALUE d.assetnum,
'device_type_id' VALUE pr.devtypid, 'device_type_description' VALUE dt.devtypname,
'main_game_id' VALUE pr.gameid,
'gaming_day' VALUE TO_CHAR( CAST(pr.dateinserted AS TIMESTAMP WITH TIME ZONE), 'RRRR-MM-DD"T"HH24:MI:SSTZH:TZM' ),
'shift_id' VALUE pr.shiftid, 'date_time_in' VALUE TO_CHAR( CAST(pr.datetimein AS TIMESTAMP WITH TIME ZONE), 'RRRR-MM-DD"T"HH24:MI:SSTZH:TZM' ),
'date_time_out' VALUE TO_CHAR( CAST(pr.datetimeout AS TIMESTAMP WITH TIME ZONE), 'RRRR-MM-DD"T"HH24:MI:SSTZH:TZM' ), 'seat_number' VALUE pr.seatnum ,'time_on_device' VALUE pr.timeondevsec,
'hold_time_seconds' VALUE pr.holdtimesec, 'cash_buy_in' VALUE pr.cashbuyin, 'chips_buy_in' VALUE pr.chipsbuyin, 'marker_buy_in' VALUE pr.markerbuyin, 'cpv_buy_in' VALUE pr.cpvbuyin,
'front_money_buy_in' VALUE pr.fodbuyin, 'cwa_buy_in' VALUE pr.cwabuyin, 'match_buy_in' VALUE pr.matchbuyin, 'promo_buy_in' VALUE pr.promobuyin,
'voucher_buy_in' VALUE pr.titobuyin, 'total_buy_in' VALUE pr.totbuyin, 'chip_walk' VALUE (select NVL(sum(TOTWALKWITH), 0)
from ems.ptnrtgcurrencydetail
where ratingid=pr.ratingid and buyintypid=5),
'cwa_walk' VALUE (select NVL(sum(TOTWALKWITH), 0)
from ems.ptnrtgcurrencydetail where ratingid=pr.ratingid and buyintypid=13),
'voucher_walk' VALUE (select NVL(sum(TOTWALKWITH), 0)
from ems.ptnrtgcurrencydetail where ratingid=pr.ratingid and buyintypid=12),
'total_walk' VALUE pr.totwalkwith,
CURSOR (SELECT prd.gameid FROM ems.ptnratingdetail prd WHERE prd.ratingid = pr.ratingid)
'rated_username' VALUE pr.lastmodby, '
rated_employee_id' VALUE nvl( e.empid, 0 ),
'game_theo_hold_percent' VALUE pr.gametheholdpct,
'average_wager' VALUE pr.avgwager,
'theo_win' VALUE pr.theowin,
'points_awarded' VALUE pr.creditpoint,
'comp_points_awarded' VALUE pr.creditcomppoint
)
)
FROM ems.ptnrating pr, ems.devicetype dt, ems.employee e, ems.device d, ems.ptnratingref prr
WHERE pr.ratingid = P_value
AND dt.devtypid = pr.devtypid
AND e.loginname (+) = pr.lastmodby AND d.devid = pr.devid AND prr.ratingid (+) = pr.ratingid;
Answers
-
-
Can I delete this?