Software Engineering

Object Types / Tables in ADF 12c Using Domains (Part 2 of 2)

Welcome back or welcome at all! This is the second and last part of how to work with Oracle PL/SQL object types and object tables in ADF. If you missed the first part, click here 🙂

In this part you will learn how to receive and send an object table from respectively to the database. The object table consists of an object type. Therefore,  domain objects and stored procedures are created and used.

Requirement

Step By Step

Set Up the Test Project

  • we will extend the project from the first article
  • if you don’t want to read the first article, here are the steps to quickly set up the things we need for this article (if you need an explanation, read the first article :))
    • create a new application
      • ADF Fusion Web Application
      • connection to the database with HR schema and user
      • an open SQL worksheet with user SYS as SYSDBA
    • create the object type (execute the following script in the SQL worksheet)

    • create the domain for the object type
      • right click on Module -> New -> From Gallery… -> Business Tier -> ADF Business Components -> Domain
      • choose your database connection and click ok
      • check Domain for an Oracle Object Type and select  EMPLOYEE_WITH_JOB_TITLE  from Available Types and click next
      • select the attribute EmployeeId and change Type to BigDecimal (you can have a look at the other attributes, but they should be fine)
    • create an application module with generated application module class
      • add the following method

Create the Object Table

  • create the object table that consists of the object type

  • check your newly created object table

 

Receive the Object Table

  • create a stored procedure that creates an instance of the object table, fills it with values and returns it as an output parameter

    • this procedure gets all employees form the HR.EMPLOYEES table and creates for each employee an HR.EMPLOYEE_WITH_JOB_TITLE and stores this in the output parameter
    • the job title is received from the HR.JOBS table
  • call this procedure inside the application module with help of this method

    • instead of using numbers for accessing the parameters of the procedure, this time we are using names
      • note: you cannot mix between numbers and names
  • create the helper method transformArrayToList(…)
    • note: it is not possible to generate a working domain out of an object table -> that’s why we transform the result to a List

    • this method is using transformStructToDomain(…) from the article before (you also find this method in this article under Set Up the Test Project)
  • now add public method to the client interface of your application module
    • AppModuleAM.xml -> Java -> Edit application module client interface
    • move the method to the right side
  • the Java section of your AppModuleAM.xml should now look like this
  • run the application module and execute your new method

Send the Object Table

  • create a new stored procedure with the object table as input parameter (in this case also as an output parameter)

    • this procedure iterates over the input table and queries the HR.EMPLOYEES table accordingly
  • call this procedure inside the application module with the following method

    • an object table is an oracle.jdbc.OracleTypes.ARRAY, so this is generated with the help of the method createARRAY(…)
      • this method takes the definition of the object type the object table consists of and the values as an simple array
    • find the helper method getObjectTableFromIds(…) in the following

  • like before, add the method to the client interface, execute the method and see the results

Be Nice to People Around You

  • it makes the world a better place

Do you want to know how to use entity objects and view objects with object types? Follow this blog and read the upcoming article Object Types in ADF 12c Using View And Entity Objects.

> Object Types / Tables in ADF 12c Using Domains (Part 1 of 2)