Oracle Apps Interview Questions with Answers

This is a compilation of the most commonly asked 50 oracle apps interview questions and answers

Question 1 What is the difference between AD_BUGS & AD_APPLIED_PATCHES

Answer AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.
AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. “merged” patches), then they are considered distinct and this table will therefore hold 2 records

Question 2 What exactly happens when you put an Oracle Apps instance in maintenance mode?

Answer 2

Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications and system downtime for maintenance. Enabling the maintenance mode feature
(a) shuts down the Workflow Business Events System
(b) sets up function security so that no Oracle Applications functions are available to users.

Used only during AutoPatch sessions, maintenance mode ensures optimal performance and reduces downtime when applying a patch.

Question 3 What appsutil directory under Database ORACLE_HOME is used for?

Answer All the template files, startup scripts, XML files are maintained here.

Question 4. How to Create User in Oracle Applications? Can you delete a User?

Answer
New User can be created using the below navigation

security–>Define–>User menu.
No, the user cannot be deleted but can be end-dated.

Question 5. How frequent do you run the Gather Schema Statistics program & with what option ? Why do you need to run it? What is a cost-based optimizer?

Answer
Generally, Gather schema statistics should be run every week at least with 30% estimation to calculate statistics so that optimizer can choose a better plan for sql. Oracle cost-based optimizer is sql optimization  based on statistics of the table  and indexes

Question 6. How to defragment the table?

Answer By moving the table using  Alter table move and then rebuild all its index

Question 7. Which table stored the concurrent program definition?

Answer FND_CONCURRENT_PROGRAM and FND_CONCURRENT_PROGRAM_TL

Question 8. How do find the process which is taking the highest CPU from the Unix command?

Answer using top command

Question 9. How will you find Invalid Objects in the apps schema in the database?

Answer

col object_name format a50
col owner format a10
set lines 300
set pages 1000
select object_name, object_type, owner, status from dba_objects where status = 'INVALID' and owner='APPS';

Question 10  What is sub query and correlated subquery?

Answer

Sub Query

A Sub Query is a SELECT statement that is embedded in a clause of other SQL statements called the parent statement. A Subquery (Inner Query) returns a value that is used by the outer query. A Scalar sub query is a subquery that returns exactly one column value from one row.

Correlated Sub Query

A correlated subquery is used for row–by–row processing.  Each subquery is executed once for every row of the outer query. A correlated subquery is one way of reading (data) every row in a table and comparing values in each row against related data. Oracle server performs correlated subquery when the subquery references a column from a table in the parent query. The inner query is driven by the outer query in correlated subqueries .

Question 11. What are Oracle Joins, Set, and Subqueries

Answer
Oracle Joins
Union and Union All Sets Operator
Oracle Subqueries

Question 12 How duplicate rows are deleted?

Answer

Duplicate rows are deleted by using ROWID

Syntax 

delete from <Table>
Where ROWID not in (Select max (ROWID) from  <Table>
Group by <Column_name>);

Related articles

How to delete duplicate rows from a table

Question 13 What is Appstand Form.

Answer

Appstand form contains the following.

(1) Object Group STANDARD_PC_AND_VA.

Which contain the visual attribute and property class.

(2) Object group STANDARD_TOOLBAR which contains the windows

Canvasses blocks and items of application toolbar.

(3) Object group STANDARD_CALENDAR which contains the windows

Canvasses blocks and items of the application calendar.

(4) Object groups QUERY_FIND, which contains a window, blocks, and item

Used as a starting point for coding a find window.

Question 14  What is a set of books.

Answer

A financial reporting entity that uses a particular chart of accounts, functional currency and accounting calendar. You must define at least one set of books for each business location

Question 15

What is the Significance of the US Folder?
Answer

It is nothing but language specification by default it is in the American language. We can have multiple languages folders based on installed languages. from backend, we can get it from
FND_LANGUAGES — COL –INSTALLED_FLAG I, B, D

I–INSTALLED,
B–BASE,
D–DISABLE
select language_code,nls_language from fnd_languages where installed_flag like ‘B’

Question 16

What are the steps involved in adding a custom program to Oracle Applications?

Answer

(a) Develop a concurrent program or report.
(b) Identify the corresponding executable and register it with the application.
(c) Create a concurrent program and its parameters.
(d) Add a concurrent program to a request set.

