Oracle ORA-00900 Error in SQL Publish Action

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

There is a DataPool that publishes data in a Oracle Repository table (GTREP.XXXXX). It includes 2 actions (pre and post SQL type), that execute Oracle stored procedures:

 

Pre Publish Direct SQL - BEGIN PACKAGENAME.P_TRUNCATE; END;
Post Publish Direct SQL -  BEGIN PACKAGENAME.P_INSERT(  p_arg1  =>  'Value1', p_arg2  =>  'Vlaue2', p_arg3 =>  'Value3’ ); END;

From Datamaker the Publish works perfectly fine including execution of Pre and Post actions.

From TDM Portal the publish works without errors, however but both actions aren’t executed and in log file there are below errors about them:

Pre Publish Action Error:

2021-12-20 13:35:44.325 UTC [INFO ] [pool-103-thread-1        ] --- [U:][M:][P:]                           c.c.t.p.l.TDMPublishLog:  Processing query: PACKAGENAME.P_TRUNCATE

2021-12-20 13:35:44.338 UTC [WARN ] [pool-103-thread-1        ] --- [U:][M:][P:]                   c.c.t.s.p.PublishActionExecutor:  SQL publish action XXXXXX failed: StatementCallback; bad SQL grammar [PACKAGENAME.P_TRUNCATE]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

oracle.jdbc.OracleDatabaseException: ORA-00900: invalid SQL statement

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

               ... 27 common frames omitted

Wrapped by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

 

Pre Publish Action Error:

2021-12-20 13:35:44.464 UTC [INFO ] [pool-104-thread-1        ] --- [U:][M:][P:]                           c.c.t.p.l.TDMPublishLog:  Processing query: PACKAGENAME.P_INSERT(  p_arg1  =>  'Value1', p_arg2  =>  'Vlaue2', p_arg3 =>  'Value3’ );

2021-12-20 13:35:44.469 UTC [WARN ] [pool-104-thread-1        ] --- [U:][M:][P:]                   c.c.t.s.p.PublishActionExecutor:  SQL publish action 02 - CALL MAIN_TRX failed: StatementCallback; bad SQL grammar [PACKAGENAME.P_INSERT(  p_arg1  =>  'Value1', p_arg2  =>  'Vlaue2', p_arg3 =>  'Value3’ );]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

oracle.jdbc.OracleDatabaseException: ORA-00900: invalid SQL statement

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

               ... 27 common frames omitted

Wrapped by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Environment

Release : 4.9

Component :

Cause

The issue is the with way the stored procedure is being called in the Direct SQL of publish actions.

Resolution

First the statements needs to be formatted in such a way that everything is in single line.

Then though the calling statements work from SQLDeveloper, we need to use the statements as mentioned below:

Pre Publish Action: Direct SQL -

CALL PACKAGENAME.P_TRUNCATE();

Post Publish Action: Direct SQL (In one single line) -

CALL PACKAGENAME.P_INSERT(  p_arg1  =>  'Value1', p_arg2  =>  'Vlaue2', p_arg3 =>  'Value3’ );