ORACLE Technical Interview Questions

What should be the return type for a cursor variable. Can we use a scalar data type as return type?
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE

What are different Oracle database objects?
-TABLES
-VIEWS
-INDEXES
-SYNONYMS
-SEQUENCES
-TABLESPACES etc

What is difference between SUBSTR and INSTR?
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE INSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

Display the number value in Words?
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- ----------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like, Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- -----------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.

What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

What are various joins used while writing SUBQUERIES?
Self join-Its a join foreign key of a table references the same table. Outer Join--Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.
Equi-join--Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

What a SELECT FOR UPDATE cursor represent.?
SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT]
The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.

What are various privileges that a user can grant to another user?
-SELECT
-CONNECT
-RESOURCES

Display the records between two range?
select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);

minvalue.sql Select the Nth lowest value from a table?
select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name')
group by level;
Example:
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second lowest salary:
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level

What is difference between Rename and Alias?
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

Difference between an implicit & an explicit cursor.?
only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

What is a OUTER JOIN?
Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.

What is a cursor?
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

What is the purpose of a cluster?
Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.

What is OCI. What are its uses?
Oracle Call Interface is a method of accesing database from a 3GL program. Uses--No precompiler is required,PL/SQL blocks are executed like other DML statements.
The OCI library provides
--functions to parse SQL statemets
--bind input variables
--bind output variables
--execute statements
--fetch the results

How you open and close a cursor variable. Why it is required?
OPEN cursor variable FOR SELECT...Statement
CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

Display Odd/ Even number of records?
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Output:-
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
Output:-
2
4
6

What are various constraints used in SQL?
-NULL
-NOT NULL
-CHECK
-DEFAULT

Can cursor variables be stored in PL/SQL tables. If yes how. If not why?
No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.

Difference between NO DATA FOUND and %NOTFOUND?
NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

Can you use a commit statement within a database trigger?
No

What WHERE CURRENT OF clause does in a cursor?
LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
FHKO;;;;;;;;;SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;

There is a string 120000 12 0 .125 , how you will find the position of the decimal place?
INSTR('120000 12 0 .125',1,'.')
output 13

What are different modes of parameters used in functions and procedures?
-IN -OUT -INOUT

How you were passing cursor variables in PL/SQL 2.2?
In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2, the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

When do you use WHERE clause and when do you use HAVING clause?
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

Difference between procedure and function.?
Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

ORACLE Technical Interview Questions

What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

What are the components of a PL/SQL Block ?
Declarative part, Executable part and Exception part.
Datatypes PL/SQL

What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.
e.g.. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;

What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS??

a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.

What are the components of a PL/SQL block ?
A set of related declarations and procedural statements is called block.

What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION is always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.

What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are :
I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type );
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.

What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?
Procedures and Functions,Packages and Database Triggers.

What are the advantages of having a Package ?
Increased functionality (for example,global package variables can be declared and used by any proecdure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)

What are the uses of Database Trigger ?
Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

What is a Procedure ?
A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

What is a Package ?
A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.

What is difference between Procedures and Functions ?
A Function returns a value to the caller where as a Procedure does not.

What is Database Trigger ?
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in, update to, or delete from a table.

Can the default values be assigned to actual parameters?
Yes

Can a primary key contain more than one columns?
Yes

What is an UTL_FILE.What are different procedures and functions associated with it?
UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.

What are ORACLE PRECOMPILERS?
Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,... This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql statements into calls to the precompiler runtime library. The output must be compiled and linked with this library to creator an executable.

Differentiate between TRUNCATE and DELETE?
TRUNCATE deletes much faster than DELETE
TRUNCATE
DELETE
It is a DDL statement
It is a DML statement
It is a one way trip, cannot ROLLBACK
One can Rollback
Doesn't have selective features (where clause)
Has
Doesn't fire database triggers
Does
It requires disabling of referential constraints.

What is difference between a formal and an actual parameter?
The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

ORACLE Technical Interview Questions

What is a Check Box?
A Check Box is a two state control that indicates whether a certain condition or value is on or off, true or false. The display state of a check box is always either "checked" or "unchecked".

What is a canvas-view?
A canvas-view is the background object on which you layout the interface items (text-items, check boxes, radio groups, and so on.) and boilerplate objects that operators see and interact with as they run your form. At run-time, operators can see only those items that have been assigned to a specific canvas. Each canvas, in term, must be displayed in a specific window.

