导入依赖库到oracle（org.json:json:20170516）

loadjava -r -f -u user/password@ip:1521/orasid json-20170516.jar

添加java source工具类

create or replace and compile java source named "JsonUtil" as
package org.jws;

import org.json.JSONObject;

public class JsonUtil {
    // 获取字符值
    public static String getStringValue(String jsonObjStr,String key) {
        try {
            JSONObject obj = new JSONObject(jsonObjStr);
            if (obj.has(key)) {
                Object value = obj.get(key);
                return value.toString();
            }
        } catch (Exception e) {

        }
        return null;
    }

    // 获取数字值
    public static Double getNumberValue(String jsonObjStr,String key) throws RuntimeException {
        try {
            JSONObject obj = new JSONObject(jsonObjStr);
            if (obj.has(key)) {
                Object value = obj.get(key);
                if (value instanceof Number) {
                    return ((Number) value).doubleValue();
                } else {
                    return Double.parseDouble(value.toString());
                }
            }
        } catch (Exception e) {

        }
        return null;
    }
}
/

定义package

create or replace package pkg_json as --获取字符 function getstr ( jsonstr varchar2,nodename varchar2 ) return varchar2;

  --获取数值
  function getnum
  (
    jsonstr  varchar2,nodename varchar2
  ) return number;
end pkg_json;
/
create or replace package body pkg_json as function getstr ( jsonstr varchar2,nodename varchar2 ) return varchar2 as language java name 'org.jws.JsonUtil.getStringValue(java.lang.String,java.lang.String) return java.lang.String';

  function getnum
  (
    jsonstr  varchar2,nodename varchar2
  ) return number as
    language java name 'org.jws.JsonUtil.getNumberValue(java.lang.String,java.lang.String) return java.lang.Double';

end pkg_json;
/

测试

select pkg_json.getstr('{"name":"zhangsan","age":18}','name') strval,pkg_json.getnum('{"name":"zhangsan",'age') numval from dual;

tips

select * from user_java_classes a;