calling Stored Procedure String via Rest API returns empty instead of valid String

As a back-end developer, I would like to call stored procedure connecting Oracle 9i database to fetch the single output string.

In reality, the result of the output parameter as string, returns empty.

Would you please tell me what to modify the work?

Here is my rest controller:

@GetMapping(value = { "/third"})
public String getVariable (){
   String  result = "" ;

   Connection conn;
   conn = getConnection() ;

   CallableStatement cstmt = null;

   try {
       String SQL = "{call pkg1234.get_pc_lookup_value_second(?,?)}";
       cstmt = conn.prepareCall (SQL);
       cstmt.setString(1, "TES");         
       cstmt.registerOutParameter(2,  oracle.jdbc.OracleTypes.VARCHAR );
       cstmt.execute();

       String dddresult = cstmt.getString(2);
       System.out.println(" Record :"+dddresult);
       result = dddresult; 
    }
   catch (SQLException e) {
       System.out.println(" go this");
       e.printStackTrace();
  }
   return result ;
}

Here is my stored procedure:

PROCEDURE get_pc_lookup_value_second (
    i_lookup_code    IN       VARCHAR2,
    o_lookup_value   OUT      VARCHAR2
)
o_lookup_value := '456';
END get_pc_lookup_value_second;

Try my own library for simplicity:

<dependency>
   <groupId>com.github.buckelieg</groupId>
   <artifactId>db-fn</artifactId>
   <version>0.3.4</version>
</dependency>

And then in code:

try(DB db = new DB("your-connection-string")) {
    String result = db.procedure("{call pkg1234.get_pc_lookup_value_second(?,?)}", P.in("TES"), P.out(JDBCType.VARCHAR)).call(cs -> cs.getString(2)).orElse("Unknown");
}

See more here

You have reported the following as your code:

try(DB db = new DB("jdbc:oracle:thin:username/password@10.8.12.6:1521:dev")) {
    String thisString = db.procedure("{call b_pc_mob_portal_pkg.get_pc_lookup_value_second(?,?)}", 
            P.in("TES" , "i_lookup_code"),
            P.out(JDBCType.LONGNVARCHAR , "o_lookup_value" )).call(cs -> cs.getString(2)).orElse("Unknown");
    System.out.println( "thisString String  :" + thisString );

}

You have reported that this gives:

[Ljava.lang.Object; cannot be cast to [Lbuckelieg.fn.db.P;

You have asked:

How can I check on whether this method need to be revised.

This is because you try to combine named parameters with anonymous ones. Either remove parameter names:

db.procedure("{call b_pc_mob_portal_pkg.get_pc_lookup_value_second(?,?)}", P.in("TES"), P.out(JDBCTtype.VARCHAR))...

or add names to procedure call statement:

db.procedure("{call b_pc_mob_portal_pkg.get_pc_lookup_value_second(:i_lookup_code,:o_lookup_value)}", P.in("i_lookup_code", "TES"), P.out(JDBCType.VARCHAR , "o_lookup_value" ))...

You have also reported that you have tried this code:

try(DB db = new DB("jdbc:oracle:thin:username/password@10.8.12.6:1521:dev")) {
        String thisString = db.procedure("{call b_pc_mob_portal_pkg.get_pc_lookup_value_second(?,?)}", 
                P.in("TES" ),
                P.out(JDBCType.VARCHAR )).call(cs -> cs.getString(2)).orElse("Unknown");
        System.out.println( "thisString String  :" + thisString );
        }

And you have said:

when I do it in this way, it gives:

buckelieg.fn.db.SQLRuntimeException: [registerOutParameter not implemented]

I think the problem is in JDBC driver. Which version do you use? Can it be updated for newer one?

You have said that this is your database:

And also that you have downloaded:

OJDBC7.jar

https://www.oracle.com/database/technologies/jdbc-drivers-12c-downloads.html

Here is the Oracle IDE Version fetched by the Oracle SQL Developer

Very interesting. I will look into this issue.

As a temporal workaround I suggest to do the following:

Rewrite procedure as follows:

CREATE OR REPLACE FUNCTION b_pc_mob_portal_pkg.get_pc_lookup_value_second(IN param1 VARCHAR2 DEFAULT 'TES') 
RETURN VARCHAR2 IS
BEGIN
    RETURN '456';
END b_pc_mob_portal_pkg.get_pc_lookup_value_second;

And then in code:

String value = db.select("SELECT b_pc_mob_portal_pkg.get_pc_lookup_value_second(?) AS output FROM DUAL", "TES").single(rs -> rs.getString("output")).orElse("Unknown");

How about procedure?

Have you tried latest version (0.3.6) of library?

 <dependency>
  <groupId>com.github.buckelieg</groupId>
  <artifactId>db-fn</artifactId>
  <version>0.3.6</version>
</dependency>