How can I covert a SQL statement with a cursor into JSON

User_0JAQ3

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;

cormaco

Duplicate thread:

https://community.oracle.com/tech/developers/discussion/4480021/convert-a-sql-statement-into-json-what-has-a-cursor-in-it#latest

User_0JAQ3

Can I delete this?