CODEWITHSUNDEEP
oracle-databaseuser-defined-functionsdistributed-transactions
Rushikesh Yadav
Rushikesh Yadav

Reputation: 1

Error while using stored procedure in distributed transaction

I am not good at databases and SQL and have not worked on complex tasks. I will try to explain as best as I can. Thanks for your help.

We are performing proof-of-concept (POC) and we are facing some issue in distributed transaction. Following are details. We are using Oracle database and the application is Spring Boot application and this is how we are connected to it.

datasource:
  xa:
    properties:
      driver-class-name: oracle.jdbc.OracleDriver
      data-source-class-name: oracle.jdbc.xa.client.OracleXADataSource
  hikari:
    minimumIdle: 5
    maximum-pool-size: 30
    idleTimeout: 30000
    maxLifetime: 2000000
    connectionTimeout: 30000
    poolName: <poolName>
  url: <url>
  username: <username>
  password: <password>

We have defined some user-defined functions (UDF) these use external libraries (3rd party tokenization service provider), and there are some triggers we have created on INSERT. From application when we are inserting into table using a stored procedure like as shown below, we are getting an error "unimplemented feature":

--we call these udfs
CREATE OR REPLACE PACKAGE dbg AUTHID CURRENT_USER
AS
FUNCTION dbg_protect_id(inval IN VARCHAR2)
        RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE;

FUNCTION dbg_unprotect_id(inval IN VARCHAR2)
        RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE;
END;


CREATE OR REPLACE PACKAGE BODY dbg AS

FUNCTION dbg_unprotect_id(inval IN VARCHAR2)
        RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
IS
        e_NoAccess EXCEPTION;
        retval     VARCHAR2(80);
BEGIN
        IF inval IN ('NA', 'UNK') THEN
                retval := inval;
        ELSE
                xpSel('DBG_MEMBERIDENTIFIERS_UPPERALPHANUM_LP', retval, inval, oraCommunicationId, SCID);
        END IF;
        RETURN retval;
END;

FUNCTION dbg_protect_id(inval IN VARCHAR2)
        RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
IS
        retval VARCHAR2(80);
BEGIN
        IF inval IN ('NA', 'UNK') THEN
                retval := inval;
        ELSE
                xpIns('DBG_MEMBERIDENTIFIERS_UPPERALPHANUM_LP', inval, oraCommunicationId, retval, SCID);
        END IF;
        RETURN retval;
END;

PROCEDURE xpIns( dataelement     IN CHAR,
                 cdata           IN VARCHAR,
                 communicationId IN BINARY_INTEGER,
                 rdata OUT VARCHAR,
                 SCID IN BINARY_INTEGER )
AS
        EXTERNAL NAME "ptyOraInsertTPE" LIBRARY protegrity WITH CONTEXT PARAMETERS
        ( CONTEXT,dataelement,cdata INDICATOR,cdata LENGTH,cdata,communicationId,
        rdata INDICATOR,rdata LENGTH,rdata MAXLEN,rdata,SCID);

PROCEDURE xpSel( dataelement IN CHAR,
                 cdata OUT      VARCHAR,
                 rdata           IN       VARCHAR,
                 communicationId IN BINARY_INTEGER,
                 SCID            IN BINARY_INTEGER )
AS
        EXTERNAL NAME "ptyOraSelectTPE" LIBRARY protegrity WITH CONTEXT PARAMETERS( 
        CONTEXT,dataelement,cdata INDICATOR,cdata LENGTH,cdata MAXLEN,cdata,
        rdata INDICATOR,rdata LENGTH,rdata,communicationId,SCID );
END dbg;


PROCEDURE create_order ( p_subscribernum IN VARCHAR2 )
AS
BEGIN
        INSERT INTO
                cr_member_primary
                (
                        primary_first_nm ,
                        primary_middle_nm,
                        primary_last_nm
                )
        SELECT
                first_nm ,
                middle_nm,
                last_nm
        FROM
                member_new
        WHERE
                subscr_num = dbg.dbg_protect_id(p_subscribernum);

END create_order;

Following is the error stack trace:

