
<%@ page import="java.sql.* , oracle.jsp.dbutil.*" %>

<!------------------------------------------------------------------
 * This is a basic JavaServer Page that uses a Cursor and Conn Beans and queries
 * dept table in schema scott and outputs the result in an html table.
 *  
--------------------------------------------------------------------!>

<% String connStr; %>
<%@ include file="../checkConn.jsp" %>

<jsp:useBean id="connbean" class="oracle.jsp.dbutil.ConnBean" scope="session">
<jsp:setProperty name="connbean" property="User" value="scott"/>
<jsp:setProperty name="connbean" property="Password" value="tiger"/>
</jsp:useBean>
<jsp:setProperty name="connbean" property="URL" value="<%= connStr %>"  />
<% session.setAttribute("connbean", connbean); %>

<jsp:useBean id="cursorbean" class="oracle.jsp.dbutil.CursorBean" scope="session">
<jsp:setProperty name="cursorbean" property="PreFetch" value="10"/>
<jsp:setProperty name="cursorbean" property="ExecuteBatch" value="2"/>
</jsp:useBean>

<HTML> 
  <HEAD> 
    <TITLE>
 CursorBean Demo JSP
    </TITLE>
  </HEAD>
 <BODY BGCOLOR=EOFFFO> 
 <H1> Hello 
  <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %>
 !  I'm Cursor Bean Demo JSP.
 </H1>
 <HR>
 <B> I'm using a cursor bean with PL/SQL to query department names from the DEPT table in schema SCOTT.
 </B> 

 <P>
<%
 
    try {

      // Make the Connection
      connbean.connect();

      String sql = "BEGIN OPEN ? FOR SELECT DNAME FROM DEPT ORDER BY DNAME; END;";
 
      // Create a Callable Statement
      cursorbean.create ( connbean, CursorBean.CALL_STMT, sql);
      cursorbean.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR);

      // Execute the PLSQL
      cursorbean.executeUpdate ();
 
      // Get the Ref Cursor
      ResultSet rset = cursorbean.getCursor(1);

      out.println(oracle.jsp.dbutil.BeanUtil.translateToHTMLTable (rset));

      // Close the RefCursor
      rset.close();

      // Close the Bean
      cursorbean.close();

      // Close the connection
      connbean.close();

    } catch (SQLException e) {
      out.println("<P>" + "There was an error doing the query:");
      out.println ("<PRE>" + e + "</PRE> \n <P>");
    }
%>

 </BODY>
</HTML>
