Home » Developer & Programmer » JDeveloper, Java & XML » seeing java output in sqlplus (10.2.0.1.0)
seeing java output in sqlplus [message #365339] Thu, 11 December 2008 09:52 Go to next message
Kevin Meade
Messages: 1936
Registered: December 1999
Location: Connecticut USA
Senior Member
I want to demonstrated to my guys at work, how java can work with nested refcursors. I found the following java routine on the internet and I am sure it works, but I cannot see any of its output. The java routine uses system.out.println to output results.

Quote:
How can I view this in Sqlplus?
If not in Sqlplus, where does it go and how can I see it so that I can prove to them it works.

I execute in sqlplus, and it says procedure completed successfully.
I execute from toad it says same thing.
I look in v$sqlarea I see the query.

Thanks, Kevin

create or replace and compile java source named "MyNestedCursorDemo" as

   import java.io.*;
   import java.sql.*;
   import oracle.sql.*;
   import oracle.jdbc.driver.*;
   import oracle.jdbc.OracleTypes;

   public class NestedCursorDemo {

     public static void CursorTest() throws SQLException {

        String query = 
            "begin " + "open ? for " + "'select department_id" + ",department_name" + 
            ",      cursor(select employee_id, last_name, department_id" + 
            "              from   employees e" + 
            "              where  e.department_id = d.department_id) employees " + 
            "from departments d " + "where department_id=60';" + "end;";

        System.out.println(" query = " + query);

        Connection conn = new OracleDriver().defaultConnection();

        Statement trace = conn.createStatement();

        CallableStatement cstmt = conn.prepareCall(query);

        cstmt.registerOutParameter(1, OracleTypes.CURSOR);
        cstmt.execute();

        ResultSet rs = (ResultSet)cstmt.getObject(1);

        for (int i = 0; rs.next(); i++) {
            System.out.println("departmentid   " + rs.getString(1));
            System.out.println("departmentname " + rs.getString(2));
            ResultSet rs2 = (ResultSet)rs.getObject(3);
            for (int j = 0; rs2.next(); j++) {
                System.out.println("  employeeid   " + rs2.getString(1));
                System.out.println("  lastname     " + rs2.getString(2));
                System.out.println("  departmentid " + rs2.getString(3));
                System.out.println("  --------");
            }
            ;
            rs2.close();
        }
        ;
        rs.close();
     }
   }
/
show errors


create or replace
procedure show_nested_refcursors
as language java
name 'NestedCursorDemo.CursorTest()';
/


set serveroutput on
exec show_nested_refcursors
Re: seeing java output in sqlplus [message #365344 is a reply to message #365339] Thu, 11 December 2008 10:38 Go to previous message
Kevin Meade
Messages: 1936
Registered: December 1999
Location: Connecticut USA
Senior Member
Never mind, I found it.

CALL DBMS_JAVA.SET_OUTPUT(1000000);

SQL> exec show_nested_refcursors

query = begin open ? for 'select department_id,department_name,
cursor(select employee_id, last_name, department_id              from
employees e              where  e.department_id = d.department_id) employees
from departments d where department_id=60';end;
departmentid   60
departmentname IT
employeeid   103
lastname     Hunold
departmentid 60
--------
employeeid   104
lastname     Ernst
departmentid 60
--------
employeeid   105
lastname     Austin
departmentid 60
--------
employeeid   106
lastname     Pataballa
departmentid 60
--------
employeeid   107
lastname     Lorentz
departmentid 60
--------
 
PL/SQL procedure successfully completed.
 
SQL>


Thanks everyone. Kevin
Previous Topic: EJB 3.0 Compatible
Next Topic: Very Strange Problem
Goto Forum:
  


Current Time: Wed Aug 27 16:04:26 CDT 2014

Total time taken to generate the page: 0.10399 seconds