Explain the following file extension related to library?
.pll,.lib,.pld
The library pll files is a portable design file comparable to an fmb form file
The library lib file is a plat form specific, generated library file comparable to a fmx form file
The pld file is Txt format file and can be used for source controlling your library files Parameter

Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.

Explain the two type of Cursors ?
There are two types of cursors, Implicit Cursor and Explicit Cursor. PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.

What is Fine Grained Auditing? (for DBA)
Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL. Look at this example:
o Add policy on table with autiting condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
o Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
analyze table EMP compute statistics;
select * from EMP where c1 = 11; -- Will trigger auditing
select * from EMP where c1 = 09; -- No auditing
o Now we can see the statments that triggered the auditing condition...
select sqltext from sys.fga_log$;
delete from sys.fga_log$;

What is a package ? What are the advantages of packages ? What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

What is a Virtual Private Database? (for DBA)
Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it. VPD configuration is done via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.

What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

What is Oracle Label Security? (for DBA)
Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle8i to implement row level security. Access to rows are restricted according to a user's security sensitivity tag or label. Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;

What is OEM (Oracle Enterprise Manager)? (for DBA)
OEM is a set of systems management tools provided by Oracle Corporation for managing the Oracle environment. It provides tools to monitor the Oracle environment and automate tasks (both one-time and repetitive in nature) to take database administration a step closer to "Lights Out" management.

Question What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

What are the components of OEM? (for DBA)
Oracle Enterprise Manager (OEM) has the following components:
. Management Server (OMS): Middle tier server that handles communication with the intelligent agents. The OEM Console connects to the management server to monitor and configure the Oracle enterprise.
. Console: This is a graphical interface from where one can schedule jobs, events, and monitor the database. The console can be opened from a Windows workstation, Unix XTerm (oemapp command) or Web browser session (oem_webstage).
. Intelligent Agent (OIA): The OIA runs on the target database and takes care of the execution of jobs and events scheduled through the Console.

What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned Boolean value.

What are the modes of parameters that can be passed to a procedure ?
IN,OUT,IN-OUT parameters.

Where the Pre_defined_exceptions are stored ?
In the standard package.
Procedures, Functions & Packages ;

Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integarity Constraints.?

I & ii.

Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;

Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');

What are advantages fo Stored Procedures?
Extensibility,Modularity, Reusability, Maintainability and one time compilation.

What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE

ORACLE Technical Interview Questions

What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

What are the components of logical database structure of Oracle database?
There are tablespaces and database's schema objects.

What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

Explain the relationship among database, tablespace and data file ?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

What is schema?
A schema is collection of database objects of a user.

What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

Can objects of the same schema reside in different tablespaces?
Yes.

Can a tablespace hold objects from different schemes?
Yes.

What is Oracle table?
A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

What is an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

What is Partial Backup ?
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

What is Mirrored on-line Redo Log ?

A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

What is Full Backup ?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.

Can a View based on another View ?
Yes.

Can a Tablespace hold objects from different Schemes ?
Yes.

Can objects of the same Schema reside in different tablespace ?
Yes.

What is the use of Control File ?
When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Do View contain Data ?
Views do not contain or store data.

What are the Referential actions supported by FOREIGN KEY integrity constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

What are the type of Synonyms?
There are two types of Synonyms Private and Public.

What is a Redo Log ?
The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

What is an Index Segment ?
Each Index has an Index segment that stores all of its data.

Explain the relationship among Database, Tablespace and Data file?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

What are the different type of Segments ?
Data Segment, Index Segment, Rollback Segment and Temporary Segment.

What are Clusters ?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

What is an Integrity Constrains ?
An integrity constraint is a declarative way to define a business rule for a column of a table.

What is an Index ?
An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

What is an Extent ?
An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

What is a View ?
A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

What is Table ?
A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

ORACLE Technical Interview Questions

What is an Lov?
A list of values is a single or multi column selection list displayed in a pop-up window

What is a record Group?
A record group is an internal oracle forms data structure that has a similar column/row frame work to a database table

Give built-in routine related to a record groups?
Create_group (Function)
Create_group_from_query(Function)
Delete_group(Procedure)
Add_group_column(Function)
Add_group_row(Procedure)
Delete_group_row(Procedure)
Populate_group(Function)
Populate_group_with_query(Function)
Set_group_Char_cell(procedure)

