从java中的plsql函数获取返回的记录类型 [英] Getting Returned record type from plsql function in java

问题描述

我有一个plsql函数，该函数返回记录类型challan_rec.

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;

我想从Java代码中调用此函数，并在Java中获取该记录类型. 请指导我如何做. 谢谢

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

推荐答案

首先让我们从更正您的代码开始.我可以看到您发布的代码无法编译，并且会引发问题.正确的代码如下:

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;

执行此代码时，您可能会遇到类似invalid datatype的问题.这是因为RECORD仅限于在PLSQL中使用.

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:无效的数据类型

ORA-00902: invalid datatype

执行此操作的最佳方法是先创建对象的OBJECT和TYPE，然后在函数中创建return:

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;

/

执行:

SQL> select get_challan(2265) from dual;
     /

这篇关于从java中的plsql函数获取返回的记录类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！