Bulk  Mechanism

IT IS USED TO REDUCE THE TIME TAKEN TO RETREVE THE DATA

àBulk is one of the method to improve the performance of the applications

2 types:

                1:Bulk Collect

                2:Bulk binding

 1:Bulk Collect    In this clause we are used to fetch the data from database  into collection.

                            àselect ……into…clause

                            àcursor ….fetch…..statement

                            àdml ….returning

bulk collect practise

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

declare

type t1 is table of jacmel.jmplan_temp%rowtype index by binary_integer;

v1   t1;

begin

select * bulk collect into v1 from jacmel.jmplan_temp;

for i in v1.first..v1.last

loop

dbms_output.put_line(v1(i).SEGMENT1||','||v1(i).ATTR1||','||v1(i).CUSTOMER_NUMBER);

end loop;

end;

Example for bulk collect by 2 variables

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

DECLARE

type t1

IS

  TABLE OF VARCHAR2(50) INDEX BY binary_integer;

  v1 t1;

  v2 t1;

  CURSOR c1

  IS

    SELECT SEGMENT1,attr1 FROM jacmel.jmplan_temp;

BEGIN

  OPEN c1;

  FETCH c1 bulk collect INTO v1,v2;

  CLOSE c1;

  FOR i IN v1.first..v1.last

  LOOP

    dbms_output.put_line(v1(i)||','||v1(i));

  END LOOP;

END;

example with out bulk collect method

declare

vrow VARCHAR2(30);

cursor c1 is select distinct name from wsh_deliverable_trips_v;

z1 number(10);

z2 number(10);

begin

z1:=dbms_utility.get_time;

open c1;

loop

fetch c1 into vrow;

exit when c1%notfound;

end loop;

close c1;

z2:=dbms_utility.get_time;

dbms_output.put_line(z1);

dbms_output.put_line(z2);

dbms_output.put_line(z2-z1);

end;

/

example with bulk method

declare

type t1 is table of varchar2(30) index by binary_integer;

v1 t1;

cursor c1 is select distinct name from wsh_deliverable_trips_v;

z1 number(10);

z2 number(10);

begin

z1:=dbms_utility.get_time;

open c1;

loop

fetch c1 bulk collect into v1;

exit when c1%notfound;

end loop;

close c1;

z2:=dbms_utility.get_time;

dbms_output.put_line(z1);

dbms_output.put_line(z2);

dbms_output.put_line(z2-z1);

end;

/

Global Temporary Table

Global Temporary Tables (GTT)

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. From Oracle 8i onward, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

• Creation of Global Temporary Tables
• Global Temporary Tables and Undo
• Global Temporary Tables and Redo
• Miscellaneous Features

Related articles.

• Temporary Undo in Oracle Database 12c Release 1 (12.1)
• Session Sequences in Oracle Database 12c Release 1 (12.1)
• Session-Private Statistics for Global Temporary Tables in Oracle Database 12c Release 1 (12.1)
• Temporary Undo for Global Temporary Tables (GTTs) in Oracle 12c
• Global Temporary Tables (GTT) in Oracle 

Creation of Global Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.

The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction, or the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

-- Insert, but don't commit, then check contents of GTT.
INSERT INTO my_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM my_temp_table;

  COUNT(*)
----------
         1

SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM my_temp_table;

  COUNT(*)
----------
         0

SQL>

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert and commit, then check contents of GTT.
INSERT INTO my_temp_table VALUES (1, 'ONE');
COMMIT;

SELECT COUNT(*) FROM my_temp_table;

  COUNT(*)
----------
         1

SQL>

-- Reconnect and check contents of GTT.
CONN test/test

SELECT COUNT(*) FROM my_temp_table;

  COUNT(*)
----------
         0

SQL>

Global Temporary Tables and Undo

Although the data in a GTT is written to the temporary tablespace, the associated undo is still written to the normal undo tablespace, which is itself protected by redo, so using a GTT does not reduce undo and the redo associated with protecting the undo tablespace.

The following code creates a conventional table, populates it and checks the amount of undo used by the transaction.

DROP TABLE my_temp_table PURGE;

-- Create conventional table.
CREATE TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
);

-- Populate table.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

-- Check undo used by transaction.
SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 USED_UBLK  USED_UREC
---------- ----------
       302       6237

SQL>

We now repeat the previous test, but this time using a GTT.

DROP TABLE my_temp_table PURGE;

-- Create GTT.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Populate GTT.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

-- Check undo used by transaction.
SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 USED_UBLK  USED_UREC
---------- ----------
       303       6238

SQL>

TRUNCATE TABLE my_temp_table;

We can see, there is no significant difference in the undo used.

 Oracle 12c introduced the concept of Temporary Undo, allowing the undo for a GTT to be written to the temporary tablespace, thereby reducing undo and redo.

Global Temporary Tables and Redo