List the built-in routines for the controlling canvas views during run-time?
Find_canvas
Get-Canvas_property
Get_view_property
Hide_View
Replace_content_view
Scroll_view
Set_canvas_property
Set_view_property
Show_view
Alert

System.effective_date system variable is read only True/False?
False

What are the built_in used to trapping errors in forms 4?
Error_type return character
Error_code return number
Error_text return char
Dbms_error_code return no.
Dbms_error_text return char

What is Oracle Financials? (for DBA)
Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:
Oracle Assets: Ensures that an organization's property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.
Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.
Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.
Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.
Oracle Payables: Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.
Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.
Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.
Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.
Oracle Revenue Accounting Gives an organization timely and accurate revenue and flexible commissions reporting.
Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What are the design facilities available in forms 4.0?
Default Block facility.
Layout Editor.
Menu Editor.
Object Lists.
Property Sheets.
PL/SQL Editor.
Tables Columns Browser.
Built-ins Browser.

What is the most important module in Oracle Financials? (for DBA)
The General Ledger (GL) module is the basis for all other Oracle Financial modules. All other modules provide information to it. If you implement Oracle Financials, you should switch your current GL system first.GL is relatively easy to implement. You should go live with it first to give your implementation team a chance to be familiar with Oracle Financials.

What are the types of canvas-views?
Content View, Stacked View.

What is the MultiOrg and what is it used for? (for DBA)
MultiOrg or Multiple Organizations Architecture allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications. This keeps each operating unit's transaction data separate and secure.
Use the following query to determine if MuliOrg is intalled:
select multi_org_flag from fnd_product_groups;

What is the difference between Fields and FlexFields? (for DBA)
A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.

Explain types of Block in forms4.0?
Base table Blocks.
Control Blocks.
1. A base table block is one that is associated with a specific database table or view.
2. A control block is a block that is not associated with a database table. ITEMS

What is an Alert?
An alert is a modal window that displays a message notifies the operator of some application condition

What are the built-in routines is available in forms 4.0 to create and manipulate a parameter list?
Add_parameter
Create_Parameter_list
Delete_parameter
Destroy_parameter_list
Get_parameter_attr
Get_parameter_list
set_parameter_attr

What is a record Group?
A record group is an internal oracle forms data structure that has a similar column/row frame work to a database table

What is a Navigable item?
A navigable item is one that operators can navigate to with the keyboard during default navigation, or that Oracle forms can navigate to by executing a navigational built-in procedure.

What is a library in Forms 4.0?
A library is a collection of Pl/SQL program units, including user named procedures, functions & packages

How image_items can be populate to field in forms 4.0?
A fetch from a long raw database column PL/Sql assignment to executing the read_image_file built_in procedure to get an image from the file system.

What is the content view and stacked view?
A content view is the "Base" view that occupies the entire content pane of the window in which it is displayed. A stacked view differs from a content canvas view in that it is not the base view for the window to which it is assigned

columns.

1.

ORACLE Technical Interview Questions

What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

What are the components of logical database structure of Oracle database?
There are tablespaces and database's schema objects.

What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

Explain the relationship among database, tablespace and data file ?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

What is schema?
A schema is collection of database objects of a user.

What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

Can objects of the same schema reside in different tablespaces?
Yes.

Can a tablespace hold objects from different schemes?
Yes.

What is Oracle table?
A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

What is an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

What is Partial Backup ?
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

What is Mirrored on-line Redo Log ?

A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

What is Full Backup ?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.

Can a View based on another View ?
Yes.

Can a Tablespace hold objects from different Schemes ?
Yes.

Can objects of the same Schema reside in different tablespace ?
Yes.

What is the use of Control File ?
When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Do View contain Data ?
Views do not contain or store data.

What are the Referential actions supported by FOREIGN KEY integrity constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

What are the type of Synonyms?
There are two types of Synonyms Private and Public.

What is a Redo Log ?
The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

What is an Index Segment ?
Each Index has an Index segment that stores all of its data.

Explain the relationship among Database, Tablespace and Data file?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

What are the different type of Segments ?
Data Segment, Index Segment, Rollback Segment and Temporary Segment.

What are Clusters ?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

What is an Integrity Constrains ?
An integrity constraint is a declarative way to define a business rule for a column of a table.

