Stored Procedure em Oracle

8 de junho de 2006 15 respostas

Sergio_Marques 8 de junho de 2006

Prezados amigos :
Possuo a seguinte SP em Oracle :
PROCEDURE USP_603_CSU_UF
(
v_cursor out sys_refcursor
)

is
begin

open v_cursor for
select * from s524.vw_glc_uf
order by GLUF_SGL_UF;

end; – procedure

e estou usando o seguinte codigo Java para executá-la :
public class teste {

public static void main(String[] args) {
	Connection connection = null;
	try {

		// Carregando o Driver JDBC
		Class.forName("oracle.jdbc.driver.OracleDriver");
		connection = DriverManager.getConnection(
				"jdbc:oracle:thin:@172.33.3.23:1521:des1", "usu1","usu1");
		System.out.println("Conectado ...");
		OracleCallableStatement cStmt = (OracleCallableStatement) connection.prepareCall("{call S603.usp_603_csu_uf(?)}");
		cStmt.registerOutParameter(1, OracleTypes.CURSOR);
		
		//
		cStmt.execute();

		ResultSet rs = (ResultSet) cStmt.getObject(1);

		while (rs.next()) {
		System.out.println(rs.getString(1));
		}


	} catch (Exception e) {
		System.out.println("Problemas na execução ...");
		e.printStackTrace();
	}
}

}

quando executo o meu stackTrace vem com o seguinte erro :

Conectado …

java.sql.SQLException: Tamanho de tipo maior que o Máximo

Problemas na execução …

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)

at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:885)

at oracle.jdbc.driver.T4CMAREngine.buffer2Value(T4CMAREngine.java:2231)

at oracle.jdbc.driver.T4CMAREngine.unmarshalUB2(T4CMAREngine.java:1048)

at oracle.jdbc.driver.T4CTTIdcb.receiveCommon(T4CTTIdcb.java:112)

at oracle.jdbc.driver.T4CTTIdcb.receiveFromRefCursor(T4CTTIdcb.java:104)

at oracle.jdbc.driver.T4CResultSetAccessor.unmarshalOneRow(T4CResultSetAccessor.java:165)

at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:788)

at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:705)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:527)

at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:180)

at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:783)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2979)

at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4103)

at teste.main(teste.java:53)

Alguém poderia me ajudar

15 Respostas

boaglio 8 de jun. de 2006

Talvez a primeira coluna da tabela não seja uma String...

Por favor use as tags Code para exibir o código.
Assim:

PROCEDURE USP_603_CSU_UF
(
v_cursor out sys_refcursor
)

is
begin

open v_cursor for
select * from s524.vw_glc_uf
order by GLUF_SGL_UF;

end;

Sergio_Marques 8 de jun. de 2006

Caro boaglio :

A primeira coluna do ResultSet é String ( Varchar2(2) ) que é a Sigla UF. Portanto ainda não consegui fazer funcionar.

boaglio 8 de jun. de 2006

Dando uma olhada no seu código acho que o problema é o jeito que você pega o resultSet.

Veja esse exemplo:

http://www.idevelopment.info/data/Programming/java/jdbc/PLSQL_and_JDBC/RefCursorExample.java

Sergio_Marques 9 de jun. de 2006

Prezado Boaglio :

Muito obrigado pela sua atenção, mas a maneira de chamar a procedure enviada no exemplo ( {?=call call S603.usp_603_csu_uf(?)} ) resulta no seguint erro - o PL/SQL espera 2 parametros quando na verdade eu só tenho 1 de retorno, veja :

Loading JDBC Driver  -> oracle.jdbc.driver.OracleDriver
  Connecting to        -> jdbc:oracle:thin:@172.31.2.23:1521:des1
  Connected as         -> u603

Using OracleCallableStatement / OracleResultSet
-----------------------------------------------
java.sql.SQLException: Parâmetro IN ou OUT ausente do índice:: 2
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
	at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1645)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2883)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2979)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4103)
Using CallableStatement / ResultSet
-----------------------------------
	at RefCursorExample.performRefCursor(RefCursorExample.java:171)
	at RefCursorExample.main(RefCursorExample.java:269)
java.sql.SQLException: Parâmetro IN ou OUT ausente do índice:: 2
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
	at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1645)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2883)  Closing Connection...

	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2979)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4103)
	at RefCursorExample.performRefCursor2(RefCursorExample.java:217)
	at RefCursorExample.main(RefCursorExample.java:270)

Quando eu “conserto o erro” ({call call S603.usp_603_csu_uf(?)}) obtenho o mesmo erro anterior :

Loading JDBC Driver  -> oracle.jdbc.driver.OracleDriver
  Connecting to        -> jdbc:oracle:thin:@172.31.2.23:1521:des1
  Connected as         -> u603

