Create trigger Oracle Missing IN or OUT

[blindcoding9]

When I try execute a stmt Im facing SQLException Missing IN or OUT parameter at index:: 1

(jdbc/execute! @datasource ["CREATE TABLE T_TEMP AS SELECT sysdate AS d FROM dual"])
=> [#:next.jdbc{:update-count 1}]

(jdbc/execute! @datasource ["CREATE SEQUENCE SEQ_TEMP"])
=> [#:next.jdbc{:update-count 0}]

(jdbc/execute! @datasource ["CREATE OR REPLACE EDITIONABLE TRIGGER T_TEMP_TRIGGER
BEFORE INSERT ON T_TEMP
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
BEGIN
    IF inserting THEN
        IF :new.id IS NULL THEN
            SELECT SEQ_TEMP.nextval
              INTO :new.id
              FROM dual;
        END IF;
    END IF;
END;"])
Execution error (SQLException) at oracle.jdbc.driver.OraclePreparedStatement/processCompletedBindRow (OraclePreparedStatement.java:2017).
Missing IN or OUT parameter at index:: 1 

[seancorfield]

I don't use Oracle and don't have access to it to test anything -- the error is coming from the underlying JDBC driver (not next.jdbc) and I would expect such an error to occur on the call to a proc, not the definition.

One thing you might try is to remove the ; at the end of the statement in case Oracle's driver thinks that's an additional statement and/or some kind of proc invocation.

Other than that, I have no idea (and I cannot provide support for Oracle-related issues -- you'll need to ask someone who uses Oracle, perhaps in the #sql channel on the Clojurians Slack -- you can sign up at http://clojurians.net ).

[blindcoding9]

I found the solution

When using a 10g (or higher) Oracle JDBC driver, attempts to create a trigger fail when the syntax for doing so contains :OLD (or :NEW) types of references. The following exception is thrown:

Missing IN or OUT parameter at index:: 1

CAUSE

This is expected behavior when using a PreparedStatement or a CallableStatement.

Beginning in the 10g drivers, a feature was added that allows binding parameters by name.

As a consequence of this, strings such as ":old" are interpreted by the JDBC driver as bind variables.

Both PreparedStatement and CallableStatement check in advance that values have been provided for all bind variables.

With no value provided for ":old", since it's not intended in this context to be a bind variable, the error is thrown.

Solution

Use Statement rather than PreparedStatement or CallableStatement.

(with-open [con (jdbc/get-connection @datasource)]
    (let [stmt (prepare/statement con)]
      (.executeUpdate stmt (str "CREATE OR REPLACE EDITIONABLE TRIGGER T_TEMP_TRIGGER
BEFORE INSERT ON T_TEMP
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
BEGIN
    IF inserting THEN
        IF :new.id IS NULL THEN
            SELECT SEQ_TEMP.nextval
              INTO :new.id
              FROM dual;
        END IF;
    END IF;
END;
/"))))
=> 0

[seancorfield]

I'll reopen this and add a Tips & Tricks note about it. Could you confirm you can use jdbc/execute! on stmt -- no need for interop there?

[seancorfield]

Documentation updated on develop.

Don't forget to call .close on your Statement or use with-open when setting it up:

(with-open [con (jdbc/get-connection @datasource)
            stmt (prepare/statement con)]
      (jdbc/execute! stmt ["CREATE OR REPLACE EDITIONABLE TRIGGER ..."]))