Collections

SET SERVEROUTPUT ON;

DECLARE
  -- Define a varray with a maximum of 3 rows.
TYPE integer_varray IS VARRAY(3) OF INTEGER;
-- Declare the varray with null values.
varray_integer INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
BEGIN
  -- Print initialized null values.
  dbms_output.put_line('Varray initialized as nulls.');
  dbms_output.put_line('––––––––––––––--------------');
  FOR i IN 1..3
  LOOP
    dbms_output.put ('Integer Varray ['||i||'] ');
    dbms_output.put_line('['||varray_integer(i)||']');
  END LOOP;
  -- Assign values to subscripted members of the varray.
  varray_integer(1) := 11;
  varray_integer(2) := 12;
  varray_integer(3) := 13;
  -- Print initialized null values.
  DBMS_OUTPUT.PUT (CHR(10));
  -- Visual line break. dbms_output.put_line('Varray initialized as values.');
  dbms_output.put_line('––-––––––––––––--------------');
  FOR i IN 1..3
  LOOP
    dbms_output.put_line('Integer Varray ['||i||'] '|| '['||varray_integer(i)||']');
  END LOOP;
END;
/


SET SERVEROUTPUT ON;
DECLARE
  -- Define a varray of integer with 3 rows.
TYPE INTEGER_VARRAY IS VARRAY(3) OF INTEGER;
-- Declare an array initialized as a no-element collection.
varray_integer INTEGER_VARRAY := integer_varray();
BEGIN
  -- Allocate space as you increment the index.
  FOR i IN 1..3
  LOOP
    varray_integer.EXTEND;       -- Allocates space in the collection.
    varray_integer(i) := 10 + i; -- Assigns a value to the indexed value.
  END LOOP;
  -- Print initialized array.
  dbms_output.put_line('Varray initialized as values.');
  dbms_output.put_line('––––--------------––––––––––-');
  FOR i IN 1..3
  LOOP
    dbms_output.put ('Integer Varray ['||i||'] ');
    dbms_output.put_line('['||varray_integer(i)||']');
  END LOOP;
END;
/

--CREATE OR REPLACE TYPE TYPE_NAME AS {VARRAY | VARYING ARRAY} (SIZE_LIMIT) OF element_type [ NOT NULL ];

-- This is in create_varray3.sql on the publisher's web site.

CREATE OR REPLACE TYPE integer_varray AS VARRAY(3) OF INTEGER;
/

set serveroutput on;
DECLARE
  varray_integer INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
BEGIN
  -- Assign values to replace the null values.
  FOR i IN 1..3
  LOOP
    varray_integer(i) := 10 + i;
  END LOOP;
  -- Print the initialized values.
  dbms_output.put_line('Varray initialized as values.');
  dbms_output.put_line('––––––––––––––---------------');
  FOR i IN 1..3
  LOOP
    dbms_output.put ('Integer Varray ['||i||'] ');
    dbms_output.put_line('['||varray_integer(i)||']');
  END LOOP;
END;
/


CREATE OR REPLACE TYPE integer_varray AS VARRAY(100) OF INTEGER NOT NULL;
/

set serveroutput on;
DECLARE
varray_integer INTEGER_VARRAY := integer_varray();
BEGIN
FOR I IN 1..VARRAY_INTEGER.LIMIT
LOOP
varray_integer.EXTEND;
END LOOP;
dbms_output.put ('Integer Varray Initialized ');
dbms_output.put_line('['||varray_integer.COUNT||']');
END;
/

----------------------------------------------------------


CREATE OR REPLACE TYPE strings AS VARRAY(3) OF VARCHAR2(30 CHAR);
/


CREATE TABLE individuals
( individual_id INTEGER NOT NULL
, first_name VARCHAR2(30 CHAR) NOT NULL
, middle_name VARCHAR2(30 CHAR)
, last_name VARCHAR2(30 CHAR) NOT NULL
, title VARCHAR2(10 CHAR)
, CONSTRAINT INDIV_PK PRIMARY KEY(INDIVIDUAL_ID)
);

CREATE TABLE addresses
( address_id INTEGER NOT NULL
, INDIVIDUAL_ID INTEGER NOT NULL
, street_address STRINGS NOT NULL  -- use of collection datatype
, city VARCHAR2(20 CHAR) NOT NULL
, state VARCHAR2(20 CHAR) NOT NULL
, postal_code VARCHAR2(20 CHAR) NOT NULL
, country_code VARCHAR2(10 CHAR) NOT NULL
, CONSTRAINT addr_pk PRIMARY KEY(address_id)
, CONSTRAINT ADDR_INDIV_FK FOREIGN KEY(INDIVIDUAL_ID)
REFERENCES INDIVIDUALS (INDIVIDUAL_ID)
);

select * from INDIVIDUALS;

select * from ADDRESSES;

-- This is in varray_dml1.sql on the publisher's web site.
INSERT INTO individuals VALUES
( 1, 'John', 'Sidney', 'McCain', 'Mr.');


INSERT INTO addresses VALUES
( 1
, 1
, strings
('Office of Senator McCain','450 West Paseo Redondo','Suite 200')
,'Tucson'
,'AZ'
,'85701'
,'USA'
);

SELECT STREET_ADDRESS FROM addresses;


-- Create a PL/SQL table datatype.
CREATE OR REPLACE TYPE varray_nested_table IS TABLE OF VARCHAR2(30 CHAR);

-- Use SQL*Plus to format the output.
COL COLUMN_VALUE FORMAT A30 -- Print formatted elements from aggregate table.
;

