Oracle Apps Technical Interview Questions

Got an interview scheduled on Oracle? Don't worry! We've got you covered. This post will give you the confidence to tackle interview on Oracle Apps - Technical. The questions mentioned here are the real time questions asked in various companies. Hope this post helps you!!

Oracle Technical Interview Questions

1. What is the process of P2P cycle in Oracle and what are the tables that are affected?

P2P flow goes as below.

2. What is the process of O2C cycle in Oracle and what are the tables that are affected?

O2C Flow is as below:

Along with the process flow, its better to understand what each action is doing and what tables are involved.

3. What is the difference between a PL/SQL Procedure and a Function?

The main difference between a procedure and a function is that a function must return a value where as a procedure may or may not return a value.

One more difference is that we can call a function from a SELECT statement (if no DML statements are used) but the procedure can only be called using EXEC or from an anonymous block.

4. What is a Cursor in Oracle?

Oracle creates a memory area which is called as Context area. A cursor is a pointer to this context area which fetches and processes the rows returned by an SQL statement, one at a time.

5. What are the types of cursors?

There are two types of cursors.

(i) Implicit cursors - Implicit cursors are automatically generated by Oracle whenever a DML statement i.e., INSERT, UPDATE, DELETE is executed if we don't use an explicit cursor.

(ii) Explicit cursors - Explicit cursors are user-defined cursors that should be defined in the declaration section. They are generally written on a SELECT statement that returns more than one row.

Eg: CURSOR c1 IS SELECT empno, ename FROM emp;

6. What are the cursor attributes of an implicit cursor?

Implicit cursors have four attributes:

%FOUND - Returns 'TRUE' if the rows fetched by a DML statement has more than or equal to one row else returns 'FALSE'.

%NOTFOUND - Returns ' TRUE' if there are no rows fetched by a DML statement else returns 'FALSE'

%ISOPEN - It always returns 'FALSE' in case of implicit cursors because the cursor is closed automatically after the execution of the code.

%ROWCOUNT - Returns the number of rows affected by a DML statement returned by a SELECT INTO statement. It returns numeric data type while the above three attributes return a string datatype.

7. What is a Ref cursor in Oracle?

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. Ref cursors can be used as parameters to a procedure or a function.

8. What is bulk collect in Oracle?

We can use bulk collect to retrieve multiple rows with a single fetch that improves the speed of data retrieval.

9. What are triggers in Oracle?

A trigger is a named PL/SQL block that is stored in Oracle database and executed automatically whenever a triggering event takes place.

There are three types of triggers based on timing - BEFORE trigger, AFTER Trigger and INSTEAD OF Trigger. 

There are two level based triggers - STATEMENT level Trigger and ROW level Trigger. And there are three event triggers - DML Trigger, DDL Trigger, DATABASE Trigger.

10. What is the process of BI Publisher XML report in Oracle?

1.Build a query

2.Create a data template file with the query

3.Create a concurrent program with the Executable method as 'XDODTEXE' (Java Concurrent Program) and give parameters(if any). Select the output format as 'XML'.

4.Go to XML Publisher>Data Definitions>Create a Data definition with the Concurrent Program short name and attach the data template file(.xml).

5.Go to Data Templates in the same responsibility>Create the data template with the Concurrent Program short name. Attach the layout template which can be either RTF or EXCEL template based on the data.

6.Run the report.

11. What are the sections available in XML data template file?

dataTemplate

parameters

dataQuery

sqlStatement

dataTrigger

dataStructure

group

element

12. What is the default parameter in RDF reports?

p_conc_request_id.

13. How to register PL/SQL stored procedure in concurrent program?

Build a PL/SQL stored procedure.

Create an executable with execution method as 'PL/SQL Stored Procedure' and mention the procedure name in 'File name' field.

Create a concurrent program with this executable and run the report.

14. How to generate XML output through PL/SQL procedure in concurrent program?

We need to embed XML tags in PL/SQL procedure and register it in the concurrent program with output type 'XML'.

source: http://www.erpschools.com/articles/xml-publisher

15. What are the mandatory parameters for a PL/SQL stored procedure that is used in a concurrent program?

Errbuf (varchar2) - Used to store error message

Retcode (number) - To store the status of the concurrent request.

Without these 2 parameters defined in a procedure or a package, the program will result in error when it is run.

16. Can we set the status as 'Warning' when using PL/SQL stored procedure as execution method in concurrent program?

Yes, we can manually set it through retcode.

Retcode = 1, Warning

Retcode = 2, Error (0 for Success).

