[大数据]oracle把sql转换成JSON字符串

 -- Purpose   : JSON特殊字符替换   
  FUNCTION replace_json_char(p_char VARCHAR2) RETURN VARCHAR2 IS
    l_char      VARCHAR2(20000);
    l_temp_char VARCHAR2(100);
  BEGIN
    FOR i IN 1 .. length(p_char) LOOP
      l_temp_char := substr(p_char, i, 1);
      IF l_temp_char = '\' THEN
        l_char := l_char || '\\';
      ELSIF l_temp_char = '"' THEN
        l_char := l_char || '\"';
      ELSIF (ascii(l_temp_char) BETWEEN 0 AND 31)
            OR ascii(l_temp_char) = 127 THEN
        NULL;
      ELSE
        l_char := l_char || l_temp_char;
      END IF;
    END LOOP;
    RETURN l_char;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN p_char;
  END;
  PROCEDURE init_clob(x_clob        IN OUT CLOB,
                      p_init_string IN VARCHAR2) IS
  BEGIN
    -- 清空CLOB
    dbms_lob.trim(x_clob, 0);
    dbms_lob.write(lob_loc => x_clob, amount => length(p_init_string), offset => 1, buffer => p_init_string);
  END init_clob;
  PROCEDURE export_sql_result(p_sql           VARCHAR2,
                              p_metadata_flag VARCHAR2,
                              x_msg_data      OUT CLOB) IS
    l_curid   INTEGER;
    l_cnt     NUMBER;
    l_desctab dbms_sql.desc_tab;
    l_rownum  NUMBER := 0;
    l_tmp_str VARCHAR2(2000);
  
    l_stat       NUMBER;
    l_str_value  VARCHAR2(2000);
    l_num_value  NUMBER;
    l_date_value DATE;
  
    l_value VARCHAR2(2000);
  BEGIN
  
    dbms_lob.createtemporary(x_msg_data, TRUE);
    dbms_lob.open(lob_loc => x_msg_data, open_mode => dbms_lob.lob_readwrite);
    init_clob(x_clob => x_msg_data, p_init_string => '[{');
  
    l_curid := dbms_sql.open_cursor();
    dbms_sql.parse(l_curid, p_sql, dbms_sql.native);
    dbms_sql.describe_columns(l_curid, l_cnt, l_desctab);
    IF p_metadata_flag = 'Y' THEN
      FOR i IN 1 .. l_desctab.count LOOP
        IF i > 1 THEN
          l_tmp_str := ',';
          dbms_lob.writeappend(lob_loc => x_msg_data, amount => length(l_tmp_str), buffer => l_tmp_str);
        END IF;
        --col_type 1:VARCAHR2,2:NUMBER,12:DATE
        SELECT '"' || l_desctab(i).col_name || '":"' ||
               decode(l_desctab(i).col_type, 1, 'VARCAHR2', 2, 'NUMBER', 12, 'DATE', l_desctab(i).col_type) || '"'
          INTO l_tmp_str
          FROM dual;
        dbms_lob.writeappend(lob_loc => x_msg_data, amount => length(l_tmp_str), buffer => l_tmp_str);
      END LOOP;
    ELSE
      l_stat := dbms_sql.execute(l_curid); --执行动态SQL语句。 
      -- 定义列
      FOR i IN 1 .. l_cnt LOOP
        --此处是定义游标中列的读取类型，可以定义为字符，数字和日期类型，
        IF l_desctab(i).col_type = 2 THEN
          dbms_sql.define_column(l_curid, i, l_num_value);
        ELSIF l_desctab(i).col_type = 12 THEN
          dbms_sql.define_column(l_curid, i, l_date_value);
        ELSE
          dbms_sql.define_column(l_curid, i, l_str_value, 2000);
        END IF;
      END LOOP;
      --从游标中把数据检索到缓存区（BUFFER）中，缓冲区 的值只能被函数COULUMN_VALUE()所读取
      WHILE dbms_sql.fetch_rows(l_curid) > 0 LOOP
        l_rownum := l_rownum + 1;
        IF l_rownum > 1 THEN
          l_tmp_str := '},{';
          dbms_lob.writeappend(lob_loc => x_msg_data, amount => length(l_tmp_str), buffer => l_tmp_str);
        END IF;
        FOR i IN 1 .. l_cnt LOOP
          l_value := NULL;
          --  dbms_output.put_line('I=' || i);
          IF (l_desctab(i).col_type = 1) THEN
            dbms_sql.column_value(l_curid, i, l_str_value);
            --  dbms_output.put_line(l_desctab(i).col_name || ' ' || L_STR_VALUE || ', ');
            l_value := l_str_value;
          ELSIF (l_desctab(i).col_type = 2) THEN
            dbms_sql.column_value(l_curid, i, l_num_value);
            --   dbms_output.put_line(l_desctab(i).col_name || ' ' || L_NUM_VALUE || ', ');
            l_value := to_char(l_str_value);
          ELSIF (l_desctab(i).col_type = 12) THEN
            dbms_sql.column_value(l_curid, i, l_date_value);
            l_value := to_char(l_date_value, 'YYYY-MM-DD HH24:MI:SS');
            --   dbms_output.put_line(l_desctab(i).col_name || ' ' || to_char(L_DATE_VALUE, 'YYYY-MM-DD HH24:MI:SS') || ', ');
          END IF;
        
          IF i > 1 THEN
            l_tmp_str := ',';
            dbms_lob.writeappend(lob_loc => x_msg_data, amount => length(l_tmp_str), buffer => l_tmp_str);
          END IF;
          --col_type 1:VARCAHR2,2:NUMBER,12:DATE
          l_value := replace_json_char(l_value);
          SELECT '"' || l_desctab(i).col_name || '":"' || l_value || '"' INTO l_tmp_str FROM dual;
          dbms_lob.writeappend(lob_loc => x_msg_data, amount => length(l_tmp_str), buffer => l_tmp_str);
        END LOOP;
      END LOOP;
    END IF;
    l_tmp_str := '}]';
    dbms_lob.writeappend(lob_loc => x_msg_data, amount => length(l_tmp_str), buffer => l_tmp_str);
    dbms_sql.close_cursor(l_curid); --关闭游标。
     EXCEPTION
    WHEN OTHERS THEN
      x_msg_data := NULL;
  END;