CallableStatementCallback; uncategorized SQLException for SQL [{call LEAP_BHP.PKG_MANAGE_ORDER.create_order(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [0A000]; error code [3001]; ORA-03001: unimplemented feature
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 1266
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-03001: unimplemented feature
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 1266
ORA-06512: at line 1
","error.stack_trace":"org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call LEAP_BHP.PKG_MANAGE_ORDER.create_order(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [0A000]; error code [3001]; ORA-03001: unimplemented feature
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 1266
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-03001: unimplemented feature
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 1266
ORA-06512: at line 1

               at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1578)
               at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1211)
               at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1250)
               at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:140)
               at com.beaconlbs.common.dao.storedprocedure.SearchForRecordsStoredProcedure.executeProcedureWithNonCursorOut(SearchForRecordsStoredProcedure.java:206)
               at com.beaconlbs.common.dao.storedprocedure.SearchForRecordsStoredProcedure.executeProcedureWithNonCursorOutParamWithSchema(SearchForRecordsStoredProcedure.java:220)
               at com.beaconlbs.order.dao.impl.OrderPerPayorDaoImpl.createOrder(OrderPerPayorDaoImpl.java:1174)
               at com.beaconlbs.order.business.impl.OrderBusinessImpl.createOrder(OrderBusinessImpl.java:236)
               at com.beaconlbs.order.business.impl.OrderBusinessImpl$$FastClassBySpringCGLIB$$ddabf5ac.invoke(<generated>)
               ... 161 more
Caused by: java.sql.SQLException: ORA-03001: unimplemented feature
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 1266
ORA-06512: at line 1

               at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
               at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
               at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
               at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
               at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
               at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
               at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:265)
               at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:86)
               at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
               at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
               at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
               at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358)
               at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
               at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251)
               at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
               at org.springframework.jdbc.core.JdbcTemplate.lambda$call$6(JdbcTemplate.java:1251)
               at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1192)
               ... 106 more
Caused by: Error : 3001, Position : 0, Sql = BEGIN LEAP_BHP.PKG_MANAGE_ORDER.create_order(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 ); END;, OriginalSql = {call LEAP_BHP.PKG_MANAGE_ORDER.create_order(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}, Error Msg = ORA-03001: unimplemented feature
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 1266
ORA-06512: at line 1

               at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
               ... 122 more

Workarounds we tried:

  1. Create a queue and scheduler: data which we want to insert we push that to the queue and scheduler inserts it, but the data will not get reflected in the database until scheduler runs (works, but not preferred at all because of delay in inserting data).

  2. Created a trigger which call a stored procedure. We tried using pragma autonomous transactions inside stored procedure which works most of the time but, sometimes throws "Distributed autonomous transaction disallowed within migratable distributed".

    This is the code:

    CREATE OR REPLACE TRIGGER trg_cr_member_primary_insert AFTER
INSERT
ON
        leap_bhp.cr_member FOR EACH ROW BEGIN sp_insert_cr_member_primary
        (
                :NEW.member_subscr_num
        );
END;

CREATE OR REPLACE PROCEDURE sp_insert_cr_member_primary (p_member_subscr_num VARCHAR2)
IS
        v_detokenized_subscr_num VARCHAR2(100);
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        v_detokenized_subscr_num := dbg.dbg_unprotect_id(p_member_subscr_num);
        SELECT
                first_nm ,
                middle_nm,
                last_nm
        FROM
                member_new
        WHERE
                subscr_num = v_detokenized_subscr_num;

        COMMIT;
EXCEPTION
WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END sp_insert_cr_member_w;

    following is the error stack trace:

    CallableStatementCallback; uncategorized SQLException for SQL [{call LEAP_BHP.PKG_MANAGE_ORDER.MANAGE_COMPLIANCE_REQUEST(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [99999]; error code [164]; ORA-00164: distributed autonomous transaction disallowed within migratable distributed transaction
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 20
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 7
ORA-06512: at "POCUSER.TRG_COMPLIANCE_REQUEST_INSERT", line 3
ORA-04088: error during execution of trigger 'POCUSER.TRG_COMPLIANCE_REQUEST_INSERT'
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 353
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-00164: distributed autonomous transaction disallowed within migratable distributed transaction
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 20
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 7
ORA-06512: at "POCUSER.TRG_COMPLIANCE_REQUEST_INSERT", line 3
ORA-04088: error during execution of trigger 'POCUSER.TRG_COMPLIANCE_REQUEST_INSERT'
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 353
ORA-06512: at line 1
","error.stack_trace":"org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call LEAP_BHP.PKG_MANAGE_ORDER.MANAGE_COMPLIANCE_REQUEST(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [99999]; error code [164]; ORA-00164: distributed autonomous transaction disallowed within migratable distributed transaction
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 20
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 7
ORA-06512: at "POCUSER.TRG_COMPLIANCE_REQUEST_INSERT", line 3
ORA-04088: error during execution of trigger 'POCUSER.TRG_COMPLIANCE_REQUEST_INSERT'
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 353
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-00164: distributed autonomous transaction disallowed within migratable distributed transaction
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 20
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 7
ORA-06512: at "POCUSER.TRG_COMPLIANCE_REQUEST_INSERT", line 3
ORA-04088: error during execution of trigger 'POCUSER.TRG_COMPLIANCE_REQUEST_INSERT'
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 353
ORA-06512: at line 1

               at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1578)
               at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1211)
               at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1250)
               at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:140)
               at com.beaconlbs.common.dao.storedprocedure.SearchForRecordsStoredProcedure.execute(SearchForRecordsStoredProcedure.java:170)
               at com.beaconlbs.common.dao.storedprocedure.SearchForRecordsStoredProcedure.executeProcedureWithSchema(SearchForRecordsStoredProcedure.java:125)
               at com.beaconlbs.order.dao.impl.OrderPerPayorDaoImpl.saveComplianceRequest(OrderPerPayorDaoImpl.java:197)
               at com.beaconlbs.order.business.impl.OrderBusinessImpl.saveComplianceRequest(OrderBusinessImpl.java:495)
               at com.beaconlbs.order.business.impl.OrderBusinessImpl$$FastClassBySpringCGLIB$$ddabf5ac.invoke(<generated>)
               at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
               at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:792)
               at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
               at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
               at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
               at com.beaconlbs.common.aop.aspects.performance.AbstractPerformanceMonitoringAspect.monitor(AbstractPerformanceMonitoringAspect.java:86)
               at jdk.internal.reflect.GeneratedMethodAccessor34.invoke(Unknown Source)
               at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
               at java.base/java.lang.reflect.Method.invoke(Method.java:566)
               at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634)
               at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624)
               at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72)
               at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
               at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
               at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:58)
               at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
               at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
               at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
               at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
               at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
               at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
               at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
               at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
               at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
               at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
               at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:707)
               at com.beaconlbs.order.business.impl.OrderBusinessImpl$$EnhancerBySpringCGLIB$$15b8dd9e.saveComplianceRequest(<generated>)
               at com.beaconlbs.order.service.impl.OrderServiceImpl.saveComplianceRequest(OrderServiceImpl.java:333)
               at com.beaconlbs.order.service.impl.OrderServiceImpl$$FastClassBySpringCGLIB$$ef08853a.invoke(<generated>)
            ... 189 more
