I am trying to get key names dynamically in PL/SQL (oracle).
This is my function
create FUNCTION GET_EXPRESSION_VALUES(data CLOB) RETURN VARCHAR2 IS
action_api_response VARCHAR2(32767);
data_value VARCHAR2(32767);
json_obj JSON_OBJECT_T ;
keys JSON_KEY_LIST;
key VARCHAR2(4000);
BEGIN
action_api_response := JSON_VALUE(data, '$.action_api_response');
data_value := JSON_VALUE(action_api_response, '$.data');
--WRITE_LOG_TABLE('EVALUTION_VALUES', data_value);
json_obj := JSON_OBJECT_T(data_value);
keys := json_obj.GET_KEYS;
FOR i IN 1..LEAST(keys.COUNT, 5) LOOP
key := keys(i);
WRITE_LOG_TABLE('EVALUTION_VALUES', key);
END LOOP;
RETURN 'Success';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RETURN 'ERROR';
END;
/
And this is the JSON
{
"code": 200,
"status": "OK",
"meta": {
"responseType": "HashMap",
"responseTypeFull": "java.util.HashMap"
},
"data": {
"TOTAL_PAGES_NUM_IN_DOC": "",
"PATH": "",
"ENVELOPE_MESSAGE": "",
"INDEX_DOC": "",
"ENVELOPE_NUMBER": "",
"ADDRESS_BARCODE": "",
"ROTATE_CLOCKWISE_DEGREES": "",
"TOTAL_PAGES_NUM_IN_COMB": "",
"ENVELOPE_STUFFER_BARCODE_VAL": "",
"ID_BF_DOCUMENT_PREV": "",
"BARCODE": "",
"PAGE_PAYMENT_SLIP": "",
"FILE_NAME": "",
"DOC_PAGE_NUM_IN_BULK": "",
"INSTANCE_ID": "",
"D_DOCUMENT_REF_NBR": "",
"CUSTOMER_ACCOUNT_ID": "",
"ENVELOPE_NUM_IN_BULK": "",
"COMB_DOCUMENT_PAGE_NUM": "",
"BULK_DOCUMENT_PAGE_NUM": "",
"TOTAL_PAGES_NUM_IN_PDF_JOB": "",
"DOCUMENT_PAYMENT_SLIP": "",
"COMBINATION_ID_PREV": "",
"PAGE_COUNT_DUPLEX": "",
"COMB_TYPE": "",
"PRINT_STYLE": "",
"TOTAL_PAGES_NUM_IN_PDF_COMBINATION_ID": "",
"CUSTOMER_ENVELOPE_NUMBER": "",
"PATH_RAW": "",
"SHEET_COUNT_SIMPLEX": "",
"BULK_PHISICAL_PAGE_NUM": "",
"FILE_EXTENSION": "",
"CONTRACT_ID": "",
"RUN_ID": "",
"BRAND": "",
"ORDER_BY": "",
"RUN_START_DATE": "",
"CURR_PAGE_NUM_IN_ENV": "",
"DOCUMENT_ID": "",
"FLAG_FILE_SPLIT": "",
"PATH_ARCHIVE": "",
"SUB_BRAND": "",
"STANDING_ORDER": "",
"SOURCE_PATH": "",
"JOB_ID": "",
"PATH_PS": "",
"DOCUMENT_PAGE_NUM": "",
"TOTAL_PAGES_NUM_IN_PDF_DOCUMENT_ID": "",
"COMBINATION_TYPE": "",
"ID_BF_DOCUMENT_NEXT": "",
"TOTAL_PAGES_NUM_IN_PDF": "",
"PAGE_COUNT_SIMPLEX": "",
"TOTAL_PAGES_NUM_IN_ENV": "",
"DOCUMENT_LABEL": "",
"DOCUMENT_NAME": "",
"FIRST_IN_ENVELOPE": "",
"INDEX_ADRESS": "",
"FILE_SUBDIRECTORY": "",
"MAX_WAIT_DATE": "",
"COMBINATION_ID": "",
"LAST_IN_ENVELOPE": "",
"COMBINATION_ID_NEXT": "",
"PAGE_NUMBER": "",
"PAGE_TYPE": "",
"ENVELOPE_SIZE": "",
"ENV_NUM": "",
"FIRST_PAGE_NUM_IN_BULK_DOC": "",
"FLAG_ENVELOPE_SPLIT": "",
"PATH_SIGNED": "",
"CURR_PAGE_NUM_IN_DOC": "",
"PAGE_NUMBER_IN_PDF": "",
"FULL_DOCUMENT_NAME": "",
"ENVELOPE_ACTION": "",
"SORT_COL": "",
"ID_BF_DOCUMENT": "",
"CHANNEL_LABEL": "",
"SHEET_COUNT_DUPLEX": "",
"BARCODE_VALUE": "",
"SN_ENVELOPE_NUMBER": ""
}
}
This is how I call the function
DECLARE
KLOB CLOB;
BEGIN
KLOB := GET_EXPRESSION_VALUES('{"EVAL_FORMA":null,"action_api_response":"{\"code\":200,\"status\":\"OK\",\"meta\":{\"responseType\":\"HashMap\",\"responseTypeFull\":\"java.util.HashMap\"},\"data\":{\"TOTAL_PAGES_NUM_IN_DOC\":\"\",\"PATH\":\"\",\"ENVELOPE_MESSAGE\":\"\",\"INDEX_DOC\":\"\",\"ENVELOPE_NUMBER\":\"\",\"ADDRESS_BARCODE\":\"\",\"ROTATE_CLOCKWISE_DEGREES\":\"\",\"TOTAL_PAGES_NUM_IN_COMB\":\"50\",\"ENVELOPE_STUFFER_BARCODE_VAL\":\"\",\"ID_BF_DOCUMENT_PREV\":\"\",\"BARCODE\":\"\",\"PAGE_PAYMENT_SLIP\":\"\",\"FILE_NAME\":\"\",\"DOC_PAGE_NUM_IN_BULK\":\"\",\"INSTANCE_ID\":\"\",\"D_DOCUMENT_REF_NBR\":\"\",\"CUSTOMER_ACCOUNT_ID\":\"\",\"ENVELOPE_NUM_IN_BULK\":\"\",\"COMB_DOCUMENT_PAGE_NUM\":\"\",\"BULK_DOCUMENT_PAGE_NUM\":\"\",\"TOTAL_PAGES_NUM_IN_PDF_JOB\":\"\",\"DOCUMENT_PAYMENT_SLIP\":\"\",\"COMBINATION_ID_PREV\":\"\",\"PAGE_COUNT_DUPLEX\":\"\",\"COMB_TYPE\":\"\",\"PRINT_STYLE\":\"\",\"TOTAL_PAGES_NUM_IN_PDF_COMBINATION_ID\":\"\",\"CUSTOMER_ENVELOPE_NUMBER\":\"\",\"PATH_RAW\":\"\",\"SHEET_COUNT_SIMPLEX\":\"\",\"BULK_PHISICAL_PAGE_NUM\":\"\",\"FILE_EXTENSION\":\"\",\"CONTRACT_ID\":\"\",\"RUN_ID\":\"\",\"BRAND\":\"\",\"ORDER_BY\":\"\",\"RUN_START_DATE\":\"\",\"CURR_PAGE_NUM_IN_ENV\":\"\",\"DOCUMENT_ID\":\"\",\"FLAG_FILE_SPLIT\":\"\",\"PATH_ARCHIVE\":\"\",\"SUB_BRAND\":\"\",\"STANDING_ORDER\":\"\",\"SOURCE_PATH\":\"\",\"JOB_ID\":\"\",\"PATH_PS\":\"\",\"DOCUMENT_PAGE_NUM\":\"\",\"TOTAL_PAGES_NUM_IN_PDF_DOCUMENT_ID\":\"\",\"COMBINATION_TYPE\":\"\",\"ID_BF_DOCUMENT_NEXT\":\"\",\"TOTAL_PAGES_NUM_IN_PDF\":\"\",\"PAGE_COUNT_SIMPLEX\":\"\",\"TOTAL_PAGES_NUM_IN_ENV\":\"\",\"DOCUMENT_LABEL\":\"\",\"DOCUMENT_NAME\":\"\",\"FIRST_IN_ENVELOPE\":\"\",\"INDEX_ADRESS\":\"\",\"FILE_SUBDIRECTORY\":\"\",\"MAX_WAIT_DATE\":\"\",\"COMBINATION_ID\":\"\",\"LAST_IN_ENVELOPE\":\"\",\"COMBINATION_ID_NEXT\":\"\",\"PAGE_NUMBER\":\"\",\"PAGE_TYPE\":\"\",\"ENVELOPE_SIZE\":\"\",\"ENV_NUM\":\"\",\"FIRST_PAGE_NUM_IN_BULK_DOC\":\"\",\"FLAG_ENVELOPE_SPLIT\":\"\",\"PATH_SIGNED\":\"\",\"CURR_PAGE_NUM_IN_DOC\":\"\",\"PAGE_NUMBER_IN_PDF\":\"\",\"FULL_DOCUMENT_NAME\":\"\",\"ENVELOPE_ACTION\":\"\",\"SORT_COL\":\"\",\"ID_BF_DOCUMENT\":\"\",\"CHANNEL_LABEL\":\"\",\"SHEET_COUNT_DUPLEX\":\"\",\"BARCODE_VALUE\":\"\",\"SN_ENVELOPE_NUMBER\":\"\"}}","APPLICATION_USER":"multicom"}');
end;
The problem is, If I try to get keys from the top level, I get "code", "status", "meta", and "data". Now I want to get all the key names from data, but If I use the code written below nothing logs into the log_table. Not sure what's wrong, it should work because If I try to get single value then I get, so it's reachable.
Here is a function that does exactly that. It gets keys, values, path of each of the elements:
create or replace TYPE "JSON_ELEMENT_T" as object (
attr_level number,
attr_name varchar2(255),
attr_type varchar2(30),
attr_value varchar2(255),
attr_path varchar2(255),
parent_attr_path varchar2(255)
)
/
create or replace TYPE "JSON_ELEMENT_CT" as table of json_element_t
/
CREATE OR REPLACE FUNCTION parse_json( p_json IN CLOB ) RETURN json_element_ct IS
l_parsed_json apex_json.t_values;
l_root_children wwv_flow_t_varchar2;
l_root_node apex_json.t_value;
l_p0 NUMBER := 0;
l_p1 NUMBER := 0;
l_p2 NUMBER := 0;
l_p3 NUMBER := 0;
l_p4 NUMBER := 0;
l_result json_element_ct := json_element_ct();
FUNCTION replace_path( p_path IN VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
RETURN regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace( p_path, '%d', l_p0, 1, 1 ),
'%d', l_p1, 1, 1 ),
'%d', l_p2, 1, 1 ),
'%d', l_p3, 1, 1 ),
'%d', l_p4, 1, 1 );
END replace_path;
PROCEDURE evaluate_node(
p_prefix IN VARCHAR2,
p_node IN apex_json.t_value,
p_node_name IN VARCHAR2,
p_parent_name IN VARCHAR2 DEFAULT NULL,
p_level IN NUMBER DEFAULT 0,
p_arraylevel IN NUMBER DEFAULT 1
) IS
l_node apex_json.t_value;
l_node_children wwv_flow_t_varchar2;
l_type VARCHAR2(30);
l_value VARCHAR2(255);
BEGIN
IF p_node.kind = 2 THEN
l_type := 'boolean';
l_value := 'true';
ELSIF p_node.kind = 3 THEN
l_type := 'boolean';
l_value := 'false';
ELSIF p_node.kind = 4 THEN
l_type := 'number';
l_value := to_char( p_node.number_value );
ELSIF p_node.kind = 5 THEN
l_type := 'varchar2';
l_value := p_node.varchar2_value;
ELSIF p_node.kind = 7 THEN
l_type := 'array';
l_value := NULL;
l_result.EXTEND(1);
l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );
IF p_arraylevel = 1 THEN l_p0 := 0; END IF;
IF p_arraylevel = 2 THEN l_p1 := 0; END IF;
IF p_arraylevel = 3 THEN l_p2 := 0; END IF;
IF p_arraylevel = 4 THEN l_p3 := 0; END IF;
IF p_arraylevel = 5 THEN l_p4 := 0; END IF;
FOR j IN 1 .. p_node.number_value loop
IF p_arraylevel = 1 THEN l_p0 := l_p0 + 1; END IF;
IF p_arraylevel = 2 THEN l_p1 := l_p1 + 1; END IF;
IF p_arraylevel = 3 THEN l_p2 := l_p2 + 1; END IF;
IF p_arraylevel = 4 THEN l_p3 := l_p3 + 1; END IF;
IF p_arraylevel = 5 THEN l_p4 := l_p4 + 1; END IF;
l_node := apex_json.get_value(
p_values => l_parsed_json,
p_path => p_prefix || p_node_name|| '[%d]',
p0 => l_p0,
p1 => l_p1,
p2 => l_p2,
p3 => l_p3,
p4 => l_p4 );
evaluate_node(
p_prefix => p_prefix || p_node_name || '[%d]',
p_node => l_node,
p_node_name => NULL,
p_parent_name => p_prefix || p_node_name,
p_level => p_level + 1,
p_arraylevel => p_arraylevel + 1 );
END loop;
ELSIF p_node.kind = 6 THEN
l_type := 'object';
l_value := NULL;
l_result.EXTEND(1);
l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );
l_node_children := apex_json.get_members(
p_values => l_parsed_json,
p_path => p_prefix || p_node_name,
p0 => l_p0,
p1 => l_p1,
p2 => l_p2,
p3 => l_p3,
p4 => l_p4 );
IF l_node_children IS NOT NULL THEN
FOR i IN 1 .. l_node_children.count LOOP
l_node := apex_json.get_value(
p_values => l_parsed_json,
p_path => p_prefix || p_node_name || '.' || l_node_children( i ),
p0 => l_p0,
p1 => l_p1,
p2 => l_p2,
p3 => l_p3,
p4 => l_p4 );
evaluate_node(
p_prefix => p_prefix || p_node_name || '.',
p_node => l_node,
p_node_name => l_node_children( i ),
p_parent_name => p_prefix || p_node_name,
p_level => p_level + 1,
p_arraylevel => p_arraylevel );
END loop;
END IF;
END IF;
IF p_node.kind IN ( 2, 3, 4, 5 ) THEN
l_result.EXTEND( 1 );
l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );
END IF;
END evaluate_node;
BEGIN
apex_json.parse( l_parsed_json, p_json );
l_root_children := apex_json.get_members( p_values => l_parsed_json, p_path => '.' );
l_result.EXTEND( 1 );
l_result( l_result.count ) := json_element_t ( NULL, '<root>', 'object', NULL, '<root>', NULL );
IF l_root_children IS NOT NULL THEN
FOR i IN 1 .. l_root_children.count loop
l_root_node := apex_json.get_value(
p_values => l_parsed_json,
p_path => l_root_children( i ) );
evaluate_node(
p_prefix => NULL,
p_node => l_root_node,
p_node_name => l_root_children( i ),
p_parent_name => '<root>' );
END loop;
END IF;
RETURN l_result;
END parse_json;
/
DECLARE
l_json_nodes_ct JSON_ELEMENT_CT;
BEGIN
l_json_nodes_ct := parse_json('{
"code": 200,
"status": "OK",
"meta": {
"responseType": "HashMap",
"responseTypeFull": "java.util.HashMap"
},
"data": {
"TOTAL_PAGES_NUM_IN_DOC": "",
"PATH": "",
"ENVELOPE_MESSAGE": ""
}
}');
FOR r IN 1 .. l_json_nodes_ct.COUNT LOOP
dbms_output.put_line(l_json_nodes_ct(r).attr_name);
END LOOP;
END;
-----
attr_level:
attr_name: <root>
attr_type: object
attr_value:
attr_path: <root>
-----
attr_level: 0
attr_name: code
attr_type: number
attr_value: 200
attr_path: code
-----
attr_level: 0
attr_name: status
attr_type: varchar2
attr_value: OK
attr_path: status
etc...
Credits go to @Carsten Czarski