This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2022-09-30
Channels
- # announcements (31)
- # aws (17)
- # babashka (26)
- # babashka-sci-dev (8)
- # beginners (16)
- # biff (1)
- # calva (9)
- # cider (5)
- # clj-kondo (3)
- # clj-on-windows (38)
- # cljdoc (2)
- # cljs-dev (9)
- # cljsrn (6)
- # clojure (58)
- # clojure-europe (47)
- # clojure-nl (3)
- # clojure-norway (21)
- # clojure-uk (2)
- # clojurescript (25)
- # conjure (2)
- # data-science (7)
- # datomic (3)
- # emacs (12)
- # events (5)
- # fulcro (5)
- # honeysql (10)
- # introduce-yourself (7)
- # lsp (4)
- # meander (3)
- # nbb (18)
- # off-topic (28)
- # rdf (1)
- # releases (2)
- # sci (5)
- # shadow-cljs (23)
- # sql (5)
- # test-check (3)
When I try execute a stmt using next.jdbc, 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
✅ 1
I just find 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
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.
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;
/"))))
You should still be able to call jdbc/execute!
on a Statement
-- you don't need to drop down to interop there.
👍 1