Wael Abdeen Blog
Oracle PL SQL || Oracle ADF

Hi

Many Oracle developers possess high capabilities in the world of PL SQL, try to find solutions of Business using Oracle PL SQL in the form of Stored Procedures, Functions, Packages or may be PL SQL Blocks..

in a word , Oracle Developers consider PL SQL a Corner Stone !!

today i give 2 simple examples to show how to use Oracle PL SQL in the Form of Stored Procedure and PL SQL Normal Block.

Notes

please use the below sequence and hints to build successfully PL SQL Block in ADF

  • use CallableStatement to initiate your block.
  • user question marks instead of all in-out parameters.
  • Register our parameter using Types class to show completable data type.
  • use set Datatype to pass values.
  • user get DataType to Retrieve Data after Execution.

Example 1 :

    public Number GetAdvertiseSeq(String ProjectID, Number ContractID, Number Typ) {
        Number _result = new Number(0);

        CallableStatement MyAuthMethod =
            getDBTransaction().createCallableStatement(“begin select nvl(max(advertise_no),0) + 1 into ? from prj_advertising_master where project_id = ? and contract_id = ? and advertise_type = ?; end;”,
                                                       getDBTransaction().DEFAULT);


        try {
            MyAuthMethod.registerOutParameter(1, Types.NUMERIC);
            MyAuthMethod.setString(2, ProjectID.toString());
            MyAuthMethod.setString(3, ContractID.toString());
            MyAuthMethod.setString(4, Typ.toString());
            MyAuthMethod.executeQuery();
            _result = new Number(MyAuthMethod.getInt(1));

        } catch (SQLException e) {
            _result = new Number(0);
        }

        return _result;
    }

Example 2 :

    public String ValidateBeforeAdvertiseorInvitation(String Project, Number Contract) {

        String _result = null;

        CallableStatement MyValidationMethod =
            getDBTransaction().createCallableStatement(“begin pm.VALIDATE_BEFORE_ADV_INV(?,?,?); end;”,
                                                       getDBTransaction().DEFAULT);
        try {
            MyValidationMethod.setString(1, Project);
            MyValidationMethod.setLong(2, Contract.longValue());
            MyValidationMethod.registerOutParameter(3, Types.VARCHAR);

            MyValidationMethod.executeQuery();
            _result = MyValidationMethod.getString(3);

        } catch (SQLException e) {
            _result = null;
        }
        return _result;
    }
}

Good Luck ?

  1. oracle-itself posted this