Hi! This post is a continuation of interview questions on Oracle apps technical. Hope this helps whoever is reading - Click here for Part 1.
1. What is an alert in Oracle and what are the types of alerts?
An Alert in Oracle Apps works like a notification that notifies every time when a certain condition specified is met. There are 2 types of alerts in Oracle.
(i) Event Alerts - Every time when an event occurs, an event alert sends a notification.
For example, these alerts can be used whenever a row is inserted or gets updated in a particular table.
(ii) Periodic Alerts - These alerts can be triggered weekly monthly, yearly based on a condition.
2. What is a materialised view?
A materialized view Works like a snapshot, balances the data between a table and a materialised view.
3. What is the difference between a view and a materialized view in Oracle?
A view rerun the query each time it is executed where as a materialised view goes to the storage location and run from there without executing the query again, therefore results in faster retrieval of data.
4. How can we give the layout manually in Oracle apps?
fnd_request.add_layout (
template_appl_name => 'Template Application',
template_code => 'Template Code',
template_language => 'en', --Use language from template definition
template_territory => 'US', --Use territory from template definition
output_format => 'PDF' --Use output format from template definition
);
5. How to submit a concurrent request from backend?
FND_REQUEST.SUBMIT_REQUEST (
application => 'ONT'-->Application short name
,program => 'OEOIMP');-->Concurrent program short name
6. What is the difference between CHAR and VARCHAR in Oracle?
Both are used to store strings but CHAR datatype stores strings of fixed lengths and reserves space for null value where as VARCHAR can store strings of variable lengths and doesn't reserve space for null values.
7. What is the use of WITH clause in Oracle?
WITH Clause allows us to give a sub query block a name which is also known as sub query refactoring which can be referenced in several places within main query.
8. What is a collection and what are the types of collections in Oracle?
A collection is an ordered group of elements, all of the same type that helps in faster retrieval of data in Oracle.
Types of Collections:
Associative Array or Index by Table: Indexing can be done with strings, map.
Nested Tables: List, Variable size, Index starts with 1.
Varrays: Arrays, Pre-defined size, Index starts with 1, Cannot delete elements.
9. Name some analytical functions.
LEAD, LAG, RANK, DENSE_RANK, FIRST, LAST, LISTAGG, MATCH_RECOGNIZE.
10. Write a query which shows a running total.
select empno, sal, sum(sal) over(order by sal) as running_total from emp;
11. What do you mean by lexical parameter?
Lexical references are placeholders for text that is embedded in the SELECT statement. Lexical references can be used to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
12. What are transaction control statements in Oracle?
Transaction control statements are used to manage transactions in Oracle.
Examples are COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.
13. What are the types of Sub queries:
Correlated Sub Query: Correlated sub queries depend on data provided by the outer query. This type of sub query also includes sub queries that use the EXISTS operator.
Inline sub query: A query that starts in the FROM clause of a another query is called an Inline sub query.
Row-based:
Single Row Sub queries: A single row Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions.
Multi-row Sub queries: A Sub query that returns multiple rows. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also.
14. Name some PO interface 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
15. What is the difference between Org, Organization in Oracle apps?
ORG_ID works at operating unit level
ORGANIZATION_ID works at INVENTORY module level.
16. What is the command to delete a bursting file in server?
DELETE
FROM XDO_LOBS
WHERE LOB_CODE=<DATA_DEFINITION_CODE>
AND LOB_TYPE='BURSTING_FILE';
17. What is the difference between UNION and UNION ALL?
UNION merges the results from given queries/ tables where as UNION ALL retrieves the merged result including duplicates.
Performance wise, UNION ALL is preferred to UNION.
18. What is the command for page break in RTF template?
<?split-by-page-break:?>
19. What is an order hold and give an example?
A Hold is to stop an order, order line, or return line from continuing to progress through its life cycle. It can also be applied manually by the users but a user can not apply a hold if the order has been pick released.
Examples: Credit Checking Hold, Configurator Validation Hold, GSA, Violation Hold.
20. Name some UTL file commands.
UTL_FILE.FOPEN,
UTL_FILE.GET,
UTL_FILE.PUT,
UTL_FILE.FCLOSE
21. What is the of NOCOPY hint in Oracle?
NOCOPY hint tells a procedure to use pass by reference instead of default pass by value.
Pass By Value : By default, it creates a temporary buffer ,copy the data from the parameter variable to that buffer and work on the temporary buffer during the lifetime of the procedure. When the procedure successfully gets completed, the contents of the temporary buffer are copied back into the parameter variable. In the event of an exception occurring, the copy back operation does not happen.
Pass By Reference : With the NOCOPY hint, it tells the compiler to use pass by reference, so no temporary buffer is needed and no copy forward and copy back operations happen. Instead, any modification to the parameter values are written directly to the parameter variable.
22. What is a hash cluster?
Hash cluster is a technique to store data in hash table and improve the performance of data retrieval. Hash function is applied on table row's cluster key value and store in hash cluster.
23. What is a table space?
Table space is a logical storage unit in Oracle.
24. What is the difference between pre-select and pre query triggers in oracle forms?
A pre-query trigger fires before the query executes and fires once while you try to query the data. With the help of this trigger, you can modify the where clause part dynamically.
Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.
Note: Pre-query trigger fires before pre-select trigger.
25. What is the use of control file in Oracle?
In Oracle, Control file is used for database recovery. the control file is also used to identify the database and redo log files that must be opened for database operation to go ahead, whenever an instance of an Oracle database begins.
26. What are the limitations of CHECK constraint?
The main limitation of CHECK constraint is that the condition should be a boolean expression evaluated using the values in the row being inserted or updated and it cannot contain sub queries.
27. How will you identify Oracle Database Software release?
Oracle follows a number of formats for every release. For example, Release 10.1.1.1.1 can be referred to as:
10: Major DB Release number
1: DB Maintenance Release number
1: Application Server Release number
1: Component Specific Release number
1: Platform Specific Release number
28. What is the use of COALESCE function in Oracle?
Coalesce function is used to return the first not null value in the given argument list. If all the values in the list are NULL, then this function returns null value.
29. What is Procedure overloading?
Procedure overloading is the concept of creating multiple procedures or functions of the same name in a package but having different number of arguments and/ or where the arguments have different datatypes.
30. What is the difference between WHERE and HAVING clause in SQL?
The main difference between WHERE and HAVING clause is noticed when using GROUP BY clause. WHERE condition filters out the rows before GROUP BY clause and HAVING can filter out the rows after GROUP BY.
If you any questions or comments, hit them in the comment section below. Have a good day!!