What is an Index ?
An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

What is an Extent ?
An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

What is a View ?
A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

What is Table ?
A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

Oracle Apps interview questions

Question: You are told that the certain steps in the Oracle Apps Form/Screen are running slow, and you are asked to tune it. How do you go about it.
Answer: First thing to do is to enable trace. Preferably, enable the trace with Bind Variables. This can be done by selecting menu Help/Diagnostics/Trace/”Trace With Binds and Wait”
Internally Oracle Forms issues a statement similar to below:-
alter session set events='10046 trace name context forever, level 12' ;

Enable Trace with Bind Variables in Apps

This will enable the trace with Bind Variable values being shown in the trace file.
The screen in Oracle Apps will also provide the name of the trace file which is located in directly identified by
select value from v$parameter where name like '%us%r%dump%'
Doing a tkprof with explain plan option, reviewing plans and stats in trace file can help identify the slow performing SQL.




Question: What is the difference between running Gather Stats and “Program – Optimizer[RGOPTM]” in Oracle General Ledger?
Answer: “Gather Stats” will simply gather the stats against existing tables, indexes etc. However Gather Stats does not create any new indexes. But “Program – Optimizer[RGOPTM]” can create indexes on GL_CODE_COMBINATIONS, provided accounting segment has the indexed flag enabled,


Question: You have written a piece of code in POR_CUSTOM_PKG for Oracle iProcurement, but its not taking any effect? What may be the reason?
Answer: Depending upon which procedure in POR_CUSTOM_PKG has been programmed, one or more of the below profile options must be set to Yes
POR: Enable Req Header Customization
POR: Enable Requisition Line Customization
POR: Enable Req Distribution Customization


Question: What is the key benefit of punching out to suppliers catalogs rather than loading their catalogs locally in Oracle iProcurement?
Answer: Punchout has several advantages like, Catalogs don’t need to be loaded locally saves space on your system. You can get up-to-date list of catalogs by punching out and also you get the benefit of up-to-date pricing information on vendor items.

Question: Does oracle have a test environment on exchange?
Answer: http://testexchange.oracle.com

Question: Does Oracle Grants use its own schema or does it uses Oracle Project Accounting schema?
Answer: Although Oracle Grants has its own schema i.e. GMS, it reuses many of the tables with in Oracle Projects Schema like PA_PROJECTS_ALL, PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURE_TYPES etc.


Question: How to make an Oracle Report Type concurrent program produce an excel friendly output?
Answer: Comma can be concatenated between the column values, however a better option is to create tab delimited file, as it takes care of commas within the string.
For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;

Question: What are the settings needed for printing bitmap reports?
Answer: Get your DBA to configure two files i.e. uiprint.txt & default.ppd
For details, refer to Metalink Note 189708.1

Question: For a PL/SQL based concurrent program do you have to issue a commit at the end?
Answer: The concurrent program runs within its own new session. In APPS, the default database setting enforces a commit at the end of each session. Hence no explicit COMMIT is required.

Question: What is the best way to add debugging to the code in apps?
Answer: Use fnd_log.string , i.e. FND Logging. Behind the scenes Oracles FND Logging uses autonomous transaction to insert records in a table named fnd_log_messages.
For example
DECLARE
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => 'xxxx ' || 'pkg/procedurename '
,message => 'your debug message here');
END ;
Three profile options effecting FND Logging are
FND: Debug Log Mode
FND: Debug Log Enabled
FND: Debug Log Module

Question: If you wish to trigger of an update or insert in bespoke table or take some action in response to a TCA record being created or modified, how would you do it? Will you write a database triggers on TCA Tables?
Answer: There are various pre-defined Events that are invoked from the Oracle TCA API’s.
TCA was Oracle’s first initiative towards a fully API based approach, which means the screen and the processes all use the same set of APIs for doing same task.
In order to take an action when these events occur, you can subscribe a custom PL/SQL procedure or a Custom Workflow to these events. Some of the important TCA events are listed below:-
oracle.apps.ar.hz.ContactPoint.update
oracle.apps.ar.hz.CustAccount.create
oracle.apps.ar.hz.CustAccount.update
oracle.apps.ar.hz.CustAcctSite.create
oracle.apps.ar.hz.CustAcctSite.update
oracle.apps.ar.hz.CustAcctSiteUse.create
oracle.apps.ar.hz.CustAcctSiteUse.update
oracle.apps.ar.hz.Location.create
oracle.apps.ar.hz.Location.update
oracle.apps.ar.hz.Organization.create
oracle.apps.ar.hz.Organization.update
oracle.apps.ar.hz.PartySite.create
oracle.apps.ar.hz.PartySite.update
oracle.apps.ar.hz.PartySiteUse.create
oracle.apps.ar.hz.PartySiteUse.update
oracle.apps.ar.hz.Person.create
oracle.apps.ar.hz.Person.update