Question 17 What are the primary underlying tables for concurrent processing

Answer

FND_CONCURRENT_PROGRAMS
FND_CONCURRENT__REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUES .

Question 18 What is the Diff between APPS Schema and other Schemas?
Answer

Apps schema contains only Synonyms we can’t create tables in apps schema, whereas other schemas contain tables, & all the objects. Here only we will create the tables and giving grants on created tables. Almost all every time we will connect to apps schema only

Question 19

Define  Oracle database Architecture in short?

Answer

First let take a look at instance, server, and database

Oracle Instance:	It is a means to access an Oracle database, always opens one and only one database, and consists of memory structures and background processes.
Oracle server:	It is a DBMS that provides an open, comprehensive, integrated approach to information management,Consists of an Instance and a database.
Oracle database:	It is a collection of data that is treated as a unit,Consists of Datafiles, Control files, Redo log files. (optional param file, password file, archived log)

Now Instance memory Structures:

System Global Area (SGA):

Allocated at instance startup, and is a fundamental component of an Oracle Instance.

SGA Memory structures:

Shared Pool	Consists of two key performance-related memory structures Library Cache and Data Dictionary Cache.
Library Cache	Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.
Data Dictionary Cache	Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.
Database Buffer Cache	Stores copies of data blocks that have been retrieved from the data files. Everything is done here.
Redo Log Buffer	Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.

User process:
Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.

Server process:
Connects to the Oracle Instance and is Started when a user establishes a session.
fulfills calls generated and returns results.
Each server process has its own nonshared PGA when the process is started.
Server Process Parses and runs SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA, and Return results in such a way that the application can process the information.
In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead.

Question 20. What are the .pls files which you see with apps?

Answer
pls file stands for plsql files. In apps patch, these files contain code to create package spec or package body, or both.

Question 21. What are .ldt & .lct files which you see in apps patch or with FNDLOAD?

Answer
.ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances

Question 22. What are .odf files in the apps patch?

Answer
odf stands for Object Description Files used to create tables & other database objects

Question 23. Where to find Form Server log files in R12/11i?

Answer
For 11i
Form Server Start up log file default location is $OAD_TOP/admin/log/$CONTEXT_NAME/f60svrm.txt
Forms Run Time Diagnostics default location is
$ORACLE_HOME/forms60/log/$CONTEXT_NAME
For R12 
Form Server Start up log file default location is
$INST_TOP/admin/log/$CONTEXT_NAME/  
Trace file variable can be found using
env|grep -i trace|grep -i forms

Question 24. How to convert pll to pld file or pld file to pll?

Answer

For 11i

Pll->Pld
 f60gen module=MSCOSCW3.pll module_type=library userid=apps/ module_access=file output_file=MSCOSCW1.pld script=yes
Pld -> pll
 f60gen module=MSCOSCW3.pld userid=apps/ module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

For R12

Pll->Pld 
frmcmp module=MSCOSCW3.pll module_type=library userid=apps/ module_access=file output_file=MSCOSCW1.pld script=yes
Pld -> pll 
frmcmp module=MSCOSCW3.pld userid=apps/ module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

Question 25. Is APPS_MRC Schema exists for MRC in 11.5.10 and higher?

Answer No, apps_mrc schema is dropped with 11.5.10 Upgrade & 11.5.10 new Install. This is replaced by more Integrated Architecture

Question 26. If APPS_MRC schema is not used in 11.5.10 and higher then How MRC is working?

Answer
For products like Payable, Receivables which uses MRC, and if MRC is enabled then each transaction table in base schema related to currency now has an associated MRC Sub tables.

Question 27. When you apply the C driver patch does it require a database to be Up & Why?

Answer
Yes, the database & DB listener should be Up when you apply any driver patch in apps. even if driver is not updating any database object connection is required to validate apps & other schema and to upload patch history information in database tables.

Question 28. Can C driver in apps patch create Invalid Object in the database?

Answer
No , the C driver only copies files in File System. Database Object might be invalidated during D driver when these objects are created/dropped/modified.

Question 29. Why does a worker fail in Oracle Apps Patch and few scenarios in which it failed for you ?

Answer
adpatch worker can fail in case it doesn’t find expected data, object, files or any thing which driver is trying to update/edit/modify. Possible symptoms may be underlying tables/objects are invalid, a prereq patch is missing, login information is incorrect, inconsistency in seeded data,locking scenario

