How to run a . sql file containing a Procedure (oracle) in Java

Question

I have this script inside a file named xxx.sql

how do I create Procedure through a call within java code?

DECLARE

   PROCEDURE P_GERA_LOG(P_TEXTO IN VARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'DD/MM/YY HH24:MI:SS')||' - '||P_TEXTO);
   END P_GERA_LOG;  
  
   PROCEDURE SLEEP(P_SEGUNDOS IN INT) IS
      V_NOW DATE := SYSDATE;
   BEGIN
      LOOP
         EXIT WHEN SYSDATE >= V_NOW + (P_SEGUNDOS * (1/86400));
      END LOOP;   
   END SLEEP;
END;
/
DISCONNECT;
EXIT;
/

Answer 1

First of all, is the trial tape in the bank? Because normally a precedent is a procedure that gets we say "stored in the bank" and you call for it to be executed. Or do you want to create Procedure through a java call? That didn’t understand 100%.

Let’s assume the process is in the bank. If it is, I will pass a call execution model here.

CallableStatement cs;
String retorno;
try {
    // Prepara a chamada
    cs = connection.prepareCall("{call P_GERA_LOG(?)}");

    // Registra um parametro
    cs.registerOutParameter(1, Types.VARCHAR);

    // passa o parametro
    cs.setString(1, "a string");  

    // executa a procedure
    cs.execute();

    // obtem o retorno
   retorno = cs.getString(1);
} catch (SQLException e) {
}

After your comment that you want to create a protocol through a call in Java, I will try to replicate here a code.

public void criarProcedure(Connection con) throws SQLException {

    Statement stmtCriarProcedure = null;

    // ...

    String queryCriaProcedure =
        "CREATE PROCEDURE NOME_PROCEDURE() " +
        "conteudo da procedure...";

    // ...

    try {
        System.out.println("Chamando CREATE PROCEDURE");
        stmtCriarProcedure = con.createStatement();
        stmtCriarProcedure.execute(queryCriaProcedure)
        // ...

    } catch (SQLException e) {
        // ... trata a exception da maneira que achar melhor
    } finally {
        if (stmtCriarProcedure != null) {
            stmtCriarProcedure.close();
        }
    }
}

Answer 2

The solution was to implement using Scriptrunner:

ScriptRunner sr = new ScriptRunner(conexão com BD);
Reader reader = new BufferedReader(new FileReader("aqui o arquivo xxx.sql"));
sr.setSendFullScript(true);
sr.setAutoCommit(true);
sr.setStopOnError(true);
sr.runScript(reader);
sr.closeConnection();