Skip to Main Content

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

User_0JAQ3Jan 26 2021

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;

Comments

The best advice I can give is to upgrade PHP, either to the free Zend Server edition or to a stock PHP distribution (http://www.oracle.com/technetwork/articles/technote-php-instant-084410.html).

Other than that, try to identify how the crash is triggered and see if you can work around it.

Since this is a Zend Core issue you could contact Zend, but I expect they will also want you to upgrade.
1 - 1

Post Details

Added on Jan 26 2021
2 comments
1,060 views