Oracle Java stored procedures returning data

Question

I'm trying to write a Java stored procedure that could return a result. I've found this doc on Oracle website, but none of the examples provided return data http://docs.oracle.com/cd/B19306_01/java.102/b14187/cheight.htm#CHDJJDGH

I have created the package as follow:

CREATE OR REPLACE PACKAGE test_proc AS 
FUNCTION hello_world RETURN VARCHAR2;
PROCEDURE insert_test(CHAINE VARCHAR2, NOMBRE NUMBER);
END test_proc;

The package body as follow

CREATE OR REPLACE PACKAGE BODY test_proc AS
FUNCTION hello_world RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'TestProc.helloWorld() return java.lang.String';
PROCEDURE insert_test(CHAINE VARCHAR2, NOMBRE NUMBER) AS LANGUAGE JAVA
NAME 'TestProc.insertTEST(java.lang.String, int)';
END test_proc;

And the Java Code

public class TestProc {

public static void insertTEST(String chaine, int nombre) 
{
    System.out.println("Insert into test...");
    String sql = "INSERT INTO TEST VALUES(?,?)";
    try
    {
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, chaine);
        pstmt.setInt(2, nombre);
        pstmt.executeUpdate();
    }
    catch (SQLException e)
    {
        System.err.println(e.getMessage());     
    }
}

public static String helloWorld() 
{
    return "Hello world!!";
 }
}

I use SQLDeveloper to call my procedures using the following instructions

CALL test_proc.insert_test('test',1); #work
CALL test_proc.hello_world(); #doesn't work

When executing the second instruction I have the following error ORA-06576: not a valid function or procedure name

Do you know how to solve this? Or do you know where to find a working example of java stored procedure returning data in an Oracle database?

Finally got a result using the following command:

select test_proc.hello_world() from dual;

Result:

TEST_PROC.HELLO_WORLD()                                                              
-------------------------------------------------------------------------------------
Hello World!!
1 rows selected

Do you know how to return complex result from database like multiple row?

rgettman · Accepted Answer · 2014-01-31 16:59:48Z

1

For a PL/SQL function that returns something, you cannot call it in PL/SQL as if it were a procedure. Call it as a function. Try

var test VARCHAR2(30)
CALL test_proc.hello_world() INTO :test;
print test

TEST
------------------------------------------
Hello world!!

answered Jan 31, 2014 at 16:59

rgettman

179k30 gold badges282 silver badges365 bronze badges

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

2 Comments

Darwiin Over a year ago

var output VARCHAR2(30) CALL test_proc.hello_world() INTO :output; print output

Darwiin Over a year ago

And got following error: ORA-01008:"not all variables bound" (Sorry about formatting, doesn't seem to work in comments...)

Collectives™ on Stack Overflow

Oracle Java stored procedures returning data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related