Oracle Apps Technical Interview Questions
SQL:
- Difference between materialized view and normal view
- Union and union all difference - union removes duplicate records union all doesn’t
- Can we create a view if table is not present?
-IT
will throw error Table or view does not exist
- WHO columns significance ?
Created_by - User Id.
Created_date - SYSDATE.
Last_update_date - SYSDATE.
Last_updated_by - User ID.
Last_update_login - Login ID.
- Triggers
Row trigger
- Row trigger fires for each row , let's say an update statement update 50 rows trigger will be fired 50 times
Statement Triggers
Before and after
trigger
Instead of trigger
- Mutating Table Error
-When a trigger is getting fired on specific table and When we are
trying to modify the same table within the trigger logic , mutating table
will occurs
- Grants , performance Tuning
- Rank and Dense rank
- What is global temporary table ? (DECLARE GLOBAL TEMPORARY TABLE)
TRUNCATE statement is issued against a temporary table, only the
session specific data is truncated.
Data in temporary tables is automatically deleted at the end of
the database session, even if it ends abnormally.
id NUMBER,
description VARCHAR2(20)
)
ON COMMIT DELETE ROWS;
- Select * from per_all_people_f where rownum=10; will it return value or go into error ? Ans: No rows return
- Any arithmetic operation with null value results into null ; e.g. Select 5+null from dual;
PLSQL
- Function
- Procedures
- Exceptions
- Temporary tables ( temporary global table )
- Collection ( V array , Associative array /index by array , nested array )
- Bulk collect (How to use nested array in bulk collect) ? Benefit of bulk collect?
- Commit in exception
- Pragma types
- Query processing
- What is API what are common or default in and out parameters in oracle provided API?
- Significance of P_API_VERION_NUMBER in API
- IN/ OUT parameter
- Difference between varchar and vharcha2
- Run concurrent program through plsql
- AuthID in oracle package
If table A is in schema apps and package xyz is created in
schema apps, if we execute xyz.xyz_1 from schema scott it will be
executed successfully even though Scott doesn’t have synonym of table A
CREATE OR REPLACE PACKAGE XYZ
AUTHID DEFINER
AS
PROCEDURE XYZ_1;
END XYZ;
CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;
EXECTUE BLEOW FROM SCOTT
begin
xyz.XYZ_1;
end;
Below behaves opposite and it will throw error
CREATE OR REPLACE PACKAGE XYZ
AUTHID CURRENT_USER
AS
PROCEDURE XYZ_1;
END XYZ;
CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;
EXECUTE FROM SCOTT
begin
xyz.XYZ_1;
end;
Cursor
- Types of cursor ( implicit/explicit)
- Attributes of cursor ( %found , %Notfound , %isopen , %Rowcount )
- Ref Cursor
- UTL_FILE
AOL
- Types of value set / what is $FLEX$
- None ii) Independent iii) Dependent iv) Table v) Translated Independent vi) translated dependent
Vii ) special viii) Pair
- What are steps to register java concurrent program
- What are different types of concurrent program in oracle?
OAF
- MVC Architecture
- How do we move personalization's from one instance to other
- What is EO , VO , CO , AM
- Can we extend AM
- How to extend VO, CO
- How to check which co is to be extended
- What is association object (AO)
- Ways to pass values from one page to other
- Profile options to enable OAF personalization links
AME
- What is AME
- Sequence of configuration
- Can we dynamic approval groups without customizing workflow (Yes)
- Can we test AME configuration? How to test AME configuration ?
Workflow
- Can we trigger a workflow from PLSQL
- Parameters to be passed to function when called from the workflow (itemtype , itemkey , actid, funcmode, resulttype )
- Steps to customize workflow
- Access levels
Alerts
- Types of alerts
- Testing of alerts
- Alert how to fetch new and old value in alert
- Can we run the alert manually ??? How ??
- How to send an email notification alert to multiple users (distribution list)
Reports
- Have you created XML reports, XML Bursting , what are different ways to create xml output in oracle application?
Few ways are given in below URL
- Report Triggers
- Difference between after parameter and before report
- How to display a message in the log file ( SRW_ message ( number, 'msg'))
- User Exit
- P_CONC_REQUEST_ID
- Coolum's in report (formula column , place holder columns)
- Mapping between the data template and concurrent program
- If a plsql procedure is called through conc program what are the parameters ( errbuff , retcode)
- Significance of errbuff , retcode
- How to link two query groups together in RDF which have common value ??
Interfaces/ Conversions/API:
- Difference between interface ,conversions and API
Interfaces
- Interface is periodic activity
- It may be daily or weekly or monthly or quarterly or Yearly
- Both Legacy system and oracle system are active.
Conversion
1. Conversion is one time activity.
2. All the data will be transferred into oracle systems one time.
3. Legacy system will not be after conversion process.
Forms:
- Which top is the forms placed
- Custom.pll
- From trace
- Triggers
- Post command (Caching for particular session )
- Zoom trigger
- Which triggers are automatically created when we create master detail from
On-Populate-Details
On-Check-Delete-Master
- What are limitations of custom.pll and form personlizations
- How to use date tracking functionality and what package is used to enable date track functionality in oracle apps.
- Using form personalization I want to get a popup message based on the responsibility I am logged in with , example india resposnlility I should get message 'HI India' , with US responsibility I should get message 'Hi America' how to do this with personlizoation ?
How to get
message in different languages in this case???
-Using fnd_profile.value
- How static data which is entered in forms and not yet saved , can be validated when there are multiple line in master details form
Let's say we master detail form and we have 10 lines in
detail form and we need total of all these lines at header level without saving
the form , it can be done by taking a extra field in either control block or
header block where in property option we have calculation option which allows
to create summery filed
- How to do one personalization which will behave differently at responsibility level
HRMS
- How security profile works?
- How to rehire employee ?
- How to terminate employee?
- What are types of assignment's in oracle ?
- How to terminate assignment ? Primary vs secondary assignments ?
- Payroll processing ?
- Element entries ?
- Payroll types ?
- Assignment types ?
- Date tracking https://blogs.oracle.com/manojmadhusoodanan/entry/date_tracking_in_oracle_hrms
Update modes
CORRECTION : Over
writes the data. No history will maintain.
UPDATE : Keeps the
history and new change will effect as of effective date
UPDATE_CHANGE_INSERT :
Inserts the record and preserves the future
UPDATE_OVERRIDE :
Inserts the record and overrides the future
Delete modes
ZAP : wipes all records
DELETE : Deletes
current record
FUTURE_CHANGE : Deletes
current and future changes.
DELETE_NEXT_CHANGE :
Deletes next change
-Date track mode
- Difference types of update mode ? https://blogs.oracle.com/manojmadhusoodanan/entry/date_tracking_in_oracle_hrms
-API for deletion , insert , update employee, assignment data
what is the significance of P_VALIDATE parameter in API
12. What is object version number
13. Difference Between EIT and SIT
Which one is faster ?
Can I store
SSHR request information in EIT?
