我在检索 java 中匿名 PLSQL 块的结果时遇到一些问题。
这是块:
DECLARE
in_cnt_date DATE := '&1';
hv_cnt_id NUMBER := 0;
BEGIN
DBMS_OUTPUT.ENABLE (NULL);
INSERT INTO dt_contexts
(CNT_ID, CNT_CONTEXT, CNT_TYPE, CNT_SOURCE, CNT_COMMENT, CNT_DATE, CNT_DATE_INSERT, CNT_DATE_UPDATE)
VALUES
(0, 'EPE_CONTEXT', 'ROUTE', 'bdd', 'Built from ROUTE', in_cnt_date, SYSDATE, SYSDATE);
SELECT SEQ_DT_CNT_ID.CURRVAL
INTO hv_cnt_id
FROM DUAL;
EXCEPTION WHEN OTHERS THEN RAISE ;
END;
我将该查询放入 String 中:
public static final String CONTEXT = "DECLARE in_cnt__date DATE := '&1'; " +
"hv_cnt_id NUMBER := 0; " +
"BEGIN DBMS_OUTPUT.ENABLE (NULL); " +
"INSERT INTO dt_contexts (CNT_ID, CNT_CONTEXT, CNT_TYPE, CNT_SOURCE, CNT_COMMENT, CNT_DATE, CNT_DATE_INSERT, CNT_DATE_UPDATE) " +
"VALUES (0, 'EPE_CONTEXT', 'ROUTE', 'bdd', 'Built from ROUTE', ?, SYSDATE, SYSDATE); " +
"SELECT SEQ_DT_CNT_ID.CURRVAL INTO hv_cnt_id FROM DUAL; " +
"EXCEPTION WHEN OTHERS THEN RAISE ; END;";
该字符串正确吗?
尝试检索的方法hv_cnt_id
:
public int getContextId(Connection conn) throws Exception {
CallableStatement cs = null;
ResultSet rs = null;
int contextId = 0;
try {
conn.setAutoCommit(false);
cs = conn.prepareCall(CONTEXT);
cs.setDate(1, (java.sql.Date) Route.datePrf);
cs.execute();
contextId = (Integer) cs.getObject(1);
conn.commit();
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
close(rs, cs);
}
return contextId;
}
它不起作用,因为我收到此消息:
java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 1
那么我该如何检索hv_cnt_id
?