create or replace
TYPE      PARAMETRO AS OBJECT
(
clave varchar(100),
valor varchar(100)
);

create or replace
TYPE     PARAMETROS AS table of PARAMETRO;

create or replace
PACKAGE JSON_FN AS

FUNCTION TABLE2JSON( pQuery IN VARCHAR2, pPARAMS IN VARCHAR2 DEFAULT NULL) RETURN CLOB;
FUNCTION PL_SQL( PARAM1 IN VARCHAR2 ) RETURN VARCHAR2;
FUNCTION PREPARE_TEXT( pText IN VARCHAR2 ) RETURN VARCHAR2;
FUNCTION SPLIT( PARAM IN OUT VARCHAR2 , SEPARATOR IN VARCHAR2 DEFAULT ‘,’ ) RETURN VARCHAR2;
FUNCTION SPLIT_PARAMS( PARAMS IN VARCHAR2, SEPARATOR IN VARCHAR2 DEFAULT ‘,’, SEPARATOR2 IN VARCHAR2 DEFAULT ‘=’ ) RETURN parametros PIPELINED;

END JSON_FN;

create or replace
PACKAGE BODY JSON_FN AS

FUNCTION TABLE2JSON( pQuery IN VARCHAR2, pPARAMS IN VARCHAR2 DEFAULT NULL) RETURN CLOB IS
resultado CLOB := ‘{‘;
vcursor  INTEGER;
vFila    VARCHAR2 (4000) := »;
vDato    VARCHAR2 (4000);
vParam   VARCHAR2 (2000) := pPARAMS;
vQuery   VARCHAR2 (4000) := pQuery;
vNumRows INTEGER;
l_cols   NUMBER;
l_desc_tab dbms_sql.desc_tab;
CURSOR cparams IS SELECT clave, valor FROM TABLE (split_params(vParam));
BEGIN
EXECUTE immediate ‘alter session set nls_numeric_characters=»,.»’;
resultado := resultado||'»result»:[‘;
FOR rparams IN cparams LOOP
IF rparams.valor IS NULL THEN
rparams.valor  := ‘(null)’;
END IF;
vQuery := REPLACE (vQuery,rparams.clave,rparams.valor);
END LOOP;
vcursor := DBMS_SQL.open_cursor;
DBMS_SQL.PARSE (vcursor, vQuery, DBMS_SQL.NATIVE);
dbms_sql.describe_columns( vcursor, l_cols, l_desc_tab );
FOR i IN 1..l_cols
LOOP
dbms_sql.define_column( vcursor, i, vDato, 4000 );
END LOOP;
vNumrows := DBMS_SQL.EXECUTE (vcursor);
LOOP
EXIT
WHEN (DBMS_SQL.fetch_rows (vcursor) <= 0);
IF LENGTH(vFila)                   > 0 THEN
resultado                       := resultado||’,’;
END IF;
vFila := ‘{‘;
FOR i IN 1..l_cols
LOOP
DBMS_SQL.COLUMN_VALUE (vcursor, i, vDato);
IF i     >1 THEN
vFila := vFila||’,’;
END IF;
vFila := vFila||'»‘||l_desc_tab(i).col_name||'»:»‘||prepare_text(vDato)||'»‘;
END LOOP;
vFila     := vFila||’}’;
resultado := resultado||vFila;
END LOOP;
resultado := resultado||’]}’;
DBMS_SQL.close_cursor(vcursor);
RETURN resultado;
END TABLE2JSON;

FUNCTION PL_SQL( PARAM1 IN VARCHAR2 ) RETURN VARCHAR2 AS
resultado VARCHAR2(32767) := »;
BEGIN
EXECUTE IMMEDIATE (param1) INTO resultado;
RETURN resultado;
EXCEPTION
WHEN OTHERS THEN
RETURN ‘ERROR en PL_SQL(‘||param1||’) :  [‘||TO_CHAR(SQLCODE)||’] ‘||SQLERRM;
END PL_SQL;

FUNCTION prepare_Text( pText IN VARCHAR2 ) RETURN VARCHAR2 AS
result VARCHAR2(8000);
BEGIN
result := REPLACE(pText,’\’,’\’);
result := REPLACE(result,’/’,’\/’);
result := REPLACE(result,'»‘,’\»‘);
result := REPLACE(result,»»,’\u0027′);
result := REPLACE(result,chr(10),’\n’);
result := REPLACE(result,chr(13),’\r’);
RETURN result;
END prepare_Text;

FUNCTION SPLIT( PARAM IN OUT VARCHAR2 , SEPARATOR IN VARCHAR2 DEFAULT ‘,’ ) RETURN VARCHAR2 AS
pos INTEGER;
aux VARCHAR2(2000) := »;
BEGIN
pos   := instr(PARAM,SEPARATOR);
aux   := SUBSTR(PARAM,1,pos-1);
PARAM := SUBSTR(PARAM,pos  +1);
RETURN aux;
END SPLIT;

FUNCTION SPLIT_PARAMS( PARAMS IN VARCHAR2, SEPARATOR IN VARCHAR2 DEFAULT ‘,’, SEPARATOR2 IN VARCHAR2 DEFAULT ‘=’ ) RETURN parametros PIPELINED IS
registro PARAMETRO := PARAMETRO(NULL,NULL);
aux VARCHAR2(2000) := PARAMS;
par VARCHAR2(2000) := »;
cla VARCHAR2(2000) := »;
pos INTEGER;
BEGIN
par        := split(aux,SEPARATOR);
WHILE( par IS NOT NULL ) LOOP
cla := split(par,SEPARATOR2);
dbms_output.put_line(cla||’=’||par);
registro.clave := cla;
registro.valor := par;
pipe ROW (registro);
par := split(aux,SEPARATOR);
END LOOP;
cla            := split(aux,SEPARATOR2);
registro.clave := cla;
registro.valor := aux;
pipe ROW (registro);
END SPLIT_PARAMS;

END JSON_FN;