Call Oracle procedure in Java code

Question

I tried to call this procedure in Java code:

create or replace procedure statistics_function
    ( namein                        in varchar2
    , lob_out                       out nocopy clob )
is
    cursor last_60_cpu_cursor       is
        select 1 as last_60_cpu, sysdate as last_60_event_date
        from dual
    ;
begin
    dbms_lob.createtemporary(lob_loc => lob_out, cache => true, dur => dbms_lob.session);

    for cv in last_60_cpu_cursor loop
        dbms_lob.append(lob_out, to_char(cv.last_60_event_date)||'i'||to_char(cv.last_60_cpu)||chr(10));
    end loop;

    dbms_lob.append(lob_out, 'last_60_cpu'||chr(10)||chr(10));
end statistics_function;

Java code:

public static void main(String Args[]) throws SQLException
{
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@31.187.70.163:1521:xe", "admin", "qwerty");

    CallableStatement cstmt = conn.prepareCall("statistics_function(?, ?); end;");

    cstmt.registerOutParameter(2, Types.VARCHAR);
    cstmt.setString(1, "agent");

    cstmt.execute();
    String result = cstmt.getString(1);
    conn.close();
    System.out.println(result);
}

I get this error:

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Can you help me to fix this problem. I suppose that I have some mistake in Java code?

Can you propose some solution for this problem?

Rahman · Accepted Answer · 2015-12-04 14:02:14Z

1

Your syntax is wrong : Try this:

CallableStatement cstmt = conn.prepareCall("{ call statistics_function(?, ?)}");

answered Dec 4, 2015 at 14:02

Rahman

3,7954 gold badges29 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

Call Oracle procedure in Java code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related