How to Execute PL/SQL Procedure with Typed Input Param from DBTransactionImpl

Question

Oracle ADF/JDeveloper 11.1.1.7. Oracle DB.

I am trying to call a stored procedure written as:

create or replace 
PROCEDURE SP_ADD_AUDIT_COMMENT_BY_RECID (ct_comments IN TYPE_AUDIT_COMMENT)
    AS
            MISSING_REQUIRED_INPUT EXCEPTION;
        BEGIN  
            IF 
                (ct_comments.CUSTOM_APP_RECORD_ID  IS NULL) OR
                (ct_comments.CUSTOM_SOURCE  IS NULL) OR
                (ct_comments.UPDATEDBYDISPLAYNAME IS NULL) OR
                (ct_comments.COMMENTDATE IS NULL) OR
                (ct_comments.WFCOMMENT IS NULL)
            THEN
                RAISE MISSING_REQUIRED_INPUT;
            ELSE
                INSERT All
                    INTO AUDIT_RECORD_INSTANCE (CUSTOM_APP_RECORD_ID, CUSTOM_SOURCE, INSTANCEID, COMPOSITEINSTANCEID) VALUES (ct_comments.CUSTOM_APP_RECORD_ID, ct_comments.CUSTOM_SOURCE, ct_comments.INSTANCEID, ct_comments.COMPOSITEINSTANCEID)
                    INTO AUDIT_WFCOMMENTS (CUSTOM_APP_RECORD_ID, CUSTOM_SOURCE, TASKID, UPDATEDBY, UPDATEDBYDISPLAYNAME, COMMENTDATE, WFCOMMENT, SCOPE ) VALUES (ct_comments.CUSTOM_APP_RECORD_ID, ct_comments.CUSTOM_SOURCE, ct_comments.TASKID, ct_comments.UPDATEDBY, ct_comments.UPDATEDBYDISPLAYNAME, ct_comments.COMMENTDATE, ct_comments.WFCOMMENT, ct_comments.SCOPE)
                SELECT * FROM dual;            
            END IF;
            EXCEPTION
                WHEN MISSING_REQUIRED_INPUT THEN
                --DBMS_OUTPUT.PUT_LINE ('One or more required input values are missing.');
                raise_application_error (-20001,'One or more required input values are missing.');
        END;

This works fine from SQL Worksheet using:

DECLARE pComment TYPE_AUDIT_COMMENT;
    BEGIN
                    pComment := TYPE_AUDIT_COMMENT('123','SOURCE','1','2','3','jSmith','Joe Smith',
                    to_timestamp('06-APR-15 02.25.58.187000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'TEST COMMENT SP','SCOPE');
                    SCHEMANAME.SP_ADD_AUDIT_COMMENT_BY_RECID(pComment);

           END;

In AppModule, a method gets the params and tries to execute the procedure as so:

public void insertNewGenericComment(Comments comments){
         String input =
            "pComment := TYPE_AUDIT_COMMENT('" + comments.getAssessmentId() + "', '"
            + comments.getCustomSource() + "', '"
            + comments.getInstanceid() + "', '"
            + comments.getTaskId() + "', '"
            + comments.getCompositeInstanceId() + "', '"
            + comments.getUpdatedBy() + "', '"
            + comments.getUpdatedbyDisplayName() + "', to_timestamp('"
            + comments.getCommentDate() + "','DD-MON-RR HH.MI.SS.FF AM'), '"
            + comments.getComment() + "', '"
            + comments.getScope() + "'); SCHEMANAME.SP_ADD_AUDIT_COMMENT_BY_RECID(pComment);";

        DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
        dbti.executeCommand("DECLARE pComment TYPE_AUDIT_COMMENT; begin " + input + " end;");

I've tried many variations but why can't I just execute the sql as works in Worksheet?

The JBO error with above is:

There was an error adding a new comment: oracle.jbo.SQLStmtException: JBO-27121: SQL error during statement execution. Statement: DECLARE pComment TYPE_AUDIT_COMMENT; begin pComment := TYPE_AUDIT_COMMENT('210', 'AssignCustomTaskflow', 'NA', 'NA', 'NA', 'anonymous', 'anonymous', to_timestamp('16-Jun-15 02.58.20.000000502 PM','DD-MON-RR HH.MI.SS.FF AM'), ' test', 'NA'); SCHEMANAME.SP_ADD_AUDIT_COMMENT_BY_RECID(pComment); end;

And the statement from the error message, pasted into SQL Worksheet executes fine as well. Is there no way to pass a verbatim procedure call from ADF?

Answer 1

Here is an example that shows you how to call database procedures with custom types.

Callable Statements with custom database type adf