oracle存储过程导入导出json文件

从安装oracle19c和sql developer开始，途中遇到几十个问题，研究了大半天，注意点：

• exportjson将test1的数据以json格式导出，importjson导入
• 注意filepath不是普通的变量，首先create or replace directory DIR_TEST as ‘d:\test’;将’d:\test’赋值给变量DIR_TEST ，再将DIR_TEST 的这个变量名的字符串当filepath使用。这里被坑了很久
• DROP DIRECTORY DIR_TEST;把DIRECTORY DIR_TEST注销。注销后无法使用DIR_TEST

CREATE OR REPLACE PROCEDURE exportjson(filepath in varchar2,filename in varchar2)
IS
    v_file UTL_FILE.FILE_TYPE;
    v_data CLOB;
    cursor cursor_  is select JSON_OBJECT(*) from test1;
BEGIN
  v_file := UTL_FILE.FOPEN(filepath, filename, 'w');
  UTL_FILE.PUT(v_file, '[');
  OPEN cursor_;
  LOOP
    fetch cursor_ into v_data;
    EXIT WHEN cursor_%NOTFOUND;
    --dbms_output.put_line(v_data);
    UTL_FILE.PUT(v_file, v_data);
    UTL_FILE.PUT(v_file, ',');
  END LOOP;
  UTL_FILE.PUT(v_file, ']');
  CLOSE cursor_;
  UTL_FILE.FCLOSE(v_file);
END;

CREATE OR REPLACE PROCEDURE importjson(filepath in varchar2,filename in varchar2)
IS
BEGIN
  INSERT INTO test1 (id, name, age)
    SELECT id, name, age FROM 
      JSON_TABLE(BFILENAME(filepath, filename),     --'DIR_TEST','test1.json'
      --'[{"ID":"a","NAME":"aa","AGE":11},{"ID":"b","NAME":"bb","AGE":22},{"ID":"c","NAME":"cc","AGE":33},]', 
      '$[*]' COLUMNS (ID,NAME,AGE)     --distinguish the lower/upper case
    );
  COMMIT;
END;

create or replace directory DIR_TEST as 'd:\test';
execute exportjson('DIR_TEST','test1.json');
--execute importjson('DIR_TEST','test1.json');
DROP DIRECTORY DIR_TEST;