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)
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)
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 :
[/quote]
Ok, mas eu não disse que o jeito de chamar a procedure estava errado, eu disse o jeito que você pega o resultSet.
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();
}
}
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.
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[/quote]
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…
[code]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;[/code]
Identifiquei que o problema está nos :news e :olds da vida.
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:
[quote=AndreMayer]Provavelmente tu ta tentando acessar/modifcar algo restrito daquela dll: jvm.dll.
E como a jvm está rodando ta estourando o erro ali[/quote]
Provavelmente se ele nao tiver conseguido resolver este problema de 14/12/2007 19:16:58 até hoje, ja deve ter ganhado as contas!!
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.