Using OracleCallableStatement / OracleResultSet
-----------------------------------------------
java.sql.SQLException: Tamanho de tipo maior que o Máximo
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
	at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:885)
	at oracle.jdbc.driver.T4CMAREngine.buffer2Value(T4CMAREngine.java:2231)
	at oracle.jdbc.driver.T4CMAREngine.unmarshalUB2(T4CMAREngine.java:1048)
	at oracle.jdbc.driver.T4CTTIdcb.receiveCommon(T4CTTIdcb.java:112)
	at oracle.jdbc.driver.T4CTTIdcb.receiveFromRefCursor(T4CTTIdcb.java:104)
	at oracle.jdbc.driver.T4CResultSetAccessor.unmarshalOneRow(T4CResultSetAccessor.java:165)
	at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:788)
	at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:705)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:527)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:180)
	at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:783)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)Using CallableStatement / ResultSet
-----------------------------------

	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2979)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4103)
	at RefCursorExample.performRefCursor(RefCursorExample.java:171)
	at RefCursorExample.main(RefCursorExample.java:269)
java.sql.SQLException: OALL8 is in an inconsistent state.
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
	at oracle.jdbc.driver.T4C8Oall.init(T4C8Oall.java:308)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:165)
	at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:783)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2979)
  Closing Connection...
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4103)
	at RefCursorExample.performRefCursor2(RefCursorExample.java:217)
	at RefCursorExample.main(RefCursorExample.java:270)
java.sql.SQLException: Violação de protocolo
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
	at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:885)
	at oracle.jdbc.driver.T4C7Ocommoncall.receive(T4C7Ocommoncall.java:133)
	at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:385)
	at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:1036)
	at RefCursorExample.closeConnection(RefCursorExample.java:248)
	at RefCursorExample.main(RefCursorExample.java:271)

boaglio 9 de jun. de 2006

Sergio Marques:
Prezado Boaglio :

Muito obrigado pela sua atenção, mas a maneira de chamar a procedure enviada no exemplo ( {?=call call S603.usp_603_csu_uf(?)} ) resulta no seguint erro - o PL/SQL espera 2 parametros quando na verdade eu só tenho 1 de retorno, veja :

Ok, mas eu não disse que o jeito de chamar a procedure estava errado, eu disse o jeito que você pega o resultSet.

julianostr 9 de jun. de 2006

Tentou usar o getCursor ao inves do getObject ??

Sergio_Marques 13 de jun. de 2006

Prezados amigos :
Já tentei chamar da maneira como voces sugeriram, já li todos os meus livros de Java e Oracle e a maneira sugerida é a que vcs me mostraram. Um colega daqui do trabalho sugeriu que deva ser uma coisa do JDBC da minha versão Oracle. Vou tentar em casa com meu driver e meu Oracle para ver se funciona.

Eis aqui o código tentado :

// -----------------------------------------------------------------------------
// RefCursorExample.java
// -----------------------------------------------------------------------------

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

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.driver.OracleResultSet;


/**
 * -----------------------------------------------------------------------------
 * This class provides an example on the use of REF Cursors to execute SQL from 
 * a JDBC program, simulating dynamic SQL.
 * 
 * ===================
 * DYNAMIC SQL IN JAVA
 * ===================
 * 
 * JDBC provides APIs for executing Dynamic SQL using PreparedStatement. 
 * For example:
 * 
 *      PreparedStatement pstmt;
 *      pstmt=conn.prepareStatement("SELECT name FROM dept WHERE deptno > ?");
 *      pstmt.setInt(1,104);
 *      ResultSet c1;
 *      c1=pstmt.executeQuery();
 *      pstmt.setInt(1,10)
 *      while (c1.next ()) {System.out.println (c1.getInt(1));} 
 * 
 * 
 * ==========
 * REF CURSOR
 * ==========
 * 
 * Another option of executing dynamic SQL from JDBC is provided in this
 * example. Keep in mind that this example will only work with Oracle8i and
 * higher. In this case, the  procedure uses a PL/SQL procedure which returns 
 * a REF CURSOR.
 * 
 * A REF CURSOR is similar a pointer in the C programming language. It points
 * to rows retrieved from the database using a PL/SQL cursor. The example I
 * provide in this class uses a REF CURSOR to point to the result set
 * returned by a SELECT statement that retrieves rows from the DEPT table
 * using a PL/SQL cursor.
 * 
 * In this example, I call a PL/SQL procedure named "get_dept_ref_cursor" which
 * returns a variable of type "t_ref_cursor".
 * 
 * Stored procedures can return user-defined types, or cursor variables, of the 
 * REF CURSOR category.  This output is equivalent to a database cursor or a 
 * JDBC result set. A REF CURSOR essentially encapsulates the results of a 
 * query.
 * 
 * Advantages of using a REF CURSOR are:
 * 
 *      1.) Code Reusability
 *      
 *          The same package procedure could be used for other Java and non-Java
 *          applications.
 *          
 *      2.) Load Balancing. 
 * 
 * 
 * =============================
 * OracleCallableStatement CLASS
 * =============================
 * 
 * You will notice that in this example, I use an OracleCallableStatement class
 * in place of our typical CallableStatement class. This class defines a method
 * named getCursor() that enables you to read Oracle cursors.
 * 
 * 
 * =================
 * OracleTypes CLASS
 * =================
 * 
 * You will also notice the oracle.jdbc.driver.OracleTypes is also used
 * when registering the OutParameter. This class defines those special TYPEs
 * offered by the Oracle database. This class is similar to java.sql.Types.
 * 
 * 
 * ===========================================================
 * NOT USING OracleCallableStatement and OracleResultSet CLASS
 * ===========================================================
 * 
 * Note that you are not required to use the OracleCallableStatement and
 * OracleResultSet classes; you could use the regular CallableStatement
 * and ResultSet classes found in java.sql. However, you will need to 
 * use the getObject() method to read the Oracle cursor. An example of this is
 * provided in this example with the performRefCursor2() method.
 * 
 * 
 * -----------------------------------------------------------------------------
 * 
 * NOTE: Opening a REF CURSOR for a statement present in a variable is only 
 *       supported with Oracle8i and higher. 
 * 
 * NOTE: In order to successfully use this class, you will need to run the
 *       create_all_ddl.sql file included in the same section this example class
 *       is located.
 * 
 * -----------------------------------------------------------------------------
 */

