?
Solved

How to Call / Execute Oracle procedure using Java

Posted on 2007-10-12
8
Medium Priority
?
6,854 Views
Last Modified: 2008-07-22
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;
/
0
Comment
Question by:cutie_smily
  • 4
  • 4
8 Comments
 
LVL 92

Expert Comment

by:objects
ID: 20069359
> How can we see the procedure results on the Java console.

no, it is run on db server
0
 

Author Comment

by:cutie_smily
ID: 20069643
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 92

Accepted Solution

by:
objects earned 1500 total points
ID: 20069673
you can't. you'd need to return them as a result set to achieve that. then display that result set.
0
 

Author Comment

by:cutie_smily
ID: 20069730
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
 
LVL 92

Expert Comment

by:objects
ID: 20069767
instead of looping thru the rs in your sp, make it the resulr of your sp
0
 

Author Comment

by:cutie_smily
ID: 20073488
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
 

Author Comment

by:cutie_smily
ID: 20073490
below statement is commented
//String str = cstmt.getString(1);
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question