Error in fetching resultset from oracle PLSQL function in Spring JAVA

Question

I'm new to spring boot development and java.

I'm here trying to call the Oracle PLSQL function from spring boot with the help of simpleJdbcCall.executeFunction method. My plsql function is returning the single table row. In java i'm trying to store the result/output of function in Map data type, but whenever i'm trying to run the application i'm getting error as mentioned below

PLSQL function:

CREATE TABLE test.account1 (
 account_id INT,
 name       VARCHAR2(20)
);

INSERT INTO test.account1 VALUES ( 1, 'Bob' );
INSERT INTO test.account1 VALUES ( 2, 'Nob' );

create or replace function test.get_accounts
(Acc_id in Account1.account_id%Type)
return account1%rowtype
as
l_cust_record account1%rowtype;
begin
select * into l_cust_record from account1
where account_id=Acc_id;
return(l_cust_record);
end;
/

Java method:

@SuppressWarnings("unchecked")
public void testFunctionTablerow(){
    this.jdbcTemplate =new JdbcTemplate(datasource);
    System.out.println("1");
    simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName("get_accounts");
    System.out.println("2");
    SqlParameterSource in= new MapSqlParameterSource().addValue("Acc_id", 1);
    System.out.println("3");
    //Map<String, Object> out = simpleJdbcCall.executeFunction(Map.class, in);
    Map<String, Object> out= simpleJdbcCall.executeFunction(Map.class, in);
    System.out.println(out);

}

error:

2018-07-12 12:18:34.620  INFO 1560 --- [nio-8000-exec-2] o.s.b.f.xml.XmlBeanDefinitionReader      : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2018-07-12 12:18:34.669  INFO 1560 --- [nio-8000-exec-2] o.s.jdbc.support.SQLErrorCodesFactory    : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]

Can you please help me and let me know what is the correct method to store the value of plsql function ouput in java whenever function is returning entire row. Please share if you know any other method to call such type of function which returns complex output.

Thank you

Your "error" is two lines of INFO log that don't have anything to do with any errors. — Kayaman
– Kayaman, Commented Jul 12, 2018 at 7:14

Andrei Prakhov · Accepted Answer · 2018-07-12 07:22:06Z

2

you can try to call returningResultSet method as described here:

Calling Oracle procedure that returns rows using SimpleJdbcCall in Spring

answered Jul 12, 2018 at 7:22

Andrei Prakhov

2061 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Shaikh Mudassir Over a year ago

Thank you Andrei for your prompt response, i'll try this approach and update the status here.

Shaikh Mudassir Over a year ago

Hi Andrei, returningResultSet approach is not working for me. i tried but i'm getting same issue.

Andrei Prakhov Over a year ago

Could you post the error if you are getting one? In the example above there is no error output

Shaikh Mudassir Over a year ago

Yes, Andrei . I'm getting only the the lines which i have mentioned under error: in my question. No output i'm getting

Andrei Prakhov Over a year ago

Then I suspect you don't call the code that executes the function. I have created the same example and I get PL SQL error in log on function call. What calls testFunctionTablerow() method in your code?

|

Collectives™ on Stack Overflow

Error in fetching resultset from oracle PLSQL function in Spring JAVA

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related