If you've read the previous section, you will already know the relationship between global temporary tables and redo. The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior to Oracle 12c, all undo associated with DML against a GTT is written to the normal undo tablespace, which is itself protected by redo. As a result, using a GTT reduces the amount of redo generation, but does not eliminate it. Another why of describing this is, using a GTT removes direct redo generation, but not indirect redo generation cause by undo.

The following code creates a conventional table, populates it and checks the amount of redo generated by the transaction.

DROP TABLE my_temp_table PURGE;

-- Create conventional table.
CREATE TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
);

SET AUTOTRACE ON STATISTICS;

-- Populate table.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
        106  recursive calls
      20119  db block gets
       2603  consistent gets
         16  physical reads
   23039396  redo size
        853  bytes sent via SQL*Net to client
        987  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

We now repeat the previous test, but this time using a GTT.

DROP TABLE my_temp_table PURGE;

-- Create GTT.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

SET AUTOTRACE ON STATISTICS;

-- Populate GTT.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         45  recursive calls
      15333  db block gets
       2381  consistent gets
         16  physical reads
    2944180  redo size
        862  bytes sent via SQL*Net to client
        987  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

TRUNCATE TABLE my_temp_table;

We can see we have created an order of magnitude less redo when using the GTT, but we have not eliminated it.

Miscellaneous Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
• There are a number of restrictions related to temporary tables but these are version specific.

For more information see:

• Temporary Tables
• CREATE TABLE - GLOBAL TEMPORARY
• Temporary Undo in Oracle Database 12c Release 1 (12.1)
• Session Sequences in Oracle Database 12c Release 1 (12.1)
• Session-Private Statistics for Global Temporary Tables in Oracle Database 12c Release 1 (12.1)
• Temporary Undo for Global Temporary Tables (GTTs) in Oracle 12c
• Global Temporary Tables (GTT) in Oracle 

Hope this helps. Regards Tim...

Multi - Org Concept in Oracle Apps R12

Multi - Org Concept in Oracle Apps R12

 Multi-Org in simple term means the implementation of multiple business units (or Organization) under a single installation of Oracle Applications. The concept of Multi-Org will manage the operations of an enterprise which has got subsidiaries across globe under a single oracle apps window, taking appropriate care of data security and data maintenance. Below are some of the features of multiple organization functionality.

• Any number of Business Units in an Enterprise can be supported within a single installation of Oracle Application

• User can access the data corresponding to and limited to the operating unit

• Reporting can be managed at different organization levels like, Business Group, Ledger, Operating unit etc

• Transactions like Procurement, Receiving, Selling, Shipping Etc. with the same Party Can be Performed through Different Organization and can be managed internally through inter company postings

A real time organization construct in R12

Here in this example construct, CCS Company has organization structure as follows

1. 1 Business Group - Which controls the organization in America and Australia
2. 2 Legal Entities - one in US and one in AU
3. 2 Primary Ledgers - one in US and one in AU
4. 2 Operating Units - one in US and one in AU
5. 3 Inventory Organizations – two in US and one in AU

How Organization Hierarchy flow in Oracle R12

Multi-Org and Multi-Org Access Control in R12 (MOAC)

Prior to R12, user has to switch between responsibilities to enter transaction and for doing other activities for a particular organization. This is very time consuming to do activities in an environment like this if you have 100 operating units. To overcome this factor, oracle has introduced a new feature in R12 which allow the user to switch the organization from the same responsibility which enables the user to access different organization and its data from a single responsibility. 

To achieve the new objective, Oracle has introduced new functionality called Multi-Org Access Control (MOAC) in release 12. Following are the set up steps needs to follow for implementing the MOAC architecture for a particular application

Multi – Org Setup Steps in R12

1. Define Location

Open HRMS Manager Responsibility and navigate to Work Structure à Location

Define your location Specific Details (BG Address and time zone) for your Business Group in ‘Address Details’ tab and Shipping details in ‘Shipping Details’ tab as below.

Save the Changes

Following table will hold location details

 HR_LOCATIONS_ALL  

2. Define Business Group

Open HRMS Manger responsibility and navigate to

Work StructureàOrganizationàDescription

Enter the business group name and assign the location created in the previous step and save the changes

Select the LOV as business group under the ‘Organization Classification’ block

Select ‘Business Group from the LOV and check ‘Enabled’ check box for the business group name and save changes

Now click on ‘Others’ button and select the Business Group info from the additional window and click OK, then another window will get opened with name ‘Additional Organizations Information’ like below

Press TAB and enter the mandatory details like below

Click OK Button, then you will be prompted to save the changes. Press on YES button. The details will get saved.

Following Table will hold business group information

HR_ALL_ORGANIZATION_UNIT

3. Create Legal Entity

As per the structure defined, we have to create the one legal entity for AU operation.

 Switch the responsibility to General Ledger and navigate to the following

 SetupàFinancialsàAccounting Setup ManageràAccounting Setups

 In Release R12, the legal entity setup is a part of accounting setup.

Press button ‘Create Legal Entity’ and enter the Details

Click on ‘Create New Address’ to create the new address

