retrieve data from a stored ORACLE procedure by JSP

[Baranenko Nikolay]

Hello,

want to resolve problem

in Oracle exits procedure witch returns SYS_REFCURSOR

REPORTS.inwork

In pl-sql developer this variant returns information
DECLARE   v_ref  SYS_REFCURSOR;   v_instance admin.newtable.name%TYPE;   v_all number;   v_notin number;   v_prt varchar2(20) default 'Total' ; 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;

pl-sql block result

SUBARU|24|9 AUDI|10|2 LADA|0|0 Total|34|11

how to show this data used JSP (JSTL)?

p.s.

before I used this variant

<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> Result SQL в JSON: ${json_text}

Please help an example of information output from the Oracle stored procedure by JSP (JSTL).

[Dave Tolls]

That block doesn't return any information.
It is simply writing lines to a buffer in the db.
These can be retrieved by calls to DBMS_OUTPUT.GET_LINES (this is what SQL*Plus does, I believe).

Now, I've never tried this, but you could call that block and then, with the same Connection, try and see what happens when you call GET_LINES (it's a procedure).

Seems a rather roundabout way of doing it, though.

Is there a reason you can't call REPORTS.inwork() directly, and handle the cursor OUT parameter as a ResultSet?

[Baranenko Nikolay]

Hello, Dave

some days ago I founded this resolve, used ResultSet :-)

Connection conn = null;
CallableStatement stmt = null;
ResultSet rset = null;

String SOME_NAME = "{call REPORTS.mi_inwork(?,?)}";

  try {
        conn = DriverManager.getConnection(Connection_URL,UserName, Password);

            stmt = conn.prepareCall(SOME_NAME);//We have declared this at the very top
        stmt.setString(1, "Итого");//Passing CompanyID here
        stmt.registerOutParameter(2, OracleTypes.CURSOR);//Refcursor selects the row based upon query results provided in Package.
        stmt.execute();
        rset = (ResultSet) stmt.getObject(2);

        while (rset.next()) {
            out.println(rset.getString(1)+" | "+ rset.getString(2)+" | "+ rset.getString(3));
}
} catch (Exception e) {
     //   LOGGER.error("Error extracting ", e);
        out.println( "<h1>exception: "+e.getMessage()+"</h1>" );
} finally {
     //   DBUtils.cleanUp(conn, stmt, rset);
}