andrew krenitz

I used pretty print to convert to varchar then I was able to use.

Hi, I am using Oracle Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production. I am able to create the object I need and want to call as follows: PROCEDURE member_json_gateway ( p_process IN VARCHAR2, p_data IN VARCHAR2, p_result OUT json).... In my java I have loginUser = "{call member_info.member_json_gateway(?,?,?)}"; cstmt = conn.prepareCall (loginUser); cstmt.setString(1, toprocess); cstmt.setString(2, data); cstmt.registerOutParameter(3, OracleTypes.CLOB); I do not know how to Map...

found my answer DECLARE ret json; v_id members.member_id%type; v_zip_code zip_codes.zip_code%type := '97317'; BEGIN ret := json_dyn.executeobject ( stmt=> 'SELECT zip_code, zip_code_state state, zip_code_primary_city city FROM zip_codes where zip_code = NVL(:bindme,zip_code) and rownum < 50' ,bindvar => json('{bindme:"'||v_zip_code||'"}') ); ret.print; END;

I have the following. DECLARE ret json; v_id members.memebr_id%type; BEGIN ret := json_dyn.executeobject('SELECT mem.member_id, mem.member_password, mem.member_username FROM members mem where mem.member_id = NVL(v_id,mem.member_id)' ); ret.print; END; How do I get the where clause to work? Thank you