17. How can we make one parameter dependent on another?Assume that the values are coming from a table.

We can use $FLEX$ to achieve this requirement.

1. Define first parameter. Eg: XX_DEPTNO

2.Define second parameter with table value set. In the Where condition, give $FLEX$.First parameter name

Eg: select ename from emp where deptno = :$FLEX$.XX_DEPTNO.

18. What is the difference between an interface and a conversion in oracle?

The main difference is that a conversion is a one-time load where as an interface is a periodic process to load data into the applications but they have the same functionality which is loading data into the applications.

19. What are interfaces in Oracle and how many types of interfaces do we have?

An interface is used to load the data into the system periodically or when there is a requirement.

There are two types of interfaces in Oracle:

1. Inbound Interface

2. Outbound Interface

20. What is the process of inbound interface?

Step 1: Create a staging table with all the required columns.

Step 2: Create .ctl file with staging table name and columns.

Step 3: Place both control file and flat file (where we have data) into the server under <your application top directory>.

Step 4: Create an executable with execution method as 'SQL Loader' and give the control file name without any extension.

Step 5: Create a concurrent program for the executable and run the program. Data will be loaded into the staging table if the request status is Success.

21. What is the process of Outbound interface?

Create a procedure or a Package with utl file commands. Register it in the concurrent program and run the report.

22. What are the exceptions of UTL_FILE?

INVALID_PATH, INVALID_MODE, INVALID_FILEHANDLE, INVALID_OPERATION, READ_ERROR, WRITE_ERROR, INTERNAL_ERROR.

23. What are some of the tables in OM?

OE_ORDER_HEADERS_ALL

OE_ORDER_LINES_ALL

OE_ORDER_HOLDS_ALL

WSH_DELIVERY_DETAILS

WSH_NEW_DELIVERIES

WSH_DELIVERY_ASSIGNMENTS

24. Name some tables in AP.

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

AP_PAYMENT_SCHEDULES_ALL

AP_PAYMENT_HISTORY_ALL

AP_HOLDS_ALL

AP_AE_LINES_ALL

25. What are the types of collections that we have in Oracle?

Associative array, Nested table, Varray.

26. How to remove a new line character in oracle?

CHR(13) is used to remove new line characters.

Eg: select replace(column_name, CHR(13), '') from table;

27. Can we call a function using select statement when used DML commands in it?

We cannot call a function using a SELECT statement when there are DML statements in it.

28. Can we use DDL statements in a function?

We cannot use DDL statements in a function.

29. How to set multi org from backend?

begin

apps.mo_global.set_policy_context('M');

end;

30. How many report triggers we have in RDF and XML reports?

RDF - Before Parameter, After Parameter, Between Pages, Before Report, After Report.

XML - beforeReport and afterReport

31. Can we use XML bursting for any other purpose rather than sending emails?

Yes, we can use it for FTP.

32. What is the difference between a simple view and a complex view?

A simple view is made up of only one table where as a complex view is created using more than one table.

33. Can we use DML statements in Complex views?

No, Complex views don't support DML statements but simple views do.

34. What functions can we perform using summary column in RDF?

We can perform functions like Average, Count, First, Last, Maximum, Minimum, % of Total, Std Deviation, Sum, Variance using Summary column.

35. What is Pragma autonomous transaction in Oracle?

Use of Pragma autonomous transaction makes a sub program independent of main program. We can perform SQL operations and commit or rollback operations without committing the data in the main transaction. COMMIT or ROLLBACK is must when using Pragma autonomous transaction in the sub program.

36. What are the types of flexfields in oracle and what is their use?

Key flexfields - Used to enter multi-segment values

Descriptive flexfields - Used to enter additional information that Oracle has not provided.

37. How to see the table name and column names in a particular form in Oracle?

For a table or a view name, Help>Record History

For a column name, Click on the particular field that you want to see then click on Help>Diagnostics>Examine.

38. What is the difference between CASE and DECODE in Oracle? (OR) How is CASE different from DECODE?

CASE is a statement which can perform operations like =, >, <, BETWEEN , LIKE where as DECODE is a function which performs only equality check. CASE can even be used as a parameter of a function or a procedure. CASE expects data type consistency but DECODE doesn't. CASE can work with logical operators, predicates, searchable sub queries where as DECODE works only with scalar values.

39. What is the difference between a table and a table with suffix _ALL?

_ALL table stores data related to multiple organizations.

40. What are the PO interface, error and base tables and what is the program to load data into the base tables?

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

Error Table:

