java.sql.SQLSyntaxErrorException : ORA-01729: database link name expected while using Java stored procedure

Question

我在 Oracle 中创建了一个 java 存储过程，使用查询:

CREATE OR REPLACE PROCEDURE GETSHEETROWS(I_file_id number, I_sheetNode clob,template_key  varchar2 ,wksht_key  varchar2 ,wksht_name varchar2 )
 AS LANGUAGE JAVA
 NAME 'SheetRowsJson.getSheetRows(int, java.sql.Clob, java.lang.String, java.lang.String, java.lang.String)';
 /

以下是我的java代码。 (输入I_sheetnode是json类型。由于plsql中没有Jsontype数据类型，所以我在那里使用了clob所以，我在这里使用了相同的)

public static void getSheetRows( int I_file_id, Clob I_sheetNode, String 
template_key, String wksht_key,String wksht_name ) {
    try{
            String url = "jdbc:oracle:thin:@xxxxx:port/yyyyy";
            Connection  conn = DriverManager.getConnection(url,"username","password");
            System.out.println("-------------------Connection Successful--------------------------------");
            String sheetRows = "select X.Node,X.rn from json_table (("+ I_sheetNode.toString() +"),'$.table_row[*]' COLUMNS(rn for ordinality,Node varchar2(4000) FORMAT JSON PATH '$')) X";
            PreparedStatement ps=conn.prepareStatement(sheetRows);
            ResultSet rs = ps.executeQuery();
/* Remaining code goes here */

当我尝试运行这样的程序时，

set serveroutput on;
call dbms_java.set_output(50);
execute GETSHEETROWS(14,'{"name":"sheet","table_row":[{"value":"1","item":"11111","id":"2","value":"1","action":"NEW"},{"value":"2","item":"22222","id":"3","value":"4","action":"NEW"}]}','TEMPLATE','SHEET','Sheet1');
/

我得到以下输出:

Call completed.

-------------------Connection Successful--------------------------------
java.sql.SQLSyntaxErrorException: ORA-01729: database link name expected

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java)
    at SheetRowsJson.getSheetRows(SheetRowsJson.java:25)


PL/SQL procedure successfully completed.

我无法找出其中的原因。既然打印了“连接成功”，那么连接真的成功了吗？或不？如果不是，为什么？

注意:我已经使用 loadjava 实用程序加载了 java 类。

Answer 1

如果您添加生成的语句的基本调试打印，例如:

System.out.println(sheetRows);

你会看到类似的内容:

select X.Node,X.rn from json_table ((oracle.sql.CLOB@77556fd),'$.table_row[*]' COLUMNS(rn for ordinality,Node varchar2(4000) FORMAT JSON PATH '$')) X

toString() 方法显示对象 ID，而不是字符串内容。该 ID 中的 @ 导致了您看到的错误(如 77556fd 或您看到的任何值都不是有效的对象标识符)。

您可以嵌入实际传入的字符串值，但您必须将其括在单引号中，并且您将受到数据库中字符串文字大小的限制(4k 或 32k，具体取决于版本)和设置)，这使得首先使用 CLOB 毫无意义；无论如何，您应该使用绑定(bind)变量，例如:

sheetRows = "select X.Node,X.rn from json_table (?,'$.table_row[*]' COLUMNS(rn for ordinality,Node varchar2(4000) FORMAT JSON PATH '$')) X";
PreparedStatement ps=conn.prepareStatement(sheetRows);
ps.setClob(1, I_sheetNode);
ResultSet rs= ps.executeQuery();

I am getting a "java.sql.SQLException: ORA-22922: nonexistent LOB value".

我最初是在数据库之外进行测试，但确实看到它更完整地复制了您的设置。到目前为止，我避免它的唯一方法是避免隐式临时 CLOB:

create table t (c) as (
  select to_clob('{"name":"sheet","table_row":[{"value":"1","item":"11111","id":"2","value":"1","action":"NEW"},{"value":"2","item":"22222","id":"3","value":"4","action":"NEW"}]}') from dual
);

declare
  l_clob clob;
begin
  select c into l_clob from t;
  getsheetrows(14, l_clob, 'TEMPLATE', 'SHEET', 'Sheet1');
end;
/