Enterprise Data Manager Guide

6.5 HotFix 1
- 6.5.1
- 6.5
- 6.4.4
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Select Clause for Non-Partitioned Source Tables

If all tables in the interim table are not partitioned in the source, the archive job uses the ALL_TABLES Oracle view to populate the interim table values. Note that the partition name column will not include any values.

The select clause you configure depends on if the tables have the same table owner or multiple table owners. You can add multiple tables for each table owner.

Single Table Owner Select Clause

Use the following syntax to form the select clause for the table name when the interim tables have the same table owner:

SELECT A.owner, A.table_name 
FROM all_tables A 
WHERE A.owner='<table owner>' AND A.table_name IN ('<table name>')

For example, you want to add the following values in the interim table:

Table Owner	Table Name
GL	GL_IMPORT_REFERENCES

Use the following syntax:

SELECT A.owner, A.table_name 
FROM all_tables A 
WHERE A.owner='GL' AND A.table_name IN ('GL_IMPORT_REFERENCES')

Multiple Table Owners Select Clause

Use one select statement for each table owner. Use a UNION statement to join multiple select statements.

Use the following syntax to form the select clause for multiple select statements when the interim tables have different table owners:

SELECT A.owner, A.table_name, null, 'Y', null, null 
FROM all_tables A
WHERE A.owner='<table owner 1>' AND A.table_name IN ('<table 1 name>', '<table 2 name>')
UNION 
SELECT B.owner, B.table_name, null, 'Y', null, null 
FROM all_tables B
B.owner='<table owner 2>' AND B.table_name IN ('<table 1 name>', '<table 2 name>')
UNION 
SELECT C.owner, C.table_name, null, 'Y', null, null 
FROM all_tables C
C.owner='<table owner 3>' AND C.table_name IN ('<table 1 name>', '<table 2 name>')

For example, you want to add the following values in the interim table:

Table Owner	Table Name
GL	GL_IMPORT_REFERENCES GL_JE_LINES
XLA	XLA_AE_HEADERS XLA_AE_LINES
APPLSYS	WF_ITEMS WF_ITEM_ATTRIBUTE_VALUES

Use the following syntax:

SELECT A.owner, A.table_name, null, 'Y', null, null 
FROM all_tables A
WHERE A.owner='GL' AND A.table_name IN ('GL_IMPORT_REFERENCES', 'GL_JE_LINES')
UNION 
SELECT B.owner, B.table_name, null, 'Y', null, null 
FROM all_tables B
B.owner='XLA' AND B.table_name IN ('XLA_AE_HEADERS', 'XLA_AE_LINES')
UNION 
SELECT C.owner, C.table_name, null, 'Y', null, null 
FROM all_tables C
C.owner='APPLSYS' AND C.table_name IN ('WF_ITEMS', 'WF_ITEM_ATTRIBUTE_VALUES')

Rename Saved Search

Table of Contents

Enterprise Data Manager Guide

Enterprise Data Manager Guide

Select Clause for Non-Partitioned Source Tables

Select Clause for Non-Partitioned Source Tables

Single Table Owner Select Clause

Multiple Table Owners Select Clause