How to call procedure in JDBC?

DECLARE
  l_lang_id languages.lang_id%type;
BEGIN
  FOR lang IN (SELECT * FROM languages WHERE cv_id=1)
  LOOP
    INSERT INTO languages( languages, cv_id, mLanguage )
      VALUES( lang.languages, 21, lang.mLanguage )
      RETURNING lang_id INTO l_lang_id;

    INSERT INTO langProficiency(lang_id,speak,read,write) 
      SELECT l_lang_id, speak, read, write
        FROM langProficiency
       WHERE lang_id = lang.lang_id;
  END LOOP;
END;

Solution:

PL/SQL can be executed from JDBC using CallableStatement or PreparedStatement, like ordinary SQL 

Here is a simple example: 

CREATE TABLE TEST(
  X NUMBER,
  Y VARCHAR2(100)
);
 

public static void main(String[] args) throws SQLException {
        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setURL("jdbc:oracle:thin:test/test@localhost:1521:irko");
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);

        PreparedStatement preparedStatement = connection.prepareStatement(""
                + "DECLARE"
                + "  i PLS_INTEGER := 1;"
                + "BEGIN"
                + "   LOOP"
                + "      EXIT WHEN i > 10;"
                + "      INSERT INTO test VALUES ( i, 'TEST ' || i );"
                + "      i := i + 1;"
                + "   END LOOP;"
                + "END;");
        try {
            preparedStatement.execute();
            connection.commit();
        } finally {
            try{ preparedStatement.close(); } catch(Exception ex){};
            try{ connection.close(); } catch(Exception ex){};
        }
    }

You can bind values (use parameters) in your PL/SQL query:

PreparedStatement preparedStatement = connection.prepareStatement(""
                + "DECLARE"
                + "  i PLS_INTEGER := ? ;" // start value
                + "BEGIN"
                + "   LOOP"
                + "      EXIT WHEN i > ? ;" // end value
                + "      INSERT INTO test VALUES ( i, 'TEST ' || i );"
                + "      i := i + 1;"
                + "   END LOOP;"
                + "END;");
        try {
            preparedStatement.setInt(1, 10); // bind start value
            preparedStatement.setInt(2, 100); // bind end value
            preparedStatement.execute();
            connection.commit();
        } finally {
            try{ preparedStatement.close(); } catch(Exception ex){};
            try{ connection.close(); } catch(Exception ex){};
        }

Notes:
If the stored procedure is already there then better to use the CallableStatement directly. No need of writing the same stored procedure inside the connection.prepareStatement(). Directly use the CallableStatement with the stored procedure name.

CallableStatement cs1 = con.prepareCall("{call <Stored Proc Name>}"); 

Do you have a Java Problem?
Ask It in The Java Forum

Java Books
Java Certification, Programming, JavaBean and Object Oriented Reference Books

Return to : Java Programming Hints and Tips