PO_INTERFACE_ERRORS

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

Interface Program:

Import Standard Purchase Orders.

41. What are lookups in Oracle?

Lookups are a static collection of codes used by Oracle. Every lookup has a code associated with it and a meaning for the code. We can define a maximum of 250 lookup values for a lookup type.

42. What are the types of value sets in Oracle apps?

We have 8 types of value sets in Oracle apps.

None

Table

Independent

Dependent

Translatable Independent

Translatable Dependent

Special

Pair.

43. How is VARCHAR different from VARCHAR2?

VARCHAR can store up to 2000 bytes where as VARCHAR2 can store upto 4000 bytes. VARCHAR reserves space for NULL values but VARCHAR2 doesn't.

44. Consider a table called students where some students have same names. Write a query to retrieve the names that exist more than once.

select student_name, count(student_name) from students group by student_name having count(student_name) > 1.

45. What is DUAL table in Oracle?

DUAL is a default table provided by Oracle that has a single row single column that is used to perform arithmetic operations, evaluating expressions or calling functions.

46. Write an example of bulk collect in Oracle.

declare

TYPE empname is TABLE of VARCHAR2(20);

TYPE empno is TABLE of VARCHAR2(20);

l_ename empname;

l_eno empno;

begin

select ename, empno BULK COLLECT into l_ename, l_eno from emp;

FOR i in 1..l_ename.count

LOOP

dbms_output.put_line(l_ename(i)||' '||l_eno(i));

end LOOP;

end;

/

show errors;

47. Using sysdate, write 2 queries that should show the first day and last day of next month.

First day of next month: select trunc(add_months(sysdate,1), 'MONTH') from dual;

Last day of next month: select last_day(add_months(sysdate,1)) from dual;

OR

select last_day(sysdate)+1 from dual;

48. What is the difference between DROP and TRUNCATE table in Oracle?

TRUNCATE deletes all the rows from a table and it cannot be rolled back. DROP command drops the structure of the table as well along with the rows. The table can be brought back to its original state even after dropping it.

49. What is the difference between TRUNCATE and DELETE?

DELETE and TRUNCATE: Both perform the same action i.e., delete the rows from a table but TRUNCATE cannot be rolled back where as DELETE can be.

50. What is the use of Formula column in Oracle RDF reports?

A Formula column is used to perform user defined computation on the data of one or more columns. We use PL/SQL editor to create formula columns.

51. What is a place holder column in RDF report?

A placeholder holds a value at run time that has been calculated and placed into it by PL/SQL code.

For example, it can be used to display the name of the employee whose salary is the highest in employee report.

52. What is SRW package in RDF reports?

SRW (Sql Report Writer) Package is a built in package in Oracle Reports Builder. It is a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.

source: http://www.oracleerpappsguide.com/2011/03/srw-package-in-oracle-report.html

53. How set all permissions to a file using LINUX?

chmod 8777

54. What is the process of registering a host concurrent program?

Step 1: Create a .prog file and place it under <your application top directory>.

Step 2: Generate a symbolic link for the prog file and this link should be named same as prog file with no extension.

Step 3: Register the executable with executable method as 'Host' and give the .prog file name without extension.

Step 4: Create the concurrent program with this executable and run the report. Make sure that the .prog file has all permissions.

55. What is the syntax of break in RTF template?

<?split-by-page-break:?>

56. How to conditionally limit the rows in RTF template? Write the syntax to limit 3 rows per page.

<?position()?>

<?if: position() mod 3 = 0?>

<?split-by-page-break:?>

<?end if?>

57. How to display the 6th highest salary of an employee?

select ename, sal, dense_rank() over (order by sal desc) rank from emp where rank = 6;--n=6.

58. What is a record in PL/SQL?

A Record is a data structure that can hold different types of data items. We can use three types of records in PL/SQL.

Table-based records

Cursor-based records

User-defined records.

59. What are exceptions in Oracle? Name some examples of exceptions.

Exceptions are run-time errors that can be caught using EXCEPTION block in a procedure. Exceptions can be pre-defined or user-defined.

Examples of pre-defined exceptions in oracle - NO_DATA_FOUND, INVALID_CURSOR, INVALID_NUMBER, TOO_MANY_ROWS etc.

60. What is the difference between an anonymous block and a stored procedure?

Anonymous blocks are used for one time execution where as stored procedures are stored in the database. We can use stored procedures in concurrent programs but anonymous blocks cannot be used.

Along with these, it's better to revise important table names in each module because it's TECHNICAL. Cheers!!