Question: In Oracle OA Framework, is the MDS page/document definition stored in database or in the file system?
Answer: The MDS document details are loaded into database, in the following sets of tables.
JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS
The Document is loaded via XMLImporter, as detailed in XMLImporter Article

Question: In a Oracle Report data group, you have a “data link” between two queries. How do you ensure that the data link is made Outer Joined?
Answer: The data link is an Outer Join by default.

Question: How does substitution work in OA Framework?
What are the benefits of using Substitution in OA Framework?
Answer: Based on the user that has logged into OA Framework, MDS defines the context of the logged in user. Based upon this logged in context, all applicable personalization are applied by MDS. Given that substitutions are loaded as site level personalizations, MDS applies the substituted BC4J objects along with the personalizations. The above listed steps occur as soon as Root Application module has been loaded.
The benefit of using Substitution is to extend the OA Framework without customization of the underlying code. This is of great help during Upgrades. Entity Objects and Validation Objects can be substituted. I think Root AM’s can’t be substituted given that substitution kicks off after Root AM gets loaded.

Question: In OA Framework, once your application has been extended by substitutions, is it possible to revert back to remove those substitutions?
Answer: yes, by setting profile option “Disable Self-Service Personal%” to Yes, keeping in mind that all your personalizations will get disabled by this profile option. This profile is also very useful when debugging your OA Framework based application in the event of some error. By disabling the personalization via profile, you can isolate the error, i.e. is being caused by your extension/substitution code or by Oracle’s standard functionality.

Question: How can you import invoices into Oracle Receivables?
Answer: You can either use AutoInvoice by populating tables RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL & RA_INTERFACE_SALESCREDITS_ALL.
Alternately you may decide to use API ar_invoice_api_pub.create_single_invoice for Receivables Invoice Import.

Question: How do you setup a context sensitive flexfield
Answer: Note: I will publish a white paper to sho step by step approach.
But for the purpose of your interview, a brief explanation is…a)Create a reference field, b) Use that reference field in “Context Field” section of DFF Segment screen c) For each possible value of the context field, you will need to create one record in section “Context Field Value” ( beneath the global data elements).

Question: Does Oracle iProcurement use same tables as Oracle Purchasing?
Answer: Yes, iProcurement uses the same set of requisition tables as are used by Core Purchasing.

Question: What is the name of the schema for tables in tca
Answer: AR (at least till 11.5.10, not sure about 11.5.10).

Question: Are suppliers a part of TCA?
Answer: Unfortunately not yet. However, Release 12 will be merging Suppliers into TCA.

Question: What is the link between order management and purchasing
Answer: Internal Requisitions get translated into Internal Sales Orders.

Question: How would you know if the purchase order XML has been transmitted to vendor, looking at the tables.
Answer: The XML delivery status can be found from a table named ecx_oxta_logmsg. Use the query below
SELECT edoc.document_number
,decode(eol.result_code, 1000, 'Success', 'Failure') AS status
,eol.result_text
FROM ecx_oxta_logmsg eol
,ecx_doclogs edoc
,ecx_outbound_logs eog
WHERE edoc.msgid = eol.sender_message_id
AND eog.out_msgid = edoc.msgid
ORDER BY edoc.document_number

Question: You have done forms personalization, now how will you move it from one environment to another?
Answer: Use FNDLOAD. For examples visit FNDLOAD Article


Question: What are the key benefits of forms personalization over custom.pll?
Answer:
-->Multiple users can develop forms personalization at any given point in time.
-->It is fairly easy to enable and disable forms personalizations.
-->A programmer is not required to do simple things such as hide/disable fields or buttons.
-->Provides more visibility on customizations to the screen.

