PLSQL Types Creation

PLSQL Types Creation

create or replace type department_rec as object
  ( id number(4,0)
  , name varchar2(30)
  );

  create or replace type department_tab as table of department_rec;


create or replace
procedure check_deps
  ( p_deps in out department_tab )
is
begin
  if p_deps is null or p_deps.count = 0
  then
    return;
  end if;
 
  for i in p_deps.first..p_deps.last
  loop
    begin
      select department_name into p_deps(i).name
      from departments
      where department_id = p_deps(i).id;
    exception
      when no_data_found then
        null; -- Just skip it.
    end;
  end loop;
end;

==================

CREATE OR REPLACE TYPE emp_rec_type AS OBJECT (
    employee_id     NUMBER(18),
    first_name      VARCHAR2(30),
    last_name       VARCHAR2(30),
    middle_name     VARCHAR2(30),
    employee_type   VARCHAR2(30),
    location        VARCHAR2(30),
    addr_line1      VARCHAR2(30),
    addr_line2      VARCHAR2(30),
    addr_line3      VARCHAR2(30),
    city            VARCHAR2(30)
);


CREATE OR REPLACE TYPE emp_tbl_type AS TABLE OF emp_rec_type;


CREATE OR REPLACE PACKAGE emp_ddl_package AS
   PROCEDURE validate_emp_records (
      emp_rec_list        IN OUT NOCOPY emp_tbl_type,
      validation_status OUT NOCOPY VARCHAR2   
    );
END emp_ddl_package;

CREATE OR REPLACE PACKAGE BODY emp_ddl_package AS

  PROCEDURE validate_emp_records(
      emp_rec_list IN OUT NOCOPY emp_tbl_type,
      validation_status OUT NOCOPY VARCHAR2
  )IS 
  BEGIN
         dbms_output.put_line('Welcome to Validate_emp_records');
  END validate_emp_records;
END emp_ddl_package;

Oracle ADF Table Type

Oracle ADF Table Type

package model.service;

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import model.views.EmpVOImpl;
import model.views.EmpVORowImpl;
import java.util.List;
import oracle.jbo.domain.Struct;
import oracle.jbo.server.ApplicationModuleImpl;
import oracle.jbo.server.ViewLinkImpl;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jdbc.internal.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
//import oracle.sql.STRUCT;
import oracle.jbo.domain.Struct;

