JSON String to CLOB object

Question

I am creating new dbms_job, using java code, job is creating successfully but processing it is failing to call job_action. in job_action I am calling procedure to invoke oracle loaded java code. the clob is consist of json string. Seems json format is causing issue in oracle.sql.Clob object.

I am not sure, is there any issue with json to clob to json. why it is failing here ?

java code:

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();

oracle procedure :

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;

oracle logs:

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

If "it is failing to call job_action", how can job_action fail? — Scott Hunter, Feb 20 '19 at 15:49
PLS-00306: wrong number or types of arguments in call to 'ASSIGNBLOCK' ORA-06550: line 1, column 756:, but i am passing the same argument which is coming for job, — 2787184, Feb 20 '19 at 16:17
This seem to be no good: `job_action => 'begin pkgrestassignblock.assignblock ('|| request ...` you simple concatenets the `CLOB` in the `job_action` string - you want to *pass* the CLOB in the procedure... — Marmite Bomber, Feb 20 '19 at 17:31
Thanks Marmite, :) Now how I can pass CLOB to the procedure in this case? — 2787184, Feb 21 '19 at 04:41
As stated [here](https://stackoverflow.com/a/50129571/4808122) if the CLOB is over 32K youI'd have to store the CLOB in an interface table, pass an identifier and read it from the job procedure. — Marmite Bomber, Feb 21 '19 at 08:19

score 0 · Answer 1 · answered Feb 20 '19 at 18:57

Well, I think you could run into a problem since job_action is a VARCHAR2. I'm not sure if it's limited to 4000 or 32767 characters, but either way if your JSON is long it could break this.

However, your main problem is that you aren't quoting your strings. Try changing this line:

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

I also removed the begin..end; since the job wraps the action in its own begin/end block anyway.

JSON String to CLOB object

1 Answers1