ORA-65502: cannot access temporary LOB data

Question

I am working on Java+Spring+Oracle application. I am trying to add a new function on the database that stores NClob in a custom type. I ran into the error

ORA-65502: cannot access temporary LOB data

but I am not sure how to fix my issue.

Please pardon me that I am still new to PL/SQL and it is my first time dealing with NClob.

To begin with, I have a table like this:

CREATE TABLE "NOTIFICATION" (
    "NOTIFICATION_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    ...
    "NOTIFICATION_CONTENT" NCLOB,                -- HTML content of notification
    ...
    "COMMENTS"  NCLOB,
    ...
    CONSTRAINT "PK_NOTIFICATION_ID" PRIMARY KEY ("NOTIFICATION_ID")
);

I have a custom type to read/write encapsulated data:

create or replace TYPE NOTIFICATION_TYP FORCE AS OBJECT (
    "NOTIFICATION_ID" NUMBER,
    ...
    "NOTIFICATION_CONTENT" NCLOB,                -- HTML content of notification
    ...
    "COMMENTS"  NCLOB,
    ...
    constructor function NOTIFICATION_TYP return self as result
) NOT FINAL;

and its body simply as:

create or replace type body NOTIFICATION_TYP is
  constructor function NOTIFICATION_TYP return self as result is
  begin
    return;
  end;
end;

So basically my (simplified)function in the database package is like this:

function create_notification(
         pi_notification       in        NOTIFICATION_TYP
        ,po_notification_id    out       number
        ,po_return             out       return_typ
    )
    return integer as
        v_excep_msg varchar2(200);
    begin
        insert into NOTIFICATION (
            ...
            NOTIFICATION_CONTENT,
            ...
            COMMENTS,
            ...
        ) values (
            ...
            pi_notification.NOTIFICATION_CONTENT,
            ...
            pi_notification.COMMENTS,
            ...
        ) RETURNING NOTIFICATION_ID INTO po_notification_id;
        po_return := utils_pkg.get_success_info;
        return c_success;

    exception
        when OTHERS then
            v_excep_msg  :=  sqlerrm;
            po_return := utils_pkg.get_error_info(SQLCODE, SQLERRM);
            log_error_msg(
                 pi_app_name         =>  c_application_name
                ,pi_error_msg        =>  'Exception in creating notification'
                ,pi_error_details    =>  sqlerrm
            );

    return c_failure;
end create_notification;

So from Java end, I am using StoredProcedure and SQLData to call the function in the package. CreateNotificationFunction.java:

import ...
public class CreateNotificationFunction  extends StoredProcedure{

    public CreateNotificationFunction(JdbcTemplate jdbcTemplate){
         super(jdbcTemplate, DAOConstants.CREATE_NOTIFICATION_FUNCTION);
         setFunction(true);
         declareParameter(new SqlOutParameter(DAOConstants.PO_FUNCTION_RESULT, OracleTypes.VARCHAR));
         declareParameter(new SqlParameter(DAOConstants.PI_NOTIFICATION,OracleTypes.STRUCT,DAOConstants.TYPE_NOTIFICATION));
         declareParameter(new SqlOutParameter(DAOConstants.PO_NOTIFICATION_ID, OracleTypes.NUMBER));
         declareParameter(new SqlOutParameter(DAOConstants.PO_RETURN,OracleTypes.STRUCT,DAOConstants.TYPE_RETURN,  
                new SqlReturnSqlData(SQLDBResult.class)));
         allowsUnusedParameters();
         compile();         
    }
    
    @SuppressWarnings({ "rawtypes" })
    public Map execute(JdbcTemplate jdbcTemplate, SQLNotification sqlNotification) {
      Map<String, Object> params = new HashMap<String, Object>();
        params.put(DAOConstants.PI_NOTIFICATION, sqlNotification);
        return super.execute(params);
    }   
}

SQLNotification.java:

public class SQLNotification implements SQLData {
    private String notificationContent;
    private String comments;
    private static Connection connection;
    ...

    @Override
    public void writeSQL(SQLOutput sqlOutput) throws SQLException {
        ...
        NClob notificationContentNClob = connection.createNClob();
        notificationContentNClob.setString(1, getNotificationContent());
        sqlOutput.writeNClob(notificationContentNClob);
        ...
        NClob commentsNClob = connection.createNClob();
        commentsNClob.setString(1, getComments());
        sqlOutput.writeNClob(commentsNClob);
        ...
    }
}

(Is this the correct way to insert Java String into Oracle's NClob?)

Not sure what went wrong...

I am unsure when this error occurs - Is it when creating the type instance? Is it when calling the function? Or, is it when inserting the data into the table?

ORA-65502: cannot access temporary LOB data

0 Answers0