Bdtjtk

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.

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;

/

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.