How to call procedure in JDBC

[Farakh khan]

Hello,

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;

I want to call above Oracle PL/SQL from Java JDBC. Any help please

Best regards

[Ireneusz Kordal]

Hi,

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){};
        }

[Farakh khan]

Thanks from the bottom of my heart

Best regards

[Soumyajit Hazra]

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>}"); 

Check the link Call Stored Procedure from Java

[Farakh khan]

Soumyajit Hazra wrote: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>}"); 

Check the link Call Stored Procedure from Java

Very very helpful

Appreciated. I am grateful to you

Best regards

[Ravi Kiran Va]

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

Cant this thing can be done using a normal Statement ??

Statement statement = connection.createStatement();
statement.executeUpdate(
                + "DECLARE"
                + "  i PLS_INTEGER := 1;"
                + "BEGIN"
                + "   LOOP"
                + "      EXIT WHEN i > 10;"
                + "      INSERT INTO test VALUES ( i, 'TEST ' || i );"
                + "      i := i + 1;"
                + "   END LOOP;"
                + "END;");

[Martin Vajsar]

Ravi Kiran Pattu wrote:
Cant this thing can be done using a normal Statement ??

Yes, it definitely can be run using a Statement. However if it would be used over and over again with just the differences in literal values, then by using the PreparedStatement or CallableStatment you get the benefit of:

1) Sql injection prevention (no arbitrary strings concatenated directly into your SQL code), and

2) faster processing and higher scalability (reusing the PreparedStatement with different values of bind parameters generally saves on database processing by skipping the parse phase, which can be resource consuming and non-scalable in Oracle).