Question: Tell me some limitations of forms personalization when compared to CUSTOM.pll?
Answer:
-->Can't create record group queries, hence can’t implement LOV Query changes.
-->Can't make things interactive, i.e. can’t have a message box that gives multiple choices for example Proceed or Stop etc.

Question: Give me one example where apps uses partitioning?
Answer: WF_LOCAL_ROLES


Question: Give me one example of securing attributes in iProcurement.
Answer: You can define Realm to bundle suppliers into a Category. Such realm can then be assigned to the User using Define User Screen. Security Attribute ICX_POR_REALM_ID can be used. By doing so, the user will only be made visible those Punchout suppliers that belong to the realm against their securing attributes.


Question: Can you send blob attachments via workflow notifications?
Answer: Yes, you can send BLOB Attachments.

Oracle Apps interview questions

Question: How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions fromone environment to another without reKeying? Will you use FNDLOAD?
Answer: FNDLOAD can not be used in the scenario. You can use migrator available in "Oracle iSetup" Responsibility

Question: This is a very tough one, almost impossible to answer, but yet I will ask. Which Form in Oracle Applications has most number of Form Functions?
Answer: "Run Reports". And why not, the Form Function for this screen has a parameter to which we pass name of the "Request Group", hence securing the list of Concurrent Programs that are visible in "Run Request" Form. Just so that you know, there are over 600 form functions for "Run Reports"

Question: Which responsibility do you need to extract Self Service Personalizations?
Answer:Functional Administrator

Question: Can you list any one single limitation of Forms Personalization feature that was delivered with 11.5.10
Answer:You can not implement interactive messages, i.e. a message will give multiple options for Response. The best you can get from Forms Personalization to do is popup up Message with OK option.

Question: You have just created two concurrent programs namely "XX PO Prog1" & "XX PO Prog2". Now you wish to create a menu for Concurrent Request submission such that only these two Concurrent Programs are visible from that Run Request menu. Please explain the steps to implement this?
Answer:
a) Define a request group, lets say with name "XX_PO_PROGS"
b) Add these two concurrent programs to the request group "XX_PO_PROGS"
c) Define a new Form Function that is attached to Form "Run Reports"
d) In the parameter field of Form Function screen, enter
REQUEST_GROUP_CODE="XX_PO_PROGS" REQUEST_GROUP_APPL_SHORT_NAME="XXPO" TITLE="XXPO:XX_PO_PROGS"
e) Attach this form function to the desired menu.

Question: Does Oracle 10g support rule based optimization?
Answer: The official stance is that RBO is no longer supported by 10g.

Question: Does oracle support partitioning of tables in Oracle Apps?
Answer: Yes, Oracle does support partitioning of tables in Oracle Applications. There are several implementations that partition on GL_BALANCES. However your client must buy licenses to if they desire to partition tables. To avoid the cost of licensing you may suggest the clients may decide to permanently close their older GL Periods, such that historical records can be archived.
Note: Before running the archival process the second time, you must clear down the archive table GL_ARCHIVE_BALANCES (don’t forget to export archive data to a tape).

Question: What will be your partitioning strategy on GL_BALANCES? Your views please?
Answer: This really depends upon how many periods are regularly reported upon, how many periods are left open etc. You can then decide to partition on period_name, or period ranges, or on the status of the GL Period.


Question: Does Oracle support running of gather stats on SYS schema in Oracle Apps?
Answer: If your Oracle Applications instance is on 10g, then you can decide to run stats for SYS schema. This can be done by exec dbms_stats.gather_schema_stats('SYS');
Alternately using command dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);
I will prefer the former with default values.
If you wish to delete the stats for SYS use exec dbms_stats.delete_schema_stats('SYS');
You can schedule a dbms_job for running stats for SYS schema.


Question: Can you use concurrent program "Gather Schema Statistics" to gather stats on sys schema in oracle apps?
Answer: No, "Gather Schema Statistics" has no parameters for SYS schema. Please use dbms_job.


Question: Which table is used to provide drill down from Oracle GL into sub-ledger?
Answer: GL_IMPORT_REFERENCES

Question: What is the significance of profile option “Node Trust Level” in Oracle Apps.
Answer: If this profile option is set to a value of external against a server, then it signifies that the specific mid-tier is External i.e. it will be exposed to the www. In other words this server is not within the firewall of your client. The idea behind this profile option is to flag such middle-tier so that special restrictions can be applied against its security, which means a very restricted set of responsibilities will be available from such Middle-Tier.


