mardi 16 juillet 2019

How to mock Oracle Package Procedure in H2 for Testing

I am testing an spring boot application that reads/writes data to an Oracle DB. This Oracle DB has Oracle packages and in those packages procedures. At some point, the spring boot application calls this procedure via a Entity Repository as follows

@Repository
public interface StudentRepository extends JpaRepository<Student, String> {

@Modifying
@Query(value = "begin sch1.STUDENT_PACKAGE.Set_Grades_To_A('A'); end;", nativeQuery = true)
public void setStudentGradeToA();
}

So, it uses a native query to make the call to to a procedure Set_GradesToA in the STUDENT_PACKAGE package of the sch1 schema.

I am currently testing the functionality of the Spring Boot application and NOT the integration between it and the Oracle database. Therefore, I have decided to use an in-memory database (H2) (with the Oracle compatibility option) to replace the Oracle DB for now. BUT how can I fake out these java package procedures?

I have tried creating an alias in my schema.sql (or data.sql) as follows:

CREATE SCHEMA if not exists sch1;
CREATE ALIAS sch1.STUDENT_PACKAGE AS $$ void Set_Grades_To_A(String s) { new String(s); } $$;

I really don't care what is inside the Set_Grades_To_A procedure what I care about is how to define it.

When I create the alias as above, I'm still getting a Syntax Error.

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "BEGIN SCH1[*].STUDENT_PACKAGE.Set_Grades_To_A('A'); END; "; SQL statement:
begin sch1.STUDENT_PACKAGE.Set_Grades_To_A('A'); end; [42000-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.message.DbException.getSyntaxError(DbException.java:203)

I guess I have two questions:

  1. How can I fake out a stored procedure inside an Oracle package in the schema sch1?

  2. Why am I getting the Syntax Error above?

Aucun commentaire:

Enregistrer un commentaire