اوراکل در نسخه 21c دیتاتایپ JSON را ارائه کرد و تا قبل از آن، دیتای JSON را می توانستیم در ستونهایی با نوع داده CLOB، BLOB و حتی VARCHAR ذخیره کنیم با این اوصاف اگر دیتابیس را به تازگی به نسخه 21c(و نسخ بالاتر) ارتقا دادیم ممکن است بخواهیم دیتای از نوع JSON را به ستونی که دیتاتایپ آن JSON است منتقل کنیم.

در نسخه 23c، پروسیجری اضافه شده است که می تواند در این فرایند مورد استفاده قرار بگیرد و بعضا بسیار راهگشا باشد. پروسیجر dbms_json.json_type_convertible_check ستونی را به عنوان ورودی می گیرد و بررسی می کند همه فیلدهای آن ستون حاوی دیتای معتبر با فرمت JSON هستند و اگر در این بررسی خطایی رخ دهد این خطا از طریق جدول json_data_precheck قابل مشاهده است.

در ادامه جدولی را ایجاد می کنیم که ستونی از نوع CLOB دارد قرار است دیتای JSON را در این ستون ذخیره کنیم البته محدودیت is json constraint check را برای این ستون تنظیم نمی کنیم تا در هنگام ذخیره کردن اطلاعات، دیتای نامعتبر هم در آن قابل درج باشد.

SQL> create table author_tbl(
  2  ID number generated always as identity,
  3  author_desc clob
  4  );
Table created

SQL> insert into author_tbl(author_desc) values('{"NAME" : "Abbas Hamidian","GENDER" : "m","CURRENT_JOB" : "Oracle DBA"}');
1 row inserted

SQL> insert into author_tbl(author_desc) values('{Ali Fazli}');
1 row inserted

SQL> insert into author_tbl(author_desc) values('usefzadeh.com');
1 row inserted

SQL> commit;
Commit complete

قصد داریم اطلاعات ستون Author_Desc را به ستونی از نوع داده JSON منتقل کنیم(در اوراکل 23c). قبل از جابجایی دیتا، از طریق پروسیجر dbms_json.json_type_convertible_check بررسی می کنیم که آیا دیتای موجود در ستون Author_Desc فرمت معتبر برای JSON را دارد؟

SQL> begin
  2    dbms_json.json_type_convertible_check(
  3      owner           => 'USEF',
  4      tablename       => 'author_tbl',
  5      columnname      => 'author_desc',
  6      statustablename => 'json_data_precheck'
  7    );
  8  end;
  9  /
  
PL/SQL procedure successfully completed

بعد از اجرای این پروسیجر، جدول json_data_precheck را بررسی می کنیم تا اگر خطایی توسط پروسیجر فوق گزارش شده را برطرف کنیم:

SQL> select * from json_data_precheck;

دو رکورد گزارش شده را پاک می کنیم:

SQL> delete USEF.AUTHOR_TBL where rowid='AAAsDcAABAAAKEBAAB';
1 row deleted.
SQL> delete USEF.AUTHOR_TBL where rowid='AAAsDcAABAAAKEBAAC';
1 row deleted.
SQL> commit;
Commit complete.

پروسیجر را مجددا اجرا می کنیم:

SQL> drop table json_data_precheck ;
Table dropped.

SQL> begin
  2    dbms_json.json_type_convertible_check(
  3      owner           => 'USEF',
  4      tablename       => 'author_tbl',
  5      columnname      => 'author_desc',
  6      statustablename => 'json_data_precheck'
  7    );
  8  end;
  9  /
  
PL/SQL procedure successfully completed

این بار خطایی توسط پروسیجر گزارش نشده است:

SQL> select * from json_data_precheck;

بنابرین شرایط برای انتقال دیتا آماده است. برای این انتقال می توانیم ستونی را به جدول اضافه کنیم و JSON Data را از طریق دستور update به این ستون جدید کپی کنیم.

SQL> alter table AUTHOR_TBL add (author_desc_JSON json);
Table altered.
SQL> update AUTHOR_TBL set author_desc_JSON=json(author_desc);
1 row updated.
SQL> alter table AUTHOR_TBL drop column author_desc;
Table altered.
SQL> alter table AUTHOR_TBL rename column author_desc_JSON to author_desc;
Table altered.

جابجایی دیتا به درستی انجام شد:

SQL> select * from AUTHOR_TBL;
ID AUTHOR_DESC
-- ---------------------------------------------------
 1 {"NAME":"Abbas Hamidian","GENDER":"m","CURRENT_JOB":"Oracle DBA"}

SQL> insert into author_tbl(author_desc) values('{Armin Bahamin}');
ORA-40441: JSON syntax error
JZN-00073: missing colon in member:value pair (line 1, position 11)