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;
/
cutie_smilyAsked:
Who is Participating?
 
objectsCommented:
you can't. you'd need to return them as a result set to achieve that. then display that result set.
0
 
objectsCommented:
> How can we see the procedure results on the Java console.

no, it is run on db server
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
cutie_smilyAuthor Commented:
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);

0
 
cutie_smilyAuthor Commented:
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
0
 
objectsCommented:
instead of looping thru the rs in your sp, make it the resulr of your sp
0
 
cutie_smilyAuthor Commented:
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();
            }
      }
}
0
 
cutie_smilyAuthor Commented:
below statement is commented
//String str = cstmt.getString(1);
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.