CODEWITHSUNDEEP
javaplsql
Usman Riaz
Usman Riaz

Reputation: 3020

Getting Returned record type from plsql function in java

I have a plsql function that returns a record type challan_rec.

create or replace package xx_bal_api as
    type challan_rec is record (
        challan_number varchar2(40),
        challan_amount number
    );


    FUNCTION get_challan(foo number) return challan_rec;
end;



create or replace package body xx_bal_api as

    FUNCTION get_challan(foo number) return challan_rec
    is
    cr challan_rec;
    begin
        cr.challan_number := '00002154215'; 
        cr.challan_amount := 2265;
        return cr;
    end get_challan;
end;

I want to call this function from java code and get that record type in java. Kindly guide me how to do that. Thanks

Upvotes: 0

Views: 2298

Answers (2)

XING
XING

Reputation: 9886

First lets start by correcting your code. I can see that your posted code will not compile and will throw issue. The correct code is as below:

CREATE OR REPLACE PACKAGE xx_bal_api
AS
   TYPE challan_rec IS RECORD
   (
      challan_number   VARCHAR2 (40),
      challan_amount   NUMBER
   );

   TYPE rec IS TABLE OF challan_rec index by pls_integer;

   FUNCTION get_challan (foo NUMBER)
      RETURN rec;
END;
/
CREATE OR REPLACE PACKAGE BODY xx_bal_api
AS
   FUNCTION get_challan (foo NUMBER)
      RETURN rec
   IS
      cr   rec;
   BEGIN
      cr (1).challan_number := '00002154215';
      cr (1).challan_amount := foo;
      RETURN cr;
   END get_challan;
END;

When you execute this code you may face issue like invalid datatype . This is because a RECORD is limited to be used within PLSQL.

select xx_bal_api.get_challan(2265) from dual;

ORA-00902: invalid datatype

The best way to do this is to create a OBJECT and a TYPE of the object and then return it in your function:

CREATE OR REPLACE TYPE challan_rec IS OBJECT
(
   challan_number VARCHAR2 (40),
   challan_amount NUMBER
);
/    
CREATE OR REPLACE Type rec is table of challan_rec;
/ 
 CREATE OR REPLACE FUNCTION get_challan(foo number) 
    return rec
  is  
    cr rec:=rec();

   begin
        cr.extend();
        cr(1):= challan_rec('00002154215',foo);        
        return cr;
   end get_challan;   
end;

/

Execution:

SQL> select get_challan(2265) from dual;
     /

Upvotes: 0

dsp_user
dsp_user

Reputation: 2121

First, you can only call functions returning SQL objects from Java, not PLSQL objects. So change your challan_rec accordingly. The whole process should involve 4 steps given below.

  1. Create an object type at schema level (not within a package). Also, your type should be of type OBJECT, not RECORD.

    CREATE OR REPLACE TYPE Some_schema.chellan_rec IS OBJECT (
challan_number varchar2(40),
challan_amount number
)

2 . Change your function get_chellan to return this object

  1. Implement SQLData interface

    public class Chellan_Rec implements SQLData{

//override readSQL, writeSQL and getSQLTypeName methods
}

  2. Call the PLSQL from Java code

    ResultSet rs=null;
CallableStatement stmt=null;
Chellan_Rec rec = null;
try{

  String sqlQuery = "{call get_chellan(?,?)}";

//map plsql type to Java type
  Map m = conn.getTypeMap();
  m.put("schema_name.chellan_rec", Class.forName("some_java_package.Chellan_Rec"));//this maps the Java class to the Oracle custom type
  conn.setTypeMap(m);

  stmt=conn.prepareCall(sqlQuery);
  stmt.registerOutParameter(1, Types.STRUCT, "chellan_rec");
  stmt.setObject(2, fooNum);
  stmt.execute();    

  rec = (Chellan_Rec)stmt.getObject(1);

}catch(Exception e){
  //log the exception;
}

Upvotes: 1

Related Questions