New Interview Questions

1. What is interface and conversion and what interface you developed explain complete process along with error handling part.
2. API name and table for element entry
3. How to do conversion process.
4. Inbound interface name and outbounf interface name that you worked on.
5. AOL basics like tables for value set and types of value sets and table for indepdent VS.
6. Pragmatic autonomous transaction.
7. SQL function for string
8. Cascading Decode statement.
9. Bulk collect with limit syntax and limitation .
10. The largest component you have ever worked on explain that.
11. Unix basics command for file access.
12. XML publisher reports registration steps.
13. Trigger
14. All base tables name for Oracle HRMS
15. Workflow tables.
16. Hint in SQL
17. Public and private API in Oracle
18. Ref cursor and usage of that.
19. Collection type variables.
20. Count(*) will generate no data found exception or not if conditions don’t match in where clause.
21. How to generate XML file from PLSQL package.
22. How to migrate template from one instance to other.
23. How to skip records from ctl file.
24. What will be status after awaiting for shipping in o2c.
25. How to submit request set from back end.
26. Profile objective creation and order of precedence at different level.
27. Ddl dml and command for SQL.
28. Max number of the columns in oracle tables?
29. Total attributes in table atnd what is Global attribute.
30. Total segments in table kff enabled table.
31. Total size of any object in oracle database .
32. What is AuthID in create or replace. Package.
33. Difference between procedure and Function.
34. INOUT parameters what is the purpose of nocopy with that?
35. What is lookups and what all are the tables for the same.
36. If emp is terminated then what will be the current employer flag in per_all_peope_f table.
37. When to use NOCOPY parameters in parameters.

1. How to swap to different column values in a table
2. What is pragma autonomous transaction
3. API for emo cretion
4. How to continue the interface process if any errors comes in between
5. Ame steps to create rule for salary proposal
6.table to get the salary info And table if no payroll module is implemented.
7. PTO and absence formula for grade rule n fast formula
8. If we want to roll back the element entry processed through the batch process how we will do it
9. what is Batch process
10. Setup for Core HR , Payroll and setup level definition and what it is like organization, business group and payroll
11. Organization hierarchy for implementation setup
12. Payroll full life cycle before payroll process and after payroll process

Difference between Valuesets and Lookups

Difference 1
Value sets can be attached to parameters of a concurrent program or to a DFF segments where as Lookups are attached only to the fields of a Form/Page

Difference 2
Lookups can be maintained by end users where as Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators.

Difference 3
Lookups can have translated values in different languages but not the values in the Value Sets

Difference 4
Value sets can contain values that are a result of an SQL Statement. Hence it is possible to make Value Set list of values dynamic.
On the contrary, Lookup Codes are Static list of values which can only be entered through Lookups Form.

Difference 5
We have several different types of value sets but not lookups.

PL SQL Interview Important Questions


1) Difference between union and union all in oracle ?

Answer –> Union all does not remove duplicates
— Union removes duplicate records

Both queries should have same columns.

Number of columns in each UNION query must match

Data types must match.

For large data set queries UNION might have performance issues. So use it very carefully.

Link –>


2) What is Subquery in Oracle?

Answer –>

Subquery can be used in 3 location –>
— Where Clause —

FROM all_tables tabs
WHERE tabs.table_name IN (SELECT cols.table_name
FROM all_tab_columns cols
WHERE cols.column_name = ‘SUPPLIER_ID’);

** Limitation: Oracle allows up to 255 levels of subqueries in the WHERE clause.

— From –it is known as Inline views

SELECT, subquery1.total_amt
FROM suppliers,
(SELECT supplier_id, SUM(orders.amount) AS total_amt
FROM orders
GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;

** Oracle allows an unlimited number of subqueries in the FROM clause.


SELECT tbls.owner, tbls.table_name,
(SELECT COUNT(column_name) AS total_columns
FROM all_tab_columns cols
WHERE cols.owner = tbls.owner
AND cols.table_name = tbls.table_name) subquery2
FROM all_tables tbls;

The trick to placing a subquery in the select clause is that the subquery must return a single value.
This is why an aggregate function such as SUM function, COUNT function, MIN function, or MAX function is commonly used in the subquery.


3) Usage of INSTR and SUBSTR in oracle?

select  INSTR(‘1234-abc’,’-‘) from dual;   –will find postion, for eg. in this case it will give 5

select SUBSTR(‘1234-abc’,1,5-1) from dual ;   — over here 5 -1 is the position of separator -1 ;

— Not answered Completely

4) USage of regexp_substr to split single row into multiple columns?

select regexp_substr(‘111*222*333’, ‘[^*]+’, 1, level) str
from dual
connect by level <= length(‘111*222*333’)-length(replace(‘111*222*333′,’*’))+1


5) Use of Execute Immediate Statement –>

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement .

sql_stmt    VARCHAR2(200);

sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’;


In a PL/SQL program, you can invoke static SQL without any special notation. What is static SQL ? Static SQL is :

Data Manipulation Language (DML) Statements (except EXPLAIN PLAN) : INSERT, UPDATE, DELETE
Transaction Control Language (TCL) Statements : COMMIT, ROLLBACK…
SQL Functions
SQL Pseudocolumns : ROWID, ROWNUM…
SQL Operators

As you can see, CREATE statements are not static SQL. To invoke them, you have to use dynamic SQL, via EXECUTE IMMEDIATE.

6) what are the compound symbols? 

:= assignment operator
.. range operator
|| concatenation operator
— single-line comment indicator

7) To DEbug and Test PL / SQL package –>

set serveroutput on
dbms_output.put_line(‘Hello Tester’);




9) Global temporary tables –>

10)-– COALESCE   Function
The COALESCE function returns first not null expression among the arguments. Minimum 2 arguments required.

If all expressions are null then it returns null.


COALESCE (expr_1,expr_2,…expr_n)

Examples :-

1. select COALESCE(null,null,null,10) from dual;

would return 10.

2. select COALESCE(null,null,null) from dual;

would return null.

—Why procedure can’t be used in SQL statement, whereas Function can be used ?

Answer ==

1) This is because Function must return a value, while procedure may or may not.
2) Also function can be used as an assignment statement, while Procedure can never be used as an assignment.

Function can be used as assignmnet statement.