时间戳

首页 > 解决方案 > JSON 字符串到 CLOB 对象

java - JSON 字符串到 CLOB 对象

问题描述

我正在使用 java 代码创建新的 dbms_job，作业创建成功，但处理它时调用 job_action 失败。在 job_action 我正在调用过程来调用 oracle 加载的 java 代码。Clob 由 json 字符串组成。似乎 json 格式导致 oracle.sql.Clob 对象出现问题。

我不确定，json 到 json 是否有任何问题。为什么它在这里失败了？

爪哇代码：

connection = DBBroker.getConnection();
            stmt = (CallableStatement) connection.prepareCall("begin ? := PKGRESTASSIGNBLOCK.CREATEJOB(?, ?, ?); end;");

            CLOB clob = null;
            clob = CLOB.createTemporary(connection, false, CLOB.MODE_READWRITE);

            clob.open(CLOB.MODE_READWRITE);
            clob.setString(1, requestString);

            stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
            stmt.setClob(2, clob);
            stmt.setString(3, requestID);
            stmt.setInt(4, requestType);
            stmt.execute();

甲骨文程序：

CREATE OR REPLACE
PACKAGE BODY      PKGRESTASSIGNBLOCK
AS



FUNCTION createjob(request IN CLOB, requestID IN VARCHAR2, type IN NUMBER) RETURN VARCHAR2
IS
                    l_jobname  varchar2(1044);
BEGIN
      BEGIN
      l_jobname := NULL;

      l_jobname := SUBSTR('JOB_IPBlk_' || requestID ||'_'|| to_char(SYSTIMESTAMP, 'yymmddhhmmssFF'), 0, 30);
      DBMS_SCHEDULER.create_job(
                                job_name => l_jobname,
                                job_type => 'PLSQL_BLOCK',
                                job_action => 'begin pkgrestassignblock.assignblock ('|| request || ', ' || requestID || ', '|| type||'); end ;',
                                start_date => SYSTIMESTAMP,
                                repeat_interval      => NULL,
                                end_date             => NULL,
                                enabled              => TRUE,
                                comments             => 'Job defined entirely by the CREATE JOB procedure.');

      END;
      DBMS_OUTPUT.put_line ('l_jobname : ' ||l_jobname);
      RETURN l_jobname;
END createjob;



PROCEDURE assignblock (request IN CLOB, requestID IN VARCHAR2, type IN NUMBER)
AS
LANGUAGE JAVA
NAME 'com.abcd.efgh.AssignBlock.handleRequest(java.sql.Clob, java.lang.String, java.lang.String, int)';



END PKGRESTASSIGNBLOCK;

甲骨文日志：

logs

ORA-12012: error on auto execute of job "JOB_BLK_20022019_19022008020"
ORA-06550: line 1, column 799:
PLS-00103: Encountered the symbol "{" when expecting one of the following:

   ( ) - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   table continue avg count current exists max min prior sql
   stddev sum variance execute multiset the both leading
   trailing forall merge year month day hour minute second
   timezone_hour timezone_minute timezone_region timezone_abbr
   time timestamp interval date
   <a string literal with character set specification>
Wed Feb 20 20:55:03 2019
Errors in file path/trace/CMM_j002_30847.trc:
ORA-12012: error on auto execute of job ""JOB_BLK_20022019_19022008020"
ORA-06550: line 1, column 756:
PLS-00306: wrong number or types of arguments in call to 'ASSIGNBLOCK'
ORA-06550: line 1, column 756:
PL/SQL: Statement ignored

标签： javaoracleprocedureclobdbms-job

解决方案

好吧，我认为您可能会遇到问题，job_action因为VARCHAR2. 我不确定它是否限制为 4000 或 32767 个字符，但无论哪种方式，如果您的 JSON 很长，它可能会破坏这一点。

但是，您的主要问题是您没有引用字符串。尝试更改此行：

job_action => 'pkgrestassignblock.assignblock('''|| request || ''', ''' || requestID || ''', '|| type||');',

我还删除了，begin..end;因为作业将操作包装在它自己的开始/结束块中。

推荐阅读