Question 30. What is dev60cgi & f60cgi in R11i ?

Answer
CGI stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server. Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

Question 31. What is difference between mod_osso & mod_ose in Oracle HTTP Server ?

Answer.
mod_osso is Oracle Single Sign-On Module whereas mod_ose is a module for Oracle Servlet Engine.
mod_osso is module in Oracle’s HTTP Server that serves as a Conduit between Oracle Apache Server & Singl Sign-On Server whereas mod_ose is also another module in Oracle’s HTTP Server that serves as a conduit between Oracle Apache & Oracle Servlet Engine.

Question 32. Is Guest User a database user or application User?

Answer Guest User is an application User

Question 33. What is the difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms ?

Answer
Both the options will compile all the PL/SQL in the resultant .FMX, PLX, or.MMX file but COMPILE_ALL=YES also changes the cached version in the source .FMB, .PLL, or.MMB file. This confuses version control and builds tools (CVS, Subversion, make, scons); they believe you’ve made significant changes to the source. COMPILE_ALL=SPECIAL does not do this

Question 34. What is vmstat command in Unix?

Answer
(virtualmemory statistics) is a computer system monitoring tool that collects and displays summary information about OS memory, processes, interrupts, paging, and block I/O. Users of vmstat can specify a sampling interval that permits observing system activity in near-real time

Question 35. What is GSM in Oracle application E-Business Suite ?

Answer
GSM stands for Generic Service Management Framework. Oracle E-Business Suite consist of various components like Forms, Reports, Web Server, Workflow, Concurrent Manager ..
Earlier each service used to start at their own but managing these services (given that) they can be on various machines distributed across network. So Generic Service Management is extension of Concurrent Processing which manages all your services, provides fault tolerance (If some service is down ICM through FNDSM & other processes will try to start it even on a remote server) With GSM all services are centrally managed via this Framework.

Question 36. What is FNDSM?

Answer
FNDSM is an executable & core component in GSM ( Generic Service Management Framework discussed above). You start FNDSM services via APPS listener on all Nodes in Application Tier in E-Business Suite.

Question 37. What is responsibility?

Answer
It is collection of menus, request security groups and data groups
Menus: collection of forms is nothing but menus
Request security groups: a collection of programs.
Data groups: is a group of modules to be made accessible by the user through Responsibility

Question 38. How to write to a file through concurrent program.

Answer

By using FND_FILE package and it can be used only for log and output files.

FND_FILE.PUT	This is used to write text to a file without a new line character Procedure FND_FILE.PUT (which IN Number, Buff IN varchar2);- can be FND_FILE.LOG or FND_FILE.OUTPUT.
FND_FILE.PUT_LINE	This procedure as used to write a line of text to a file followed by a new line character.   Procedure FND_FILE.PUT_LINE (which IN number, buff IN varchar2); EX:- FND_FILE.PUT_LINE( FND_FILE.LOG, find_message_get);
FND_FILE.NEW_LINE	This procedure is used to write line terminators to a file procedure FND_FILE.NEW_LINE (which IN number LINES IN NATURAL:=1);Ex:- to write two newline characters to a log fileFnd_file.new_line (fnd_file.log,2);
FND_FILE.PUT_NAMES	this procedure is used to set the temporary log file and output filenames and the temporary directory to the user-specified values.   – This should be called before calling my other FND_FILE procedure and only once per a session.

Question 39. What is a Data Group?

Answer
-A data group is a group of oracle applications and the Oracle ID‟s of each application
– Oracle ID grants access privileges to tables in an Oracle Database
– Data group determines which Oracle Database accounts responsibilities forms, concurrent programs and reports connect to.

Question 40. What are different execution methods of executables

Answer
FlexRpt The execution file is written using the FlexReport API.
FlexSql The execution file is written using the FlexSql API.
Host The execution file is a host script.
Oracle Reports The execution file is an Oracle Reports file.
PL/SQL Stored Procedure The execution file is a stored procedure.
SQL Loader The execution file is a SQL script.
SQL Plus The execution file is a SQL Plus script.
SQL Report The execution file is a SQL Report script
Spawned The execution file is a C or Pro C program.
Immediate The execution file is a program written to run as a subroutine of the concurrent manager. We recommend against defining new immediate concurrent programs and suggest you use either a PL/SQL Stored Procedure or a Spawned C Program instead.