--select statement
SELECT nested.column_value
FROM addresses a
, TABLE(CAST(A.STREET_ADDRESS AS VARRAY_NESTED_TABLE)) NESTED
WHERE address_id = 1;

--update statement
UPDATE addresses
SET street_address =
strings('Office of Senator McCain'
,'2400 E. Arizona Biltmore Cir.'
,'Suite 1150')
WHERE address_id = 1;


-- This is in varray_dml3.sql on the publisher's web site.
DECLARE
TYPE address_type
IS
  RECORD
  (
    address_id    INTEGER ,
    individual_id INTEGER ,
    street_address STRINGS ,
    city         VARCHAR2(20 CHAR) ,
    state        VARCHAR2(20 CHAR) ,
    postal_code  VARCHAR2(20 CHAR) ,
    COUNTRY_CODE VARCHAR2(10 CHAR) );
  address ADDRESS_TYPE;
  CURSOR get_street_address (address_id_in INTEGER)
  IS
    SELECT * FROM addresses WHERE ADDRESS_ID = ADDRESS_ID_IN;
BEGIN
  -- Access the cursor.
  OPEN get_street_address(1);
  FETCH get_street_address INTO address;
  CLOSE GET_STREET_ADDRESS;
  -- Reset the first element of the varray type variable.
  ADDRESS.STREET_ADDRESS(1) := 'Office of Senator John McCain';
  -- Update the entire varray column value.
  UPDATE addresses
  SET street_address = address.street_address
  WHERE address_id   = 1;
END;
/

-- This is in varray_dml4.sql on the publisher's web site.
INSERT INTO individuals VALUES (2, 'John', 'Sidney', 'McCain', 'Mr.');

INSERT INTO addresses VALUES
( 2
, 2
, strings('Office of Senator Kennedy')
, 'Boston'
, 'MA'
, '02203'
, 'USA'
);



-- This is in varray_dml4.sql on the publisher's web site.
DECLARE
TYPE address_type IS RECORD
( address_id INTEGER
, individual_id INTEGER
, street_address STRINGS
, city VARCHAR2(20 CHAR)
, state VARCHAR2(20 CHAR)
, postal_code VARCHAR2(20 CHAR)
, COUNTRY_CODE VARCHAR2(10 CHAR)
);

ADDRESS ADDRESS_TYPE;
-- Define a cursor to return the %ROWTYPE value.

CURSOR get_street_address
(address_id_in INTEGER) IS
SELECT *
FROM addresses
WHERE address_id = address_id_in;
BEGIN
-- Access the cursor.
OPEN get_street_address(2);
FETCH get_street_address INTO address;
CLOSE get_street_address;
-- Add elements.
address.street_address.EXTEND(2);
address.street_address(2) := 'JFK Building';
address.street_address(3) := 'Suite 2400';
-- Update the varray column value.
UPDATE addresses
SET street_address = address.street_address
WHERE address_id = 2;
END;
/

using java.util.Enumeration in OAF

package xxhr.oracle.apps.per.probation.webui;

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.per.selfservice.deployperson.webui.AssignmentCO;

import java.util.Enumeration ;

/**
 * Controller for ...
 */
public class XXHRPerProAssignCO extends AssignmentCO
{
  public static final String RCS_ID="$Header$";
  public static final boolean RCS_ID_RECORDED =
        VersionInfo.recordClassVersion(RCS_ID, "%packagename%");

  /**
   * Layout and page setup logic for a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
  public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);

    if(pageContext.isLoggingEnabled(2)){
          pageContext.writeDiagnostics(this, "XXHR Start of debug", 2);
    }

 /** Code Start **/

 String parameterName;
 Enumeration e = pageContext.getParameterNames();
 while(e.hasMoreElements())
 {
  parameterName = (String)e.nextElement();
  if(pageContext.isLoggingEnabled(2)){
  pageContext.writeDiagnostics(this, "Parameter Name=>" + parameterName + " Parameter Value=> " + pageContext.getParameter(parameterName),2);
  }

 }

/*http://appsoracle-abhi.blogspot.com/2016/07/oaf.code.using-javautilenumeration-in-oaf.html*/

How to use formula in webadi (Sample of using formula's in WebADI layout)

Sample of using formula's in WebADI layout:

There are requirements when we want to create a sequence in webadi layout based without using actual sequence, as sequence creation at row level is difficult, and generally done using pre-import step (could not do it because of issue i faced), there is a workaround.

While defining the layout, just provide below text while selecting column type as formula
anything in [] will represent the reference columns, an example is below

=IF([INVOICE_NUMBER]<>"",ROW()-10,"")

Creating webadi using Application Desktop Integrator with synchronous plsql based importer

Creating webadi using Application Desktop Integrator with synchronous pl-sql based importer

Creating webadi using Application Desktop Integrator with synchronous plsql based importer

Creating webadi using Application Desktop Integrator with synchronous pl-sql based importer

Importer based WebADI

This discuss and shows how to create end to end webadi with importer.

Oracle EBS R12.2.4 VM Template for local EBS installation -setting networks

Oracle EBS R12.2.4 VM Template for local EBS installation

https://blogs.oracle.com/stevenChan/entry/ebs_12_2_4_available

However once we install, we have issues in accessing instance once we shut down, as ip keeps on changing on host machine, so the ifconfig will have different entry every time, so instance will not work.

I have crated a video tutorial to explain and fix it. Its my first video post so please excuse..comments are most welcome..