Vrácení hodnoty output parametru Oracle procedury do Javy

🎙 Poslední aktualizace článku 6.8.2017. Pokud najdete nějaké rozdíly v aktuálním stavu, dejte vědět v komentářích, díky.

Potřeboval jsem vyřešit poněkud složitější SQL update o kterým by ostatním stačilo vědět co mají mít jako vstup a co by jim mohlo být vráceno. Na tenhle případ se úplně vybízela uložená procedura v Oraclu, které se ale u nás ve firmě zatím netěší velké oblibeně.

Nezbývalo než být průkopníkem a pohledat řešení jak z Javy Oracle SP zavolat a jak si poradit s čtením výstupního parametru.

Řešení není až tak složité, pokud poskládáte ty správné příkazy dohromady.

Základní kameny

1. prepareCall – který nám vytvoření statement do kterého se dají registrovat výstupní parametry, což je zásadní benefit oproti klasickému prepareStatement
2. vstupní parametry si napojíme přes setString, setInt atp..  s dvěmi parametry
   1. název vstupního (IN) parametru v Oracle SP
   2. hodnota předaná v parametru
3. výstupní parametry se registrují pomocí registerOutParameter a opět budeme potřebovat dva parametry:
   1. název výstupního (OUT) parametru v Oracle SP
   2. datový typ který očekáváme z třídy java.sql.Types
4. celé to odpálíme pomocí execute()
5. vystupní parametry si přečteme z naplněného statementu pomocí getInt, getString atp.. pouze s jedním parametrem, který je názvem výstupního parametru

package com.company;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Types;
import java.sql.DriverManager;

public class Main {

    public static void main(String[] args) {

        Connection connection = null;

        try {
            connection = DriverManager.getConnection("jdbc:oracle:thin:oracleip:sid", "user", "pswd");
            CallableStatement cstmt = connection.prepareCall("{call INTEGRATION.deact_items_if_missing_in_st(?, ?)}");
            cstmt.setString("p_entity_type", "VProductSalesData");
            cstmt.registerOutParameter("po_affected_item", Types.INTEGER);
            cstmt.execute();
            int value = cstmt.getInt("po_affected_item");
            System.out.println("Value is " + value);
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        } finally {
            if (connection := null) {
                 connection.close();
            }
        }
    }
}

Uložená procedura v Oraclu pak může vypadat nějak takto:

PROCEDURE deact_matsales_if_not_in_st(p_entity_type varchar, po_affected_items OUT INT) 
IS 
BEGIN 
    Update V_VPRODUCTSALESDATA ... ; 
    po_affected_items := SQL%ROWCOUNT; 
END deact_matsales_if_not_in_st;