import oracle.jdbc.internal.OracleConnection;
// ---------------------------------------------------------------------
// ---    File generated by Oracle ADF Business Components Design Time.
// ---    Mon Nov 27 20:00:44 IST 2017
// ---    Custom code may be added to this class.
// ---    Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class AppModuleAMImpl extends ApplicationModuleImpl {
    /**
     * This is the default constructor (do not remove).
     */
    public AppModuleAMImpl() {
    }

    /**
     * Container's getter for Employees1.
     * @return Employees1
     */
    public ViewObjectImpl getEmployees1() {
        return (ViewObjectImpl) findViewObject("Employees1");
    }

    /**
     * Container's getter for Employees2.
     * @return Employees2
     */
    public ViewObjectImpl getEmployees2() {
        return (ViewObjectImpl) findViewObject("Employees2");
    }

    /**
     * Container's getter for Departments1.
     * @return Departments1
     */
    public ViewObjectImpl getDepartments1() {
        return (ViewObjectImpl) findViewObject("Departments1");
    }

    /**
     * Container's getter for EmpManagerFkVLink1.
     * @return EmpManagerFkVLink1
     */
    public ViewLinkImpl getEmpManagerFkVLink1() {
        return (ViewLinkImpl) findViewLink("EmpManagerFkVLink1");
    }
 
    public List<EmpVORowImpl> validateEmployees(List<EmpVORowImpl> empList) throws Exception {
      OracleCallableStatement stmt = null;
      ArrayList<EmpVORowImpl> empRespList = null;

      if (empList != null && empList.size() > 0) {
             empRespList = new ArrayList<EmpVORowImpl>();

        try {
          // Call the pl/sql API to determine invoice status
          String sql =
    "begin emp_ddl_package.validate_emp_records(emp_rec_list=>:1,validation_status=>:2); end;";

          stmt =
              (OracleCallableStatement)getDBTransaction().createCallableStatement(sql,0);
        // ArrayList<STRUCT> inputEmpList  = new ArrayList<STRUCT>();
        ArrayList<Struct> inputEmpList  = new ArrayList<Struct>();
         
    //StructDescriptor structDescriptor = StructDescriptor.createDescriptor("EMP_REC_TYPE",stmt.getConnection());
    Struct empRowStruct=null;
      for (int i = 0; i < empList.size(); i++) {
            EmpVORowImpl empRow = empList.get(i);
            Object[] o =
              new Object[] { empRow.getEmployeeId(), empRow.getFirstName(),
                             empRow.getLastName() };
                             //, empRow.getMiddleName(),
                            // empRow.getEmployeeType(), empRow.getLocation(),
                            // empRow.getAddrLine1(), empRow.getAddrLine2(),
                            // empRow.getAddrLine3(), empRow.getCity() };

            //STRUCT empRowStruct = new STRUCT(structDescriptor,stmt.getConnection(), o);
              empRowStruct = (oracle.jbo.domain.Struct)stmt.getConnection().createStruct("EMP_REC_TYPE", o);
            //STRUCT(structDescriptor,stmt.getConnection(), o);
            inputEmpList.add(empRowStruct);
          }
//          ArrayDescriptor empArray =
//          ArrayDescriptor.createDescriptor("EMP_TBL_TYPE",  stmt.getConnection());
//          ARRAY empArrayTbl = new ARRAY(empArray, stmt.getConnection(), inputEmpList.toArray());
         
            Array empArrayTbl= ((OracleConnection) stmt.getConnection()).createOracleArray("EMP_TBL_TYPE", empRowStruct);
                                //stmt.getConnection().createOracleArray
         
         
          java.sql.Array  paramListStruct = null;

          stmt.registerOutParameter(1, OracleTypes.ARRAY, "EMP_TBL_TYPE");
          stmt.setArray(1, empArrayTbl);
          //stmt.setARRAY(1, empArrayTbl);
          stmt.registerOutParameter(2, OracleTypes.VARCHAR, 0, 1);
          stmt.execute();

          //get the output table from pl/sql API and return
          Array outputArray = stmt.getArray(1);
          String returnStatus = stmt.getString(2);

          if (returnStatus != null && "Success".equals(returnStatus) &&
              outputArray != null) {
            ResultSet rs = outputArray.getResultSet();
            EmpVORowImpl tempRow;

            while (rs != null && rs.next()) {
              Struct empRespStruct = (Struct)rs.getObject(2);
              //Object[] empAttributes = empRespStruct.getAttributes();
                Object[] empAttributes = empRespStruct.getAttributeValues();
              if (empAttributes != null) {
                tempRow = (EmpVORowImpl)getEmp2().createRow();
              tempRow.setAttributeValues(Arrays.asList(tempRow.getAttributeNames()), Arrays.asList(empAttributes));
                empRespList.add(tempRow);
              }
            }
          }
        } catch (SQLException sqlex) {
          throw new Exception("sql exception");
        } catch (Exception ex) {
          throw new Exception("exception");
        } finally {
          try {
            stmt.close();
          } catch (SQLException sqlex) {
            throw new Exception("sql exception");
          }
        }
      }

      return empRespList;
    }

    /**
     * Container's getter for Emp1.
     * @return Emp1
     */
    public EmpVOImpl getEmp1() {
        return (EmpVOImpl) findViewObject("Emp1");
    }

    /**
     * Container's getter for Emp2.
     * @return Emp2
     */
    public EmpVOImpl getEmp2() {
        return (EmpVOImpl) findViewObject("Emp2");
    }

    /**
     * Container's getter for EmpManagerFk1VLink1.
     * @return EmpManagerFk1VLink1
     */
    public ViewLinkImpl getEmpManagerFk1VLink1() {
        return (ViewLinkImpl) findViewLink("EmpManagerFk1VLink1");
    }
}

Oracle ADF Email Validation

Oracle ADF Email Validation

