This is a compilation of most commonly asked 50 oracle apps interview questions and answers
Question 1 What is 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 ?
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 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 ?
New User can be created using
No , user cannot be deleted but can be end-dated.
Question 5. How frequent you run Gather Schema Statistics program & with what option ? Why you need to run it ? What is cost based optimizer ?
Generally Gather schema statistics should be run every week at least with 30% estimation to calculate statistics so that optimizer can choose 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 to find the process which is taking highest CPU from Unix command?
Answer using top command
Question 9. How will you find Invalid Objects in apps schema in database ?
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 sub query?
|Sub Query||A Sub Query is a SELECT statement that is embedded in a clause of other SQL statements called the parent statement. |
A Sub query (Inner Query) returns a value that is used by the outer query.
A Scalar sub query is a sub query that returns exactly one column value from one row.
|Correlated Sub Query||Correlated sub query are used for row – by –row processing. |
Each sub query is executed once for every row of the outer query. A correlated sub query is one way of reading (data) every row in a table and comparing values in each row against related data.Oracle server performs correlated sub query when the sub query references a column from a table in the parent query.
The inner query is driven by the outer query in correlated sub queries.
Question 11. What are Oracle Joins, Set and Subqueries
Question 12 How duplicate rows are deleted?
Duplicate rows are deleted by using ROWID
delete from <Table> Where ROWID not in (Select max (ROWID) from <Table> Group by <Column_name>);
Question 13 What is Appstand Form.
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 item of application toolbar.
(3) Object group STANDARD_CALENDAR which contains the windows
Canvasses blocks and item of 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 set of books.
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
What is the Significance of US Folder?
It is nothing but language specification by default it is in 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
select language_code,nls_language from fnd_languages where installed_flag like ‘B’
What are the steps involved in adding a custom program to Oracle Applications?
(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
Question 18 What is the Diff between APPS Schema and other Schemas?
Apps schema contains only Synonyms we can’t create tables in apps schema, where as other schemas contains 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
Define Oracle database Architecture in short?
First let take a look at instance ,server and database
|Oracle Instance:||It isa means to access an Oracle database,always opens one and only one database and consists of memory structures and background process.|
|Oracle server:||It is aa 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, passwd 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 datafiles. Everything 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.|
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.
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 run 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 is .pls files which you see with apps ?
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 ?
.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 file in apps patch ?
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?
Form Server Start up log file default location is $OAD_TOP/admin/log/$CONTEXT_NAME/f60svrm.txt
Forms Run Time Diagnostics default location is
Form Server Start up log file default location is
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?
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
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 ?
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 C driver patch does it require database to be Up & Why ?
Yes , 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 database ?
No , 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 fails in Oracle Apps Patch and few scenarios in which it failed for you ?
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 ?
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 ?
mod_osso is Oracle Single Sign-On Module where as mod_ose is module for Oracle Servlet Engine.
mod_osso is module in Oracle’s HTTP Server serves as Conduit between Oracle Apache Server & Singl Sign-On Server where as mod_ose is also another module in Oracle’s HTTP Server serves as 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 difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms ?
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 build 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 ?
(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 which permits observing system activity in near-real time
Question 35. What is GSM in Oracle application E-Business Suite ?
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 , provide fault tolerance (If some service is down ICM through FNDSM & other processes will try to start it even on remote server) With GSM all services are centrally managed via this Framework.
Question 36. What is FNDSM ?
FNDSM is 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?
It is collection of menus, request security groups and data groups
Menus: collection of forms is nothing but menus
Request security groups: 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.
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 with out 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 as 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?
-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 Data base accounts a responsibilities forms, concurrent programs and reports connect to.
Question 40. What are different execution methods of executables
FlexRpt The execution file is wrnitten 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?
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?
profile options are the set of changeable options that affects 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 collection of concurrent programs
Question 44. How to submit concurrent program through pl/sql
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 template?
(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 CALENDER FEATURES.|
|FNDSQF||it contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.|
|CUSTOM||it allows extension of oracle applications forms with out 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?
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?
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 type of flexfields?
|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 beyong 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?
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 |
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)
|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