Caused by: java.sql.SQLException: ORA-00164: distributed autonomous transaction disallowed within migratable distributed transaction
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 20
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 7
ORA-06512: at "POCUSER.TRG_COMPLIANCE_REQUEST_INSERT", line 3
ORA-04088: error during execution of trigger 'POCUSER.TRG_COMPLIANCE_REQUEST_INSERT'
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 353
ORA-06512: at line 1

               at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
               at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
               at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
               at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
               at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
               at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
               at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:265)
               at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:86)
               at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
               at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
               at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
               at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358)
               at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
               at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251)
               at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
               at org.springframework.jdbc.core.JdbcTemplate.lambda$call$6(JdbcTemplate.java:1251)
               at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1192)
               ... 109 more
Caused by: Error : 164, Position : 0, Sql = BEGIN LEAP_BHP.PKG_MANAGE_ORDER.MANAGE_COMPLIANCE_REQUEST(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 ); END;, OriginalSql = {call LEAP_BHP.PKG_MANAGE_ORDER.MANAGE_COMPLIANCE_REQUEST(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}, Error Msg = ORA-00164: distributed autonomous transaction disallowed within migratable distributed transaction
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 20
ORA-06512: at "POCUSER.DBG", line 49
ORA-06512: at "POCUSER.DBG", line 166
ORA-06512: at "POCUSER.SP_INSERT_COMPLIANCE_REQUEST_W", line 7
ORA-06512: at "POCUSER.TRG_COMPLIANCE_REQUEST_INSERT", line 3
ORA-04088: error during execution of trigger 'POCUSER.TRG_COMPLIANCE_REQUEST_INSERT'
ORA-06512: at "LEAP_BHP.PKG_MANAGE_ORDER", line 353
ORA-06512: at line 1

               at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
               ... 125 more

What we are trying to find out is a solution which lets use use UDFs we have defined while distributed transactions are enabled and insert data in new table in real time.

I know autonomous transactions are not good we just need to show that it works

We cannot disable distributed transaction.

We cannot perform tokenization in application layer.

Upvotes: 0

Views: 57

Answers (0)

Related Questions