public class RefCursorExample {

    final static String driverClass    = "oracle.jdbc.driver.OracleDriver";
    final static String connectionURL  = "jdbc:oracle:thin:@173.11.3.25:1521:tes1";
    final static String userID         = "des1";
    final static String userPassword   = "des1";
    Connection   con                   = null;


    /**
     * Construct a RefCursorExample object. This constructor will create an Oracle
     * database connection.
     */
    public RefCursorExample() {

        try {

            System.out.print("  Loading JDBC Driver  -> " + driverClass + "\n");
            Class.forName(driverClass).newInstance();

            System.out.print("  Connecting to        -> " + connectionURL + "\n");
            this.con = DriverManager.getConnection(connectionURL, userID, userPassword);
            System.out.print("  Connected as         -> " + userID + "\n\n");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    /**
     * This method is used to return a REF CURSOR that will be used to retrieve 
     * data from a result set. This REF CUSROR is retrieved by the JDBC program 
     * into a ResultSet.
     * 
     * This method Uses the OracleCallableStatement and OracleResultSet classes.
     */
    public void performRefCursor() {

        OracleCallableStatement oraCallStmt   = null;
        OracleResultSet         deptResultSet = null;

        System.out.println("Using OracleCallableStatement / OracleResultSet");
        System.out.println("-----------------------------------------------");

        try {

            oraCallStmt = (OracleCallableStatement) con.prepareCall(
                "{call S603.usp_603_csu_uf(?)}"
            );
            oraCallStmt.registerOutParameter(1, OracleTypes.CURSOR);
            //oraCallStmt.setInt(2, 104);
            oraCallStmt.execute();

            deptResultSet = (OracleResultSet) oraCallStmt.getCursor(1);

            while (deptResultSet.next()) {
                System.out.println(
                    " - " +
                    deptResultSet.getString(2) + " (" + deptResultSet.getInt(1) + "), " + 
                    deptResultSet.getString(3)
                );
            }
            System.out.println();
            
            oraCallStmt.close();
            
        } catch (SQLException e) {

            e.printStackTrace();

        }

    }


    /**
     * This method is used to return a REF CURSOR that will be used to retrieve 
     * data from a result set. This REF CUSROR is retrieved by the JDBC program 
     * into a ResultSet.
     * 
     * This method Uses the the regular CallableStatement and ResultSet classes.
     */
    public void performRefCursor2() {

        CallableStatement cstmt = null;
        ResultSet         rset  = null;

        System.out.println("Using CallableStatement / ResultSet");
        System.out.println("-----------------------------------");

        try {

            cstmt = con.prepareCall(
                "{call call S603.usp_603_csu_uf(?)}"
            );
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);
            //cstmt.setInt(2, 104);
            cstmt.execute();

            rset = (ResultSet) cstmt.getObject(1);
            
            while (rset.next()) {
                System.out.println(
                    " - " +
                    rset.getString(2) + " (" + rset.getInt(1) + "), " + 
                    rset.getString(3)
                );
            }
            System.out.println();
                    
            cstmt.close();
            
        } catch (SQLException e) {

            e.printStackTrace();

        }

    }


    /**
     * Close down Oracle connection.
     */
    public void closeConnection() {

        try {
            System.out.print("  Closing Connection...\n");
            con.close();
            
        } catch (SQLException e) {
        
            e.printStackTrace();
            
        }

    }


    /**
     * Sole entry point to the class and application.
     * @param args Array of String arguments.
     * @exception java.lang.InterruptedException
     *            Thrown from the Thread class.
     */
    public static void main(String[] args)
            throws java.lang.InterruptedException {

        RefCursorExample mainPrg = new RefCursorExample();
        mainPrg.performRefCursor();
        mainPrg.performRefCursor2();
        mainPrg.closeConnection();

    }

}

fabim 10 de jan. de 2007

tb estou tendo o mesmo problema…

alguem ja sabe como solucionar isso??

o problema nao ta na hora de mexer com o resultSet mesmo…
o erro e na hora do .execute(), do objeto Statement…

parece ser algo com o .registerOutParameter(1, OracleTypes.CURSOR)

sempre da o erro “Tamanho de tipo maior q o maximo”

spark 26 de jan. de 2007

Nao e facil resolver este problema no oracle. Cada function ou procedure eh um caso novo. A dica principal eh verificar a ordem e os tipos dos parametros passados e tentar obedece-los.

fabim 26 de jan. de 2007

amigos…

pior q o problema era facil de resolver memsmo, mas jamais poderia imaginá-lo…

fuçando uns sites english axei um cara q descobriu q era um bug do driver… troquei o driver jdbc e funcionoui tudo certo =D

flws

marciosantri 30 de nov. de 2007

fabiocsi:
amigos...
pior q o problema era facil de resolver memsmo, mas jamais poderia imaginá-lo...

fuçando uns sites english axei um cara q descobriu q era um bug do driver... troquei o driver jdbc e funcionoui tudo certo =D

flws

Levantando esse tópico que quase faz aniversário, qual é esse novo drive JDBC que você utilizou?

Estou tentando criar essa trigger no banco via JDBC e está dando este erro de parâmetro...

create or replace TRIGGER ACOMP_ORCAMENTOS_IU_AR 
AFTER INSERT OR UPDATE 
ON ACOMPANHAMENTO_ORCAMENTOS 
FOR EACH ROW 
declare
	status_ant	ACOMPANHAMENTO_ORCAMENTOS.STATUS%type;
begin

