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

Question

I have created a java stored procedure in Oracle, using query:

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)';
 /

Following is my java code. (Input I_sheetnode is of json type. Since there is no Jsontype datatype in plsql, I used clob there and so, I used the same here)

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 */

When I am trying to run the procedure like this,

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');
/

I am getting the following output:

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.

I am unable to figure out the reason for this. Since "Connection successful" is being printed, is the connection really successful? or not? If not, why?

Note: I have already loaded java class using loadjava utility.

we can't see the "Remaining code goes here", the problem is probably there when you try to get same data or call something from the database. — OracleDev, Jan 04 '19 at 11:04
You haven't shown us the code that's actually throwing the error - include the whole of the Java procedure, so we can at least see what line 25 is and the query you're executing within it. The error suggests you have a query with a stray `@`, possibly from incorrectly constructing the query string or not setting a variable up properly, but as we can't see your code we can only make vague guesses. — Alex Poole, Jan 04 '19 at 11:04

Alex Poole · Answer 1 · 2019-01-07T20:58:48.667

2

If you add a basic debugging print of your generated statement, e.g.:

System.out.println(sheetRows);

you'll see something like:

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

The toString() method shows you the object ID, not the string contents. And the @ in that ID is causing the error you see (as 77556fd or whatever value you see isn't a valid object identifier).

You could embed the actual passed-in string value, but you'd have to enclose it in single quotes, and you'd be restricted by the size of a string literal in your DB (either 4k or 32k depending on version and settings) which makes using a CLOB in the first place pointless; and you should be using bind variables anyway, something like:

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".

I was originally testing outside the DB, but did see this replicating your setup more fully. The only way I've avoided it so far is to avoid an implicit temporary 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;
/

edited Jan 07 '19 at 20:58

answered Jan 07 '19 at 10:23

Alex Poole

183,384
11
179
318

Nice explanation! :) but now I am getting a "java.sql.SQLException: ORA-22922: nonexistent LOB value". I have tried using to_clob in the call (execute GETSHEETROWS(14,to_clob('{"name":"sheet"........')), but no luck. – Ravi Shankar Jan 07 '19 at 18:47
Your original test call will do an implicit conversion from your varchar2 string literal to a CLOB anyway. Is that error coming setClob, executeQuery or your test call? – Alex Poole Jan 07 '19 at 18:52
it comes during test call – Ravi Shankar Jan 07 '19 at 19:06
That seems to be because it's a temporary LOB, and I think it's being released by the PL/SQL side while the Java side is still trying to use it, which is awkward. I was able to avoid it only by creating a table with that value and then using that as part of the call. Not entirely sure why, but hopefully your real values will come from a table anyway. – Alex Poole Jan 07 '19 at 20:51
Of course, all this assumes you have a legitimate reason for dropping into Java to do this in the first place... *8-) – Alex Poole Jan 07 '19 at 20:59
instead of creating a new table, can I do : select (*json data*) into L_clob from dual; – Ravi Shankar Jan 11 '19 at 09:30
@RaviShankar - I only created `t` as a place to hold the original CLOB so it was a permanent object, to avoid that error. For a standalone test of your function it may be necessary. But where will the real JSON data/CLOB come from when you ue this for real? From a table that already exists? – Alex Poole Jan 11 '19 at 09:35

score 0 · Answer 2 · answered Jan 04 '19 at 11:11

0

The connection is not successful.

The "connection successful" message will be printed in all cases. Even if getConnection returns null or error, System.out.print will still print "connection successful".

You might want to add an if statement or an try catch statement to check for the existence of connection instead of adding an System.out.print statement which dosen't check for the existence of the connection and will just print "connection successful"

PS:Check your url and/or the jars you are using.

answered Jan 04 '19 at 11:11

Pavan

24
1
6

1

I don't think that's true. If the connection is not successful then an SQLException will probably have been thrown, so it wouldn't reach the next line? Plus, the error being thrown is coming from the DB so it must be connected at that point, when it is executing the query (which we can't see...) – Alex Poole Jan 04 '19 at 11:12

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

2 Answers2