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;SUBARU|24|9
AUDI|10|2
LADA|0|0
Итого|34|11<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}<%
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>
Простой
Средний
Средний