Question: What is the significance of profile option “Responsibility Trust Level”.
Answer: In order to make a responsibility accessible from an external web tier, you must set profile option “Responsibility Trust Level” at responsibility level to “External”. Only those responsibilities that have this profile option against them will be accessible from External Middle tiers.


Question: What else can you suggest to restrict the access to screens from external web tiers?
Answer: You may use URL filtering within Apache.


Question: What is the role of Document Manager in Oracle Purchasing?
Answer: POXCON is an immediate concurrent program. It receives pipe signal from the application when a request is made for approval/reservations/receipts.


Question: How to debug a document manager in Oracle Apps?
Answer: Document manger runs within the concurrent manager in Oracle Applications. When an application uses a Document Manager, it sends a pipe signal which is picked up by the document manager.
There are two mechanisms by which to trace the document manager
1. Set the debugging on by using profile option
STEP 1. Set profile option "Concurrent:Debug Flags" to TCTM1
This profile should only generate debugs when set at Site level(I think, as I have only tried site), because Document Manager runs in a different session.
STEP 2. Bounce the Document Managers
STEP 3. Retry the Workflow to generate debugs.
STEP 4. Reset profile option "Concurrent:Debug Flags" to blank
STEP 5. have a look at debug information in table fnd_concurrent_debug_info

2. Enable tracing for the document managers
This can be done by setting profile option “Initialization SQL Statement – Custom” against your username before reproducing the issue. The value of this profile will be set so as to enable trace using event 10046, level 12.

Question: You have written a Java Concurrent Program in Oracle Apps. You want to modify the CLASSPATH such that new class CLASSPATH is effective just for this program.
Answer: In the options field of the concurrent program you can enter something similar to below.
-cp :/home/xxvisiondev/XXDEVDB/comn/java/appsborg.zip:/home/xxvisiondev/XXDEVDB/comn/java

Question: How will you open a bc4j package in jdeveloper?
Answer: Oracle ships a file named server.xml with each bc4j package. You will need to ftp that file alongside other bc4j objects(VO’s, EO’s, AM, Classes etc).
Opening the server.xml will load the complete package starting from AM(application module). This is a mandatory step when building Extensions to framework.


Question: In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?
Answer: Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.

Question: In self service, you wish to change the background color and the foreground text of the OA Framework screens to meet your corporate standards. How will you do it?
Answer: You will need to do the below steps
a….Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
b…Enter below text( change colours as needed)


c… cd $OA_HTML/cabo/styles/cache
d…Take a backup of all the css files.
e…Delete all the files of following pattern oracle-desktop*.css
The idea here is to delete the cache. Next time when you logon to Oracle Apps Self Service, the Framework will rebuild the css file if found missing for your browser.


Question: Can you extend and substitue a root AM ( Application Module) in OA Framework using JDeveloper.
Answer: You can extend the AM in jDeveloper, but it doesn’t work( at least it didn’t work in 11.5.9). I am hopeful that Oracle will deliver a solution to this in the future.

Question: In a workflow notification, you have a free text response field where the user enters the Vendor Number for the new vendor. You want to validate the value entered in the notification response field upon the submission of a response. How will you do it?
Answer: You will need to attach a post notification function to the Workflow Notification.
The PL/SQL code will look similar to below:-
The below code will display an error in the notification when user attempts to create a Duplicate Vendor Number.
PROCEDURE validate_response_from_notif
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_newly_entered_vendor_num VARCHAR2(50);
CURSOR c_get_response_for_new_vendor IS
SELECT wl.lookup_code
FROM wf_notification_attributes wna
,wf_notifications wn
,wf_message_attributes_vl wma
,wf_lookups wl
WHERE wna.notification_id = l_nid
AND wna.notification_id = wn.notification_id
AND wn.message_name = wma.message_name
AND wn.message_type = wma.message_type
AND wna.NAME = wma.NAME
AND wma.SUBTYPE = 'RESPOND'
AND wma.format = wl.lookup_type
AND wna.text_value = wl.lookup_code
AND wma.TYPE = 'LOOKUP'
AND decode(wma.NAME, 'RESULT', 'RESULT', 'NORESULT') = 'RESULT';
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
OPEN c_get_response_for_new_vendor;
FETCH c_get_response_for_new_vendor
INTO l_activity_result_code;
CLOSE c_get_response_for_new_vendor;
v_newly_entered_vendor_num := wf_notification.getattrtext(l_nid,'NEWLY_ENTERED_VENDOR_NUM_4_PO');
IF l_activity_result_code = 'NEW_VENDOR'
AND does_vendor_exist(p_vendor => v_newly_entered_vendor_num)
THEN
RESULT := 'ERROR: VendorNumber you entered already exists';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END validate_response_from_notif;