public void emailTextValidation(FacesContext facesContext, UIComponent uIComponent, Object object) {

        if(object!=null){

            String name=object.toString();

            String expression="^[_A-Za-z0-9-]+(\\.[_A-Za-z0-9-]+)*@[A-Za-z0-9]+(\\.[A-Za-z0-9]+)*(\\.[A-Za-z]{2,})$";

            CharSequence inputStr=name;

            Pattern pattern=Pattern.compile(expression);

            Matcher matcher=pattern.matcher(inputStr);

            String msg="Email is not in Proper Format";

            if(!matcher.matches())  throw new ValidatorException(newFacesMessage(FacesMessage.SEVERITY_ERROR,msg,null));

            

        }

    }

Oracle ADF Current row selection after ExecuteQuery Or Refresh in Table

Oracle ADF Current row selection after ExecuteQuery Or Refresh in Table

     public BindingContainer getBindings() {

      return BindingContext.getCurrent().getCurrentBindingsEntry();

     }

    public void refreshButtonClick()
   {

     BindingContainer bindings = getBindings(); 

     DCIteratorBinding parentIter = (DCIteratorBinding)bindings.get("IteratorName"); 

     Key parentKey = parentIter.getCurrentRow().getKey();

    

     OperationBinding ob= bindings.getOperationBinding("Rollback");

     ob.execute();

     OperationBinding ob1= bindings.getOperationBinding("Execute");

     ob1.execute();

     // set the same row key as present row 

     parentIter.setCurrentRowWithKey(parentKey.toStringFormat(true));
   }

Oracle ADF how get Current row Value in Table:

Oracle ADF how get Current row Value in Table:

public class ExampleBean

{

 private Integer deptId;

 public void buttonClicked(ActionEvent ae)

 {

    Row currentDeptRow=(Row)ADFUtil.evaluateEL("#{bindings.DeptVO.currentRow}");

    ADFUtil.setEL("#{pageFlowScope.deptId}",currentDeptRow.getAttribute("Deptno"));

 }

 public void setDeptId(Integer deptId) {

   this.deptId=deptId;

 }

 public Integer getDeptId()

 {

    return deptId;

 }
}

JDeveloper 12c Inputtext Set, Get and Clear.

    

Click on this link for Step by Step Document: Sum of Two Numbers 

public String onAddClick() {

        System.out.println("*** Add Button Click ****");

        int tot=0;

        if (getValue1().getValue()!=null && getValue2().getValue()!=null) {

            int a= Integer.parseInt(getValue1().getValue().toString());

            int b= Integer.parseInt(getValue2().getValue().toString());

            tot=a+b;

            sumValue.setValue(tot);

            UIRefresh(sumValue);    

        }

        return null;

    }

   

    public String onClearClick() {

        System.out.println("*** Clear Button Click ****");

        value1.resetValue();

        value2.resetValue();

        sumValue.resetValue();

        UIRefresh(value1);

        UIRefresh(value2);

        UIRefresh(sumValue);

        return null;

    }

   

    public void UIRefresh(UIComponent obj) {

        AdfFacesContext adfFacesContext = AdfFacesContext.getCurrentInstance();

        adfFacesContext.addPartialTarget(obj); 

    }

JDeveloper 12c Sum of Two Numbers

Click on this link for Step by Step Document: Sum of Two Numbers 

Source Code:

    public String onAddClick() 
    {

        System.out.println("*** Add Button Click ****");

        int tot=0;

        if (getValue1().getValue()!=null && getValue2().getValue()!=null) {

            int a= Integer.parseInt(getValue1().getValue().toString());

            int b= Integer.parseInt(getValue2().getValue().toString());

            tot=a+b;

            sumValue.setValue(tot);

            UIRefresh(sumValue);    

        }

        return null;

    }

   

    public String onClearClick() 
    {

        System.out.println("*** Clear Button Click ****");

        value1.resetValue();

        value2.resetValue();

        sumValue.resetValue();

        UIRefresh(value1);

        UIRefresh(value2);

        UIRefresh(sumValue);

        return null;

    }

   

    public void UIRefresh(UIComponent obj) 
    {

        AdfFacesContext adfFacesContext = AdfFacesContext.getCurrentInstance();

        adfFacesContext.addPartialTarget(obj); 

    }