我在java中检索匿名PLSQL块的结果时遇到了一些麻烦。
I have some trouble retrieving the result of an anonymous PLSQL block in java.
以下是块:
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;我把那个查询放在一个字符串中:
I put that query in a 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); connmit(); } catch (Exception e) { e.printStackTrace(); throw e; } finally { close(rs, cs); } return contextId; }它没有用,因为我收到这条消息:
It does'nt work, as I get this message :
java.sql.SQLException:ORA-01858:找到一个非数字字符,其中数字是预期的 ORA-06512:第1行
那么如何检索 hv_cnt_id ?
推荐答案这是因为隐式日期转换失败。添加 TO_DATE(),而不是直接将日期字符串分配给日期变量。如果使用 java.sql.Date ,则不需要 TO_DATE()。
It is because the implicit date conversion is failing. Add TO_DATE() instead of directly assigning the date string to a date variable. If java.sql.Date is used, the TO_DATE() is not required.
隐式转换通常取决于会话的 NLS_DATE_FORMAT 。
The implicit conversion usually depends on the session's NLS_DATE_FORMAT.
在你的情况下 in_cnt__date DATE:='& 1'是罪魁祸首。实际上会尝试将& 1 转换为日期..因此抛出异常!
In your case in_cnt__date DATE := '&1' is the culprit. &1 will actually be attempted to convert into a date.. And hence the exception thrown!
public static final String CONTEXT = "DECLARE in_cnt__date DATE := ? ;" + "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; " + "? := hv_cnt_id; "EXCEPTION WHEN OTHERS THEN RAISE ; END;";然后,
cs.setDate(1, (java.sql.Date) Route.datePrf);将设置的日期in_cnt__date ;
最后,要检索 hv_cnt_id 以下内容已添加到您的 PL / SQL 块
"? := hv_cnt_id;"从JDBC中我们得到它,
And from JDBC, we get it like,
cs.setDate(1, (java.sql.Date) Route.datePrf); cs.registerOutParameter(2, Types.NUMBER); cs.execute(); contextId = cs.getInt(2);更多推荐
检索匿名PLSQL块结果
发布评论