关于Oracle中使用pljson及中文乱码解决方案

一、创建生成json的sql优化函数：

  1 create or replace function sql_to_json_clob(i_sql varchar2,i_batch_rownum number default 100) return clob is
  2   FunctionResult clob;
  3     l_clob         CLOB; --最终结果
  4     l_sql2         VARCHAR2(30000); --排序SQL
  5     l_col_name_str VARCHAR2(10000); --字段名串
  6     l_sql3         VARCHAR2(30000); --分页SQL
  7     l_row_count    NUMBER; --已经处理的行数
  8     l_sum_count    NUMBER; --总行数
  9     l_clob_tmp     CLOB; --缓存
 10     l_curid        INTEGER;
 11     l_cnt          NUMBER;
 12     l_desctab      dbms_sql.desc_tab;
 13 begin
 14    --统计行数
 15     EXECUTE IMMEDIATE 'select count(1) from (' || i_sql || ')'
 16       INTO l_sum_count;
 17     --如果没数据，返回空LIST
 18     IF l_sum_count = 0
 19     THEN
 20       RETURN '[]';
 21     END IF;
 22   
 23     --开始取字段名称
 24     l_curid := dbms_sql.open_cursor();
 25     dbms_sql.parse(l_curid, i_sql, dbms_sql.native);
 26     dbms_sql.describe_columns(l_curid, l_cnt, l_desctab);
 27     FOR i IN 1 .. l_desctab.count LOOP
 28       l_col_name_str := l_col_name_str || CASE
 29                           WHEN l_col_name_str IS NULL THEN
 30                            NULL
 31                           ELSE
 32                            ','
 33                         END || '"' || l_desctab(i).col_name || '"';
 34     END LOOP;
 35     --取字段名称结束
 36   
 37     --组装排序SQL
 38     l_sql2 := 'select t.*,row_number() over(order by ' || l_col_name_str ||
 39               ') rn from (' || i_sql || ') t order by  ' ||
 40               to_char(l_desctab.count + 1);
 41     --关闭游标          
 42     dbms_sql.close_cursor(l_curid);
 43     -- dbms_output.put_line(l_sql2);
 44   
 45     --初始化处理记录数
 46     l_row_count := 0;
 47     --初始化CLOB对象
 48     l_clob := empty_clob();
 49     dbms_lob.createtemporary(l_clob, TRUE);
 50   
 51     --开始进行分页处理
 52     LOOP
 53       --组装分页SQL
 54       l_sql3 := 'select ' || l_col_name_str || ' from (' || l_sql2 ||
 55                 ') where rn > ' || to_char(l_row_count) || ' and rn<=' ||
 56                 to_char(l_row_count + i_batch_rownum);
 57       -- dbms_output.put_line(l_sql3);
 58       --初始化缓存对象
 59       l_clob_tmp := empty_clob();
 60       dbms_lob.createtemporary(l_clob_tmp, TRUE);
 61      
 62       --SQL转换成JSONLIST再转换成CLOB，存入缓存
 63       pljson_list.to_clob(SELF       => pljson_util_pkg.sql_to_json(l_sql3,
 64                                                                     i_batch_rownum,
 65                                                                     0),
 66                           buf        => l_clob_tmp,
 67                           erase_clob => TRUE);
 68     
 69       --将缓存复制到CLOB对象
 70       dbms_lob.copy(dest_lob    => l_clob,
 71                     src_lob     => l_clob_tmp,
 72                     amount      => dbms_lob.getlength(l_clob_tmp),
 73                     dest_offset => dbms_lob.getlength(l_clob) + 1,
 74                     src_offset  => CASE
 75                                      WHEN dbms_lob.getlength(l_clob) = 0 THEN
 76                                       1
 77                                      ELSE
 78                                       2
 79                                    END);
 80     
 81       --已处理行数变大
 82       l_row_count := l_row_count + i_batch_rownum;
 83     
 84       --如果已处理行数小于总行数，把CLOB内的最后一个字符，由"]"变成"," ,否则退出循环
 85       IF l_row_count < l_sum_count
 86       THEN
 87         dbms_lob.write(lob_loc => l_clob,
 88                        amount  => 1,
 89                        offset  => dbms_lob.getlength(l_clob),
 90                        buffer  => ',');
 91       ELSE
 92         EXIT;
 93       END IF;
 94     
 95     END LOOP;
 96     --返回最终结果
 97     RETURN l_clob;
 98   EXCEPTION
 99     WHEN OTHERS THEN
100       raise_application_error(-20001, SQLERRM);
101   --return(FunctionResult);
102 end sql_to_json_clob;

通过plsql的command Window安装对应的pljosn包：

SQL> @C:/pljson/pljson-master/install.sql

使用pljosn过程中，会出现中文乱码，可以使用如下解决方案：

在程序包体中找到PLJSON_PRINTER,如下进行注释，添加else null,重新编译。

 注：本人在重新编译之后，执行函数 sql_to_json_clob无效异常，重新登录plsql即可解决。