	if inserting then
		status_ant := '?';
	else
		status_ant := :old.STATUS;
	end if;

	if :new.STATUS <> status_ant then
		insert into LOGS_ACOMPANHAMENTO_ORCAMENTOS(
			ORCAMENTO_ID,
			STATUS,
			VENDEDOR_ID,
			VALOR_PEDIDO)
		values(
			:new.ORCAMENTO_ID,
			:new.STATUS,
			:new.VENDEDOR_ID,
			:new.VALOR_PEDIDO
		);
	end if;
	
end ACOMP_ORCAMENTOS_IU_AR;

Identifiquei que o problema está nos :news e :olds da vida.

jricardo_so 14 de dez. de 2007

Pessoal, tenho com um problema parecido, estou tentando executar uma procedure que retorna valores, usei as dicas desta discussão mas tá dando o seguinte erro quando o método oraCallStmt.execute() é chamado:

An unexpected error has been detected by HotSpot Virtual Machine:

EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x6d74f6c4, pid=2212, tid=644

Java VM: Java HotSpot™ Client VM (1.5.0_07-b03 mixed mode)

Problematic frame:

V [jvm.dll+0x8f6c4]

An error report file with more information is saved as hs_err_pid2212.log

If you would like to submit a bug report, please visit:

http://java.sun.com/webapps/bugreport/crash.jsp

alguem sabe como resolver isso? desde já agradeço.

AndreMayer 27 de fev. de 2009

Provavelmente tu ta tentando acessar/modifcar algo restrito daquela dll: jvm.dll.
E como a jvm está rodando ta estourando o erro ali

fredferrao 27 de fev. de 2009

AndreMayer:
Provavelmente tu ta tentando acessar/modifcar algo restrito daquela dll: jvm.dll.
E como a jvm está rodando ta estourando o erro ali

Provavelmente se ele nao tiver conseguido resolver este problema de 14/12/2007 19:16:58 até hoje, ja deve ter ganhado as contas!!

mateusvenan 17 de out. de 2011

Colegas… segue a solução: Atualizem os arquivos ojdbc14.jar e classes12.jar para a versão correspondente do Oracle usado. Isto soluciona o problema. Passei por esse problema ele hoje e, infelizmente, por não termos o tópico atualizado, perdi um bom tempo caçando a solução.

Boa sorte quem precisar…

Criado 8 de junho de 2006

Ultima resposta 17 de out. de 2011

Respostas 15

Participantes 10