Oracle Applications - Data Migration / PL SQL

Data Migration and Interfaces
-----------------------------
1) Have you worked on data conversion? If so what interfaces are
you familiar? Explain?
Customer Interface
------------------
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
Order Interface
-----------------
OE_HEADERS_IFACE_ALL or OE_HEADERS_INTERFACE
OE_LINES_IFACE_ALL
OE_RESERVTNS_IFACE_ALL,
OE_CREDITS_IFACE_ALL
OE_ACTIONS_IFACE_ALL
AP
--
AP_INVOICES_INTERFACE
AP_INVOICES_LINES_INTERFACE
AR
---
RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL
RA_INTERFACE_ERRORS_ALL


2) Is it possible to load two tables using a single Control file and a single
data file having data in different formats?
recid dept no dept name
1 50 Manufacturing — DEPT record
recid emp num empname dept no
2 1119 Smith 50 — EMP record
2 1120 Snyder 50
1 60 Shipping
2 1121 Stevens 60

A record ID field distinguishes between the two formats. Department records
have a 1 in the first column, while employee records have a 2. The following
control file uses exact positioning to load this data:

INTO TABLE dept
WHEN recid = 1
(recid FILLER POSITION(1:1) INTEGER EXTERNAL,
deptno POSITION(3:4) INTEGER EXTERNAL,
dname POSITION(8:21) CHAR)
INTO TABLE emp
WHEN recid <> 1
(recid FILLER POSITION(1:1) INTEGER EXTERNAL,
empno POSITION(3:6) INTEGER EXTERNAL,
ename POSITION(8:17) CHAR,
deptno POSITION(19:20) INTEGER EXTERNAL)
Performance Tuning
--------------------

1) How will you approach tuning a sql query? When a Full table scan is preferred
over index scan?
For eg, a table has index on a particular field called region. If the number of
records returned for a particular region,is abt 3/4 of the table size, then
it is not advantageous to have a index scan.

2) Can you mention some of the performance tuning hints that you have used?
ALL_ROWS
FIRST_ROWS
RULE
ORDERED
FULL
INDEX



Additional questions
1) Can we return more than one value from a function?
2) Have you heard about recursive procedure?
3) can share us some info about locks? Is is possible to lock the rows
inside the pl/sql block?
4) What is a cursor variable?
5) What are pseudo columns?
6) what is an active data set?
7) what is the difference between normal for loop and cursor for loop?
8) Can we create a table inside the pl/sql block? How?
9) What is the use of instr function ?
10) What are lexical parameters in reports? How is XML publisher
advantageous over rdf reports?
11) How will you call a procedure inside a java program?
12) Where is business logic done in OAF application? BC4J -- Business Components
for Java
13) Which property decides the type of bean to be associated to a page or table?