Question: How to make concurrent program end with warning?
Answer: If the concurrent program is of type PL/SQL, you can assign a value of 1 to the “retcode” OUT Parameter.
For a Java Concurrent program, use the code similar to below
ReqCompletion lRC;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
lRC.setCompletion(ReqCompletion.WARNING, "WARNING");


Question: How do you link a Host type concurrent program to Concurrent Manager?
Answer: Assuming your executable script is LOADPO.prog, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO


Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: Use table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
,to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS') dated
FROM apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;


Question: How do you send a particular Oracle Apps Workflow Activity/Function within a workflow process into background mode.
Answer: If cost of the workflow activity is greater than 50, then the workflow activity will be processed in background mode only, and it won’t be processed in online mode.

Question: What are the various ways to kick-off a workflow
Answer: You can eiter use wf_engine.start_process or you can attach a runnable process such ghat it subscribes to a workflow event.

Question: When starting (kicking off) an oracle workflow process, how do you ensure that it happens in a background mode?
--a)if initiating the process using start_process, do the below
wf_engine.threshold := -1;
wf_engine.createprocess(l_itemtype
,l_itemkey
,'');
wf_engine.startprocess(l_itemtype, l_itemkey)
--B) When initiating the workflow process through an event subscription, set the Execution Condition Phase to be equal to or above 100 for it to be executed by background process.


Question: On 10g, how will you use awr?
Answer: By running below scripts. These are both the same scripts, but with differing parameters.
$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/awrrpti.sql

Question : How will you configure Apache to run in Debug mode, specifically usefull when debugging iProcurement ( prior to 11.5.10).
Answer: After 11.5.10, FND Logging can be used for debugging Oracle iProcurement.
Prior to 11.5.10
----STEPS IN A NUTSHELL-----
cd $ORACLE_HOME/../iAS/Apache
vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/ssp_init.txt
DebugOutput=/home/<>/ora9/iAS/Apache/Apache/logs/debug.log
DebugLevel=5
DebugSwitch=ON

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.conf
ApJServLogLevel debug

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.properties
log=true


Question: How will you add a new column to a List Of Values ( LOV ) in Oracle Applications Framework? Can this be done without customization?
Answer: Yes, this can be done without customization, i.e. by using OA Framework Extension coupled with Personalization. Implement the following Steps :-
a) Extend the VO ( View Object ), to implement the new SQL required to support the LOV.
b) Substitute the base VO, by using jpximport [ similar to as explained in Link ]
c) Personalize the LOV Region, by clicking on Add New Item. While adding the new Item, you will cross reference the newly added column to VO.

Question: Can you do fnd_request.submit_request from SQL Plus in Oracle?
Answer: You will need to initialize the global variables first using fnd_global.initialize
DECLARE
v_session_id INTEGER := userenv('sessionid') ;
BEGIN
fnd_global.initialize
(
SESSION_ID => v_session_id
,USER_ID =>
,RESP_ID =>
,RESP_APPL_ID =>
,SECURITY_GROUP_ID => 0
,SITE_ID => NULL
,LOGIN_ID => 3115003--Any number here
,CONC_LOGIN_ID => NULL
,PROG_APPL_ID => NULL
,CONC_PROGRAM_ID => NULL
,CONC_REQUEST_ID => NULL
,CONC_PRIORITY_REQUEST => NULL
) ;
commit ;
END ;
/
Optionally you may use fnd_global.apps_initialize, which internally calls fnd_global.initialize
fnd_global.apps_initialize(user_id => :user_id,
resp_id => :resp_id,
resp_appl_id => :resp_appl_id,
security_group_id => :security_group_id,
server_id => :server_id);
By doing the above, your global variables upon which Concurrent Managers depend upon will be populated. This will be equivalent to logging into Oracle Apps and submitting the concurrent request from a responsibility.