Question 41. What are security Attributes?

Answer
Security Attributes are used by Oracle self service web Applications to allow rows of data to be visible to specified users responsibilities based on the specific data contained in the row

Question 42. What is a Profile Option?

Answer
profile options are the set of changeable options that affect how the application looks and behaves.
– By setting profile options, the applications can be made to react in different ways for different users depending on the specific user attributes.

Question 43. What is meant by Request group?

Answer It is nothing but a collection of concurrent programs

Question 44. How to submit concurrent program through pl/sql

Answer
fnd_request.submit_request(parameters) by using this we can submit the concurrent program thru pl/sql.

FND_GLOBAL.APPS_INITIALIZE (user_id,resp_id, resp_appl_id)

Question 45. What is a template?

Answer
(a) The TEMPLATE form is the required starting point for all development of new Forms.
(b) The TEMPLATE form includes platform–independent attachments of several Libraries.

APPSCORE	It contains package and procedures that are required of all forms to support the MENUS,TOOLBARS
APPSDAYPK	It contains packages that control the oracle applications CALENDAR FEATURES.
FNDSQF	it contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.
CUSTOM	it allows extension of oracle applications forms without modification of oracle application code, you can use the custom library for customization such

Question 46. What is who column and how you set the values?

Answer
Who column are those which keep the history of record and these are :
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATED_DATE, LAST_UPDATE_LOGIN, and in Forms we have to use API ie. FND_STANDARD.SET_WHO in pre_insert and pre_update trigger to set it as default update for all the tables where the data is being inserted in table thru form.

Question 47. What are Flexfields?

Answer
A Flexfield is a customizable field that opens in a window from a regular Oracle Applications window. Defining flexfields enables you to tailor Oracle Applications to your own business needs. By using flexfields, we get following
(a) Structure certain identifiers required by oracle applications according to your own business environment.
(b) Collect and display additional information for your business as needed

Question 48. What are different types of flexfields?

Answer

Key Flexfields	You use key flexfields to define your own structure for many of the identifiers required by Oracle Applications. Profile – „Flexfields:Open Key Window‟ (FND_ID_FLEXS)
Descriptive Flexfield	You use descriptive flexfields to gather additional information about your business entities beyond the information required by Oracle Applications. Profile – Flexfields: Open Descr Window‟ (FND_DESCRIPTIVE_FLEXS)

Question 49. How to checks the concurrent request states from the backend?

Answer

A PL/SQL procedure can check the status of a concurrent request by calling.

FND_CONCURRENT.GET_REQUEST_STATUS	This function returns both user friendly (translatable) phase and status values as well as developer phase and status vales that can drive program logic       Syntax Function FND_CONCURRENT.GET_REQUEST_STATUS   ( request_id in out number,   application in varchar2 default null,   program in varchar2 default null,   phase out varchar2,   status out varchar,   dev_phase out varchar2,   dev_status out varchar2,   message out varchar2) return BOOLEAN;   –  when application and program are specified, the request_id of the last request for the specified program should be returned to request_id.
FND_REQUEST.WAIT_FOR_REQUEST	-This function waits for request completion, then returns the request phase/status and completion message to the caller.     –  Goes to sleep between checks for request completion     Syntax FND_CONCURRENT.WAIT_FOR_REQUEST   ( request_id in number default null,   interval in number default 60,   max_wait in number default 0,   phase out varchar2,   status out varchar2,   dev_phase out varchar2,   dev_status out varchar2,   message out varchar2) return BOOLEAN;
FND_CONCURRENT.SET_COMPLETION_STATUS	-this function should be called from a concurrent program to set its completion states.   –  This function returns TRUE on success, other wise FALSE.   Syntax  function FND_CONCURRENT.SET_COMPLETION_STATUS   ( status in varchar2,          message in varchar2) return BOOLEAN;

Question 50. Where do we use Custom.pll?

Answer. Custom.pll is generally used during the process of making customized or new oracle forms. We have to very careful while using it. One mistake can break the whole environment and everybody would be getting the errors while working on forms

I hop you like this compilation of Oracle Apps interview questions. Please do provide feedback on it. I will adding more questions to this post

Also Read
PLSQL Interview Questions
Oracle Apps DBA interview Questions
Oracle dba interview Questions
Oracle interview Questions
https://en.wikipedia.org/wiki/Oracle_Applications