Click ‘APPLY’ to save the changes.

Legal Entity details will be available in the following table

 XLE_LE_OU_LEDGER_V

4. Define Ledger

Navigate to Accounting Setup Manager and define the New Ledger for the Legal Entity created

Ledger Details Will be available in the Following table

 GL_LEDGERS

5. Create and assign Operating units to Legal Entities to Ledger

In this step we will assign operating unit to Legal Entity. This will be defining from General Ledger responsibility

Navigate to Setup –> Financials –> Accounting Setup Manager –> Accounting Setups

In the Ledger Definition window, enter the Ledger name and press GO button. Once the search finishes and resulted with the Ledger name, Click on ‘Update Accounting Options’ button

Then Assign the Legal Entity to the ledger by clicking ‘Add Legal Entity’ button

Search for the legal entity created in our previous step and click on apply button to save the changes. Now the legal Entity is assigned to Ledger

Now click on Update button next to the operating unit setup option

Click on Add operating unit

Enter the details. Assign the business group and legal entity created from the above steps to the operating unit and click on apply button

After completing all ledger option press ‘Complete’ button to complete the accounting setup

6. Create Inventory Information

Switch to Human Resource responsibility and Navigate to the following

 Work Structure àOrganizationàDescription

 Click on New Button to create the new Inventory Organization

From the Organization Classifications Frame, select the option Inventory Organization from the LOV.

Press OK to save the Details

No click on ‘Others’ button and select ‘Accounting Information’ option. A small window will open, Press TAB to enter the details in this window

Assign the following to the inventory organization we have created in our previous steps

Primary Ledger

Legal Entity

Operating Unit

Click OK and Click Yes to save the changes

Again Click ‘Others’ button and select the ‘Inventory Information’ from the list

Then add all inventory details and save the changes.

Once the setup is done, run the following reports/programs to use the operating unit we created

In order to use the operating unit, run the following program and it should be run for all the new operating unit structure

Switch the responsibility to ‘System Administrator’

Program :- Replicate Seed Data

Parameter: - <Operating Unit>

Run the following program

Multi-Org Setup Validation Report

Now the operating units and other related setups are ready to use. Now we have to think how we can enable the multiple organization can be enabled from a single responsibility.

Enabling Multi – Org Access Control (MOAC)

MOAC is implemented in R12 to allow the users to submit requests and access data of different operating units in a single responsibility. This functionality can be done by setting the SECURITY PROFILES under HRMS module

There are 2 security profiles:

• SECURITY PROFILE:  is used for the selection of operating units from the same business group       
• GLOBAL SECURITY PROFILE: is used for the selection of operating units from the different business group

Set up of Multi-Org Access Control:

• Setup Security Profile in HRMS

Switch the responsibility to Human resource

Navigate to the following

Security àProfile

Select the name for the profile and attach the business group created

In order to have access to the security profile we created, we need to create a responsibility and assign this profile option to the responsibility

Switch to System Administrator responsibility and navigate to the following

SecurityàResponsibilityàDefine

Once the responsibility is defined, assign the new security profile option to the responsibility by navigating the following

Profile à System

This is it!!! 

We have done with the setup of Multi-Org.  This is a key functionality in Oracle R12 which is serving as the stepping stone to Oracle Multi-Org implementation.

Following SQL query can give the relation between Ledger, Legal entity and Operating Units in Oracle Apps R12

SELECT hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,

       lep.legal_entity_id, lep.NAME legal_entity,

       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,

       hrl.location_id,

       hrl.location_code,

       glev.FLEX_SEGMENT_VALUE

  FROM xle_entity_profiles lep,

       xle_registrations reg,

       hr_locations_all hrl,

       hz_parties hzp,

       fnd_territories_vl ter,

       hr_operating_units hro,

       hr_all_organization_units_tl hroutl_bg,

       hr_all_organization_units_tl hroutl_ou,

       hr_organization_units gloperatingunitseo,

       gl_legal_entities_bsvs glev

 WHERE lep.transacting_entity_flag = 'Y'

   AND lep.party_id = hzp.party_id

   AND lep.legal_entity_id = reg.source_id

   AND reg.source_table = 'XLE_ENTITY_PROFILES'

   AND hrl.location_id = reg.location_id

   AND reg.identifying_flag = 'Y'

   AND ter.territory_code = hrl.country

   AND lep.legal_entity_id = hro.default_legal_context_id

   AND gloperatingunitseo.organization_id = hro.organization_id

   AND hroutl_bg.organization_id = hro.business_group_id

   AND hroutl_ou.organization_id = hro.organization_id

   AND glev.legal_entity_id = lep.legal_entity_id

**Please feel free to provide your valuable inputs, questions and concerns on this topic as comments**

Important SQL queries:
The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them.

SELECT   psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
FROM     PER_SECURITY_PROFILES psp,
         PER_SECURITY_ORGANIZATIONS pso,
         HR_OPERATING_UNITS hou
WHERE    pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
         AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;