0

For the first time in my life i am working with Python and I have a problem that I can not find how to solve so i will try to be clear to explain myself as best as possible and read their comments. I put them in context. I'm doing a microservice that should return the attributes of an object. The microservice visualization has to be of the JSON format.

I've been doing some tests (none of them work for me). One, an Oracle Store Procedure to return an XML because I would like the sp code to be more dynamic. I do not know how to take that output variable and transform it into JSON within Oracle itself. Or if I can not, I would like to know if that output variable can be transformed into a JSON with Python

The other, which I do not like very much, is to make the "JSON" format within the SP and return a varchar of that content.

The second option where I generate the JSON, the package does this:

PROCEDURE get_attributes (p_element IN VARCHAR2, p_list OUT CLOB) AS
    str_json_inicial    VARCHAR2(100) := '{';
    str_json_final      VARCHAR2(100) := '"}';
    str_json1           VARCHAR2(100) := '"role":"';
    str_json2           VARCHAR2(100) := '","identification":"';
    str_json3           VARCHAR2(100) := '","class":"';
    str_json4           VARCHAR2(100) := '","country":"';
    str_json5           VARCHAR2(100) := '","stateOrProvince":"';
    str_json6           VARCHAR2(100) := '","city":"';
    str_json7           VARCHAR2(100) := '","locality":"';
    str_json8           VARCHAR2(100) := '","streetName":"';
    str_json9           VARCHAR2(100) := '","streetNr":"';
    str_json10          VARCHAR2(100) := '","x":"';
    str_json11          VARCHAR2(100) := '","y":"';
    respuesta           CLOB;
BEGIN
    select 
        TO_CLOB(str_json_inicial
        || str_json1
        || 'completarRol' 
        || str_json2
        || id_equipo_gis 
        || str_json3
        || 'completarClass' 
        || str_json4
        || pais 
        || str_json5
        || provincia 
        || str_json6
        || partido 
        || str_json7
        || localidad 
        || str_json8
        || calle 
        || str_json9
        || altura 
        || str_json10
        || poste_x 
        || str_json11
        || poste_y 
        || str_json_final)
        into p_list
    from xxy_osf_equ_equipos
       where id_equipo_gis = p_element;
    EXCEPTION WHEN NO_DATA_FOUND then
    BEGIN
        p_list := to_clob('{}');
    END;
    WHEN OTHERS then
    BEGIN
        p_list := to_clob('{ERROR}');
    END;
END get_attributes;

In both cases I have no idea how to make it work.

I'll give you some sample lines of Python of the first service I did, which works for me, but it's very harcode.

listOutput = cur.var(cx_Oracle.CURSOR)
l = cur.callproc('relationships.get_relationships', (elementId, listOutput))
cur = database.db.cursor()
result = []
for cur in listOutput.getvalue().fetchall():
    result.append({
                  'identification': cur[0],
                  'elementRole': cur[1],
                  'direction': cur[2],})
return result

where listOutput is a cursor type variable of oracle and I'm placing the JSON tags.

Does anyone have any ideas, suggestions or recommendations?

alejomarchan
  • 368
  • 1
  • 10
  • 20
  • Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Python 3.7.0 – alejomarchan Oct 25 '18 at 17:24
  • There is a python library for encoding or decoding JSON objects. See this link [https://docs.python.org/3.7/library/json.html](https://docs.python.org/3.7/library/json.html) – Tobin Oct 25 '18 at 17:24
  • Oracle 12c has some JSON capability, although it's way better in 12cR2 than in R1. In 11g your best bet is to generate an XML payload, pass that to Python and convert it there. It looks like Python has lots of different extensions for converting XML to JSON. [Find out more](https://stackoverflow.com/q/191536/146325). – APC Oct 25 '18 at 17:48

0 Answers0