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?
