Pessoal,
Tenho aqui uma função PL/SQL que faz um insert em uma tabela e retorna o ID inserido que está funcionando sem problemas.
Entretanto quando estou tentando rodar essa função no java usando CallableStatement e não estou conseguindo entender bem como esse negócio funciona.
Alguém poderia me ajudar a compreender o funcionamento desse negócio?
aqui está a função PL/SQL:
CREATE OR REPLACE FUNCTION INS_DELIVERY
(MASTER_NAME IN VARCHAR2,
DATA_DOMAIN IN VARCHAR2,
PROCESSING_TYPE IN VARCHAR2,
MEDIA_TYPE IN VARCHAR2,
DATA_TYPE IN VARCHAR2,
DESCRIPTION IN VARCHAR2,
REQUESTING_INTERPRETER IN VARCHAR2,
RESPONSIBLE_GEOPHYSICIST IN VARCHAR2,
REQUEST_DATE IN DATE,
DELIVERY_DATE IN DATE,
INTERPRETATION_PROJECT IN VARCHAR2,
SURVEY_NAME IN VARCHAR2,
REGISTER_LENGTH IN NUMBER,
DATUM IN VARCHAR2,
SAMPLE_RATE IN NUMBER,
PROJECTION IN VARCHAR2,
ACQUIRED_BY IN VARCHAR2,
ACQUISITION_DATE IN DATE,
PROCESSED_BY IN VARCHAR2,
PROCESSING_DATE IN DATE,
PROCESSING_FLOW IN VARCHAR2)
RETURN NUMBER
IS
DELIVERY_ID NUMBER;
BEGIN
SELECT NVL(MAX(DELIVERY_ID)+1,0) INTO DELIVERY_ID FROM DELIVERY;
INSERT INTO DELIVERY
VALUES(DELIVERY_ID,
MASTER_NAME,
DATA_DOMAIN,
PROCESSING_TYPE,
MEDIA_TYPE,
DATA_TYPE,
DESCRIPTION,
REQUESTING_INTERPRETER,
RESPONSIBLE_GEOPHYSICIST,
REQUEST_DATE,
DELIVERY_DATE,
INTERPRETATION_PROJECT,
SURVEY_NAME,
REGISTER_LENGTH,
DATUM,
SAMPLE_RATE,
PROJECTION,
ACQUIRED_BY,
ACQUISITION_DATE,
PROCESSED_BY,
PROCESSING_DATE,
PROCESSING_FLOW);
RETURN DELIVERY_ID;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
e aqui está uma das dezenas de tentativas fracassadas de colocar esse treco para funcionar. (desculpem se não faz muito sentido, mas chegou ao ponto em que eu já estava testando qualquer asneira…)
public static void teste(){
CallableStatement cstmt = null;
int deliveryId;
try {
cstmt = Database.getConnection().prepareCall("{? = call INS_DELIVERY(?)}");
cstmt.setString(1, "A");
cstmt.setString(2, "A");
cstmt.setString(3, "A");
cstmt.setString(4, "A");
cstmt.setString(5, "A");
cstmt.setString(6, "A");
cstmt.setString(7, "A");
cstmt.setString(8, "A");
cstmt.setDate(9, new java.sql.Date(2010));
cstmt.setDate(10, new java.sql.Date(2010));
cstmt.setString(11, "A");
cstmt.setString(12, "A");
cstmt.setInt(13, 0);
cstmt.setString(14, "A");
cstmt.setInt(15, 0);
cstmt.setString(16, "A");
cstmt.setString(17, "A");
cstmt.setDate(18, new java.sql.Date(2010));
cstmt.setString(19, "A");
cstmt.setDate(20, new java.sql.Date(2010));
cstmt.setString(21, "A");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.execute();
deliveryId = cstmt.getInt(1);
cstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
Obrigado!