What is the correct way to get data from a stored PL/SQL procedure in JSP (JSTL)?

Question

JavaScript

Hello.

Yesterday I faced such a problem.
Oracle has a procedure that takes a certain value as input and returns the result as SYS_REFCURSOR.

PL-SQL block listing

DECLARE
  v_ref  SYS_REFCURSOR;
  v_instance admin.newtable.name%TYPE;
  v_all number;
  v_notin number;
  v_prt varchar2(20) default 'Итого' ;
BEGIN
    REPORTS.inwork(v_prt, v_ref);
 
  LOOP
    FETCH v_ref
    INTO  v_instance, v_all, v_notin;
    EXIT WHEN v_ref%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_instance||'|'||v_all||'|'||v_notin);
  END LOOP;
  CLOSE v_ref;
END;

the result of executing the PL-SQL block is the following

SUBARU|24|9
AUDI|10|2
LADA|0|0
Итого|34|11

I want to display this data in WEB on JSP (JSTL) block
used this method before

<c:set var="SCRIPT">
select sysdate as VALUE from dual
</c:set>

<sql:query var="SCRIPT" dataSource="jdbc/DBORACLE">
   ${SCRIPT}
</sql:query>

<%@ taglib prefix="json" uri="http://www.atg.com/taglibs/json" %>

<c:set var="json_text" scope="application">
<json:object>
<json:array name="FROM_DBORACLE" prettyPrint="false">
<c:forEach var="row" items="${SCRIPT.rows}">
<json:object>
   <json:property name="VALUE" value="${row.VALUE}"/>
</json:object>
</c:forEach>
</json:array>

</json:object>
</c:set>

Результат SQL в JSON: ${json_text}

Please help with an example of outputting information from an Oracle stored procedure (preferably with SYS_REFCURSOR) to JSP(JSTL).

Answer 1

solved this problem in this way

<%
                        String input_parameter="Итого";
      String Driver_Class="oracle.jdbc.driver.OracleDriver";
      String Connection_URL="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myoracle)))";
      String UserName="user";
      String Password="password";
      Connection conn = null;
      CallableStatement stmt = null;
      ResultSet rset = null;

      String SOME_NAME = "{call  REPORTS.inwork(?,?)}";
                        // в SYS_REFCURSOR 4 поля для вывода - начало с 1.
      try {
        conn = DriverManager.getConnection(Connection_URL,UserName, Password);
        stmt = conn.prepareCall(SOME_NAME); 
        stmt.setString(1, input_parameter);
        stmt.registerOutParameter(2, OracleTypes.CURSOR); 
        stmt.execute();
        rset = (ResultSet) stmt.getObject(2);

        while (rset.next()) {
    %>
    <TR>
      <TD><%=rset.getString(1)%></TD>
      <TD><%=rset.getString(2)%></TD>
      <TD><%=rset.getString(3)%></TD>
      <TD><%=rset.getString(4)%></TD>
    </TR>
    <%
        }
      } catch (Exception e) {
        out.println( "<h1>exception: "+e.getClass().getName() + ": " + e.getMessage()+"</h1>" );
      } finally {
      }
    %>
  </table>