asked on

How to Call / Execute Oracle procedure using Java

How to call / execute the below oracle stored procedure using java. How can we see the procedure results on the Java console. Any help will be appreciated.

Create or replace
PROCEDURE GETEMAILS(
pbusinessid IN NUMBER) AS
vtrue NUMBER (1) := 1;
vfalse NUMBER (1) := 0;
CURSOR my_cur IS
SELECT DISTINCT bc.businessid
,bc.contactid
,bce.USAGE
,bce.email_address
FROM businesscontacts bc JOIN businesscontactemails bce ON bce.contactid = bc.contactid
WHERE bc.businessid = pbusinessid AND
bc.receive_notification = vtrue);
BEGIN
FOR rec_t IN my_cur
LOOP
--
DBMS_OUTPUT.put_line ('-------------------------- ');
DBMS_OUTPUT.put_line ('BusinessId: ' || rec_t.businessid);
DBMS_OUTPUT.put_line ('ContactId: ' || rec_t.contactid);
DBMS_OUTPUT.put_line ('Usage: ' || rec_t.USAGE);
DBMS_OUTPUT.put_line ('email_address: ' || rec_t.email_address);
--
END LOOP;
END;
/

Mick Barry

http://www.exampledepot.com/egs/java.sql/CallProcedure.html

Mick Barry

> How can we see the procedure results on the Java console.

no, it is run on db server

cutie_smily

ASKER

How to see these results on my java console..

DBMS_OUTPUT.put_line ('-------------------------- ');
DBMS_OUTPUT.put_line ('BusinessId: ' || rec_t.businessid);
DBMS_OUTPUT.put_line ('ContactId: ' || rec_t.contactid);
DBMS_OUTPUT.put_line ('Usage: ' || rec_t.USAGE);
DBMS_OUTPUT.put_line ('email_address: ' || rec_t.email_address);

ASKER CERTIFIED SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cutie_smily

ASKER

you'd need to return them as a result set to achieve that. then display that result set.
Can i get some help to do that part?

Thanks

Mick Barry

instead of looping thru the rs in your sp, make it the resulr of your sp

cutie_smily

ASKER

I appreciate you advise, but I want to use as it is because i have many complex procedures that i need to work which look similar to the above. DBA provides these procedures n i am supposed to use them in my code to see the result. As its urgent please send me sample codes. Till now i have got till here with no errors but not able to see any result. we might to get them prep.statment but how.?

public class JDBCConnection {
      public static void main(String[] args) {
            try {

                  Class.forName("oracle.jdbc.driver.OracleDriver");
                  String url = "jdbc:oracle:thin:@ca:1521:Ca10g";
                  String username = "xxxx";
                  String password = "wwwww";

                  Connection con = DriverManager.getConnection(url, username,
                              password);
                  System.out.println("Connected to database");
                  // Execute the SQL statement
            /*      Statement stmt = con.createStatement();
                  ResultSet resultSet = stmt.executeQuery("SELECT * from BUSINESSES");
                  System.out.println("Got results!");
                  // Loop thru all the rows
                  while (resultSet.next()) {
                        String data = resultSet.getString("BUSINESSALIASNAME");
                        System.out.println(data);
                  }
                  stmt.close();*/


      String command = "{call GETEMAILS_TST2(?)}"; // 4 placeholders
      CallableStatement cstmt = con.prepareCall (command);
      cstmt.setInt(1, 21);
      String str = cstmt.getString(1);
      cstmt.execute();
      cstmt.close();

      System.out.println("Closed Callable Statement"+str);

            } catch (Exception e) {
                  System.out.println(e.getMessage());
                  e.printStackTrace();
            }
      }
}

cutie_smily

ASKER

below statement is commented
//String str = cstmt.getString(1);