stored - Ejecute el bloque pl/sql anónimo y obtenga un conjunto de resultados en java

llamar stored procedure desde java

Question

Me gustaría ejecutar el PL / SQL anónimo y necesito obtener el objeto del conjunto de resultados. Obtuve el código que se puede hacer usando cursores dentro del bloque PL / SQL.

Pero el bloque PL / SQL en sí provendrá de la base de datos como texto. Así que no puedo editar ese bloque PL / SQL. Y devolverá solo dos valores cuyos nombres de columna serán siempre iguales. Volverá a la lista de valores de combinación de 2 columnas.

Aquí les doy una muestra de PL / SQL.

BEGIN

RETURN ''select distinct fundname d, fundname r from <table> where condition order by 1'';

EXCEPTION
   WHEN OTHERS THEN
    RETURN ''SELECT ''''Not Available'''' d, ''''Not Available'''' r FROM dual'';
END;

Cualquier respuesta será tan útil.

Answer 1

Aquí hay un ejemplo autocontenido de cómo "ejecutar el PL / SQL anónimo y obtener el objeto del conjunto de resultados"

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

import oracle.jdbc.OracleTypes;

public class CallPLSQLBlockWithOneInputStringAndOneOutputStringParameterAndOneOutputCursorParameter {

    public static void main(String[] args) throws Exception {

        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

        // Warning: this is a simple example program : In a long running application,
        // error handlers MUST clean up connections statements and result sets.

        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
        String plsql = "" +
        " declare " +  
        "    p_id varchar2(20) := null; " +
        "    l_rc sys_refcursor;" +
        " begin " +
        "    p_id := ?; " +
        "    ? := ''input parameter was = '' || p_id;" +
        "    open l_rc for " +
        "        select 1 id, ''hello'' name from dual " +
        "        union " +
        "        select 2, ''peter'' from dual; " +
        "    ? := l_rc;" +
        " end;";

        CallableStatement cs = c.prepareCall(plsql);
        cs.setString(1, "12345");
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.registerOutParameter(3, OracleTypes.CURSOR);

        cs.execute();

        System.out.println("Result = " + cs.getObject(2));

        ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
        while (cursorResultSet.next ())
        {
            System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));
        } 
        cs.close();
        c.close();
    }
}

La consulta de ejemplo anterior "select 1 id, nombre ''hello'' de dual union select 2, ''peter'' from dual;" Puede ser reemplazado por cualquier consulta.

Answer 2

En primer lugar, el código que has publicado no es válido. Un bloque PL / SQL anónimo no puede devolver una expresión. Y ningún bloque PL / SQL puede devolver el resultado de una consulta como esa. Necesitaría hacer algo como declarar un CURSOR DE REF y abrir ese cursor utilizando las distintas sentencias de SQL.

Dado que un bloque PL / SQL anónimo no puede devolver nada a la persona que llama, la arquitectura que está describiendo es problemática. Como mínimo, deberá modificar el bloque anónimo para que exista una variable de enlace que su código JDBC pueda registrar. Algo parecido (adaptado de un ejemplo en la Programación JDBC de Oracle de Menon''s Expert (tenga en cuenta que es posible que haya introducido algunos errores de sintaxis menores)

CallableStatement stmt := null;
ResultSet         rset := null;
String            query := ''DECLARE 
                              FUNCTION get_result
                                RETURN SYS_REFCURSOR
                              AS
                                l_rc SYS_REFCURSOR;
                              BEGIN
                                OPEN l_rc 
                                 FOR SELECT DISTINCT fundname d, fundname r
                                       FROM some_table
                                      WHERE some_condition
                                      ORDER BY 1;
                                RETURN l_rc;
                              EXCEPTION
                                WHEN others THEN
                                  OPEN l_rc 
                                   FOR SELECT ''Not Available'' d, ''Not Available'' r
                                         FROM dual;
                                  RETURN l_rc;
                              END get_result;
                            BEGIN
                              ? := get_result;
                            END;'';
try {
  cstmt := conn.prepareCall( query );
  cstmt.registerOutParameter( 1, OracleTypes.CURSOR );
  cstmt.execute();
  rset := (ResultSet) cstmt.getObject( 1 );
}
finally {
  <<close cstmt & rset>>
}

Answer 3

Intenta algo como esto (pseudo-código):

[create or replace] function get_dataset (p_query in varchar2) return sys_refcursor
as
  l_returnvalue sys_refcursor;
begin
  open l_returnvalue for p_query;
  return l_returnvalue;
end get_dataset;

El CURSOR DE REFERENCIA que se devuelve se puede procesar como un conjunto de datos normal.

Y ten cuidado con la inyección SQL cuando usas un enfoque como este ...