ReaderMX.
四海内皆兄弟

说点Oracle的JSON使用的注意事项

四薛晓刚约 6 分钟

     JSON这种数据类型在关系型数据库中已经是标配了。如果不是深度应用比如游戏那种的确是离开不了MongoDB，其他的一些简单场景的话Oracle MySQL PostgreSQL都有JSON。今天仅仅说一下Oracle的JSON在使用上的一些问题。

     开发人员提出他的场景中需要使用JSON（这个之前我推荐过，现在Oracle和MySQL的JSON都普遍使用，毕竟有了这个减少了数据库的表的变更也是好的）。我的原则是不参与业务逻辑的、不参与运算的、不用来当where条件的可以放在JSON中，反之不要放进来。不过这次开发给我提出了一个问题，就是看到官方介绍（开发能去看数据库官方介绍，其实很不容易，为这位开发同学点赞。我都没怎么去看过）

Image

     考虑到性能问题，开发打算用Varchar2的数据类型。不过这个类型有限制，只有4K大小。开发表示也够用。只是想问问万一超过4K怎么做？我第一反应就是用CLOB，因为我在Oracle12C的时候就是用CLOB来完成JSON的。那么这个是不是可以改变类型呢？做个实验。

SQL> create table x (id int,j varchar2(4000) CONSTRAINT ensure_json CHECK (j IS JSON));

表已创建。

SQL> desc x;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
J VARCHAR2(4000)

创建了一个VARCHAR2类型的JSON的表。去写入一条数据。

SQL> insert into x values (1,'{"tel" : "137", "company": "ouyeel", "name" : "张","address":"宝山"}');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from x;

ID
----------
J
--------------------------------------------------------------------------------
1
{"tel" : "137", "company": "ouyeel", "name" : "张","address":"宝山"}

这里要注意，表名一定要有别名，当有了别名t以后查询正常输出。

SQL> select j.tel from x;
select j.tel from x
*
第 1 行出现错误:
ORA-00904: "J"."TEL": 标识符无效

SQL> select x.j.tel from x;
select x.j.tel from x
*
第 1 行出现错误:
ORA-00904: "X"."J"."TEL": 标识符无效

SQL> select t.j.tel from x t;

TEL
--------------------------------------------------------------------------------
137

再创建一个CLOB的JSON，看看能不能数据迁移？

SQL> create table x2 (id int,j clob CONSTRAINT ensure_json2 CHECK (j IS JSON));

表已创建。

SQL> insert into x2 values (1,'{"tel" : "138", "company": "ouyeel", "name" : "张2","address":"宝山"}');

已创建 1 行。

SQL> commit;

提交完成。

SQL> insert into x2 select * from x;

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from x2 t;

ID
----------
J
--------------------------------------------------------------------------------
1
{"tel" : "138", "company": "ouyeel", "name" : "张2","address
":"宝山"}

1
{"tel" : "137", "company": "ouyeel", "name" : "张","address"
:"宝山"}

实测可以迁移。那么也就是可以字段赋值了。在VARCHAR2的JSON表上再增加一个CLOB的JSON列。

SQL> alter table x add j_clob clob CONSTRAINT ensure_json3 CHECK (j_clob IS JSON);

表已更改。

SQL> update x set j_clob=j;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from x t;

ID
----------
J
--------------------------------------------------------------------------------
J_CLOB
--------------------------------------------------------------------------------
1
{"tel" : "137", "company": "ouyeel", "name" : "张","address":"宝山"}
{"tel" : "137", "company": "ouyeel", "name" : "张","address"
:"宝山"}

可以看出，迁移数据没有问题。但是这个做法有点麻烦。其实数据库可以定义VARCHAR2类型到32K。只是这个方法理论上可行，但是实际上不推荐大家做。

因为需要修改数据库参数，这个参数还是静态参数，需要重启生效（这个知识点很少地方介绍到）

SQL> show parameter MAX_STRING_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL>

连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

修改参数前备份参数，这个很重要。

修改参数前备份参数，这个很重要。

修改参数前备份参数，这个很重要。

SQL> alter system set max_string_size=EXTENDED scope=both;
alter system set max_string_size=EXTENDED scope=both
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效 ORA-02095:
无法修改指定的初始化参数

SQL> alter system set max_string_size=EXTENDED scope=spfile;

系统已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 1.0133E+10 bytes
Fixed Size 14416848 bytes
Variable Size 8355053568 bytes
Database Buffers 1744830464 bytes
Redo Buffers 19136512 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter pluggable database all open;
alter pluggable database all open
*
第 1 行出现错误:
ORA-14694: 数据库必须处于 UPGRADE 模式下才能开始 MAX_STRING_SIZE 迁移

修改以后还带了一些问题。

尽管我执行了相关的命令但是最终还是回到了Ora-14694的这里。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup upgrade;
ORACLE 例程已经启动。

Total System Global Area 1.0133E+10 bytes
Fixed Size 14416848 bytes
Variable Size 8355053568 bytes
Database Buffers 1744830464 bytes
Redo Buffers 19136512 bytes
数据库装载完毕。
数据库已经打开。
SQL> @/u01/app/oracle/product/19.3.0/db/rdbms/admin/utl32k.sql

会话已更改。

会话已更改。

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

未选定行

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database does not have compatible >= 12.0.0
DOC>
DOC> Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL 过程已成功完成。

会话已更改。

已更新 0 行。

提交完成。

系统已更改。

PL/SQL 过程已成功完成。

提交完成。

系统已更改。

会话已更改。

会话已更改。

表已创建。

表已创建。

表已创建。

表被截断。

已创建 0 行。

PL/SQL 过程已成功完成。

STARTTIME
--------------------------------------------------------------------------------
11/29/2023 10:06:31.825868000

PL/SQL 过程已成功完成。

没有错误。

PL/SQL 过程已成功完成。

会话已更改。

会话已更改。

已创建 0 行。

未选定行

未选定行

DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if we encountered an error while modifying a column to
DOC> account for data type length change as a result of enabling or
DOC> disabling 32k types.
DOC>
DOC> Contact Oracle support for assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL 过程已成功完成。

PL/SQL 过程已成功完成。

提交完成。

程序包已变更。

会话已更改。

     尽管这个执行成功，但是最终打开数据库时候还是回到了Ora-14694的这里。虽然这个问题应该是可以解决的，但是我还没去深究怎么处理比较好。

    由于修改失败，好在备份了参数，用原来参数启动数据库。所以将VARCHAR2变成32K的方案不推荐正式环境使用。尽管我相信一定是支持的，但是很多文档没说明要什么代价，以及影响。就实际踩坑而言就不建议用32K了。而是采用varchar4000或者clob。这个在上线之前预估一下。

Image

KEEP THE THREAD GOING

更多来源文章