Next.jdbc execute stored procedure

Hi, I am wanting to run a stored procedure via next.jdbc.execute.
I can run the following SQL directly in SQL Developer:

EXECUTE TEST_USER.COPY_TABLES;

However, when I try to run the following from code, I get an error:

(jdbc/execute! ds ["EXECUTE TEST_USER.COPY_TABLES"])
Execution error (OracleDatabaseException) at oracle.jdbc.driver.T4CTTIoer11/processError 
(T4CTTIoer11.java:513).
ORA-00900: invalid SQL statement

Am I missing another way to execute this procedure using Next JDBC?

This error is coming from the database itself. According to ORA-00900 to ORA-01499 it says:

ORA-00900 invalid SQL statement

Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.

Action: Correct the syntax or install the Procedural Option.

Some further searching suggests that maybe CALL TEST_USER.COPY_TABLES might work with JDBC but that might require using a CallableStatement which next.jdbc does not support directly, but you could create and execute one via Java interop.

The Oracle JDBC drivers are… interesting… to work with because they don’t implement certain things that other drivers support, and they often require non-standard approaches to certain problems. I don’t test next.jdbc against Oracle because I haven’t yet found a way to do it for free via Docker (which is how I test against several other databases, including Microsoft SQL Server) so I have to rely on Oracle users in the community to do both the testing and debugging of any problems they encounter.

2 Likes

Hi Sean,

Thanks for the prompt response.
Can confirm that using the below resolves the issue.

(jdbc/execute! ds ["CALL TEST_USER.COPY_TABLES()"])

Many thanks

1 Like