LATEST Oracle Apex Interview Questions & Answers

Last updated on 10th Jun 2020, Blog, Interview Questions

Oracle Application Express (APEX) is a low-code development platform that enables you to build scalable, secure enterprise apps, with world-class features, that can be deployed anywhere. Using APEX, developers can quickly develop and deploy compelling apps that solve real problems and provide immediate value. You won’t need to be an expert in a vast array of technologies to deliver sophisticated solutions. Focus on solving the problem and let APEX take care of the rest.

1. What is the use of Oracle ASM?

Ans:

• ASM is a file system for oracle, and controlled by oracle volume manager.
• Oracle asm used for standalone databases as well as Cluster databases (RAC.

2. Difference between normal file system and ASM?

Ans:

Load balancing is high in ASM file systems compared to the traditional file system. IN oracle asm we call it ASM striping:

• To balance the loads across all the disks in a disk group
• The I/O latency is very low
• And also we can specify the redundancy level Normal or High like RAID.

3. What is ORACLE AFD?

Ans:

Oracle asm Filter driver(ASMFD.. It is a kernel module residing in the I/O paths of the oracle ASM disks. It is an optional one. After completing Grid installation we need to configure the ASMFD to the system. In case if we used oracle asmlib we need to migrate ASMLIB to ASMFD. Then we need to clean up the oracle asmlib. Usually when the server restarted the need to rebind the disk devices with oracleasm, to simplify this the Oracle ASMFD we needed to configure. It will allow only oracle I/O requests and will reject the invalid or non Oracle I/O requests.

4. How to create a Disk Group?

Ans:

The disk group will have one or more number of disks, And is the fundamental object for ASM managers.

To create the disk we need to use the command:

Oracle database files are allocated from disk groups. A disk group might contain several databases. And a single database can use multiple disk groups. What are all files we are storing inside the disk groups we call it as ASM files.

5. How to list the Disk Groups?

Ans:

• To list the disk groups we are having more views below are the some example
• V$ASM_DISKGROUP – Group name, size related information, state
• V$ASM_FILE – list the ASM files and the groups
• V$ASM_ALIAS – Disk group mounted by oracle ASM

6. How to drop Disk groups?

Ans:

• We need to mount the disk group which we are going to drop.
• Need to specify INCLUDING CONTENTS for dropping all the files in this disk group.
• DROP DISKGROUP dg_data_01 INCLUDING CONTENTS;
• Need to perform the above command in ASM instance not normal database instance.

7. Difference between the ORACLE asmlib and ORACLE afd?

Ans:

• Oracle asm usually rebind the asm files once the server restarted
• Oracle asm might allow non oracle I/O to Oracle files
• Oracle ASMFD is a kernel module and it will avoid the non oracle I/O request.
• ORACLE ASMFD is available from the release 12.2 linux version

8. How to start an ASM instance?

Ans:

 ASM instance will work as oracle normal instance. It will mount the disk groups to make it ready ASM files for the database. It has a minimal amount of SGA compared to a normal instance.

9. What is the use of an ASM instance?

Ans:

ASM instance will work as oracle normal instance. It will mount the disk groups to make it ready ASM files for the database. It has a minimal amount of SGA compared to a normal instance. In the cluster database we will have a separate ASM instance for each node.

10. What is the ACFS file system?

Ans:

• Oracle automatic storage management cluster file system(ACFS..
• The ACFS files can be accessed from outside of the databases.
• It is a scalable and multi-platform file system
• ACFS does not support Grid infrastructure files
• ACFS does not support OCR and voting disk related files

We can use the ACFS file system for backup related information like RMAN, Data pump DUMPSET and archived log files

11. What is the use of OCR files?

Ans:

OCR is a file that contains the information about the Oracle database configuration information to manage clusters.  It will be created and placed on shared storage while installation oracle cluster ware.

12. What is the use of a Voting Disk?

Ans:

 The Voting disk is a file that contains information about node membership. It will be created and placed on shared storage while installation oracle cluster ware. It will reassign the cluster ownership between the nodes in case of failure.

13. What is the use of CRS in oracle RAC?

Ans:

CRS: cluster ready service provides the cluster software which is installed in each host via OUI cluster installation.  Oracle clusterware manages CRS resources. Like database , instances VIP address , listener and services

14. What are all the cluster ware storage?

Ans:

• Raw devices
• OCFS file system
• ASM file system

15. What is cache fusion in oracle RAC?

Ans:

 For instance recovery in the RAC database will first look into the cache, if it is not available it will look into the remote cache instead of looking into the disk. This is called cache fusion. We are using a cache coherency mechanism for cache fusion. IT depends on three services.

• Global Resource Directory (GRD.
• Global Cache Services (GCS.
• Global En-ueue Services (GES.
• Where do we need to use CVU?
• Cluster verification utility (CVU. to verify that all the nodes will meets all the criteria for oracle clusterware installation.

16. How to start a database in Oracle Rac database?

Ans:

 We can use SRVCTL command to start the database in oracle RAC environment.

• To start all the instance
• Srvctl start instance –db db_name
• To start a particular instance using by using instance name
• srvctl start instance –db db_name –instance “instance_name1,instance_name2”
• To start the database using node name
• Srvctl start instance –db db_name –node node_name

17. How do the Function and Procedure differ to each other ?

Ans:

• Functions are generally used for computation purposes, whereas procedures are used to implement the business logic.
• Functions can be called through SL uery, but procedure can’t.
• Function must return a value, whereas procedure may or may not.

18. Can we create a package body, without creating a package specification ?

Ans:

We must create the package specification first. Specification can be created without a body, but vice versa cannot be possible.

19. Explain the mutation of the trigger

Ans:

It occurs when a Trigger tries to update a row that is currently in the execution stage. So it can be solved by using temporary tables and views.

20. How to generate the primary key on a table without using sequence ?

Ans:

We can refer the following example for that :

21. How to handle the exception for duplicate record entry?

Ans:

We can use dup_val_index as an exception handler.

22. What will happen, if we will write the exception section like below :

Ans:

EXCEPTION:

23. What are the PL/SL cursors?

Ans:

Oracle uses workspaces to execute the SL commands. That means, when Oracle processes a SL command, it opens an area in the memory called Private SQL Area. A cursor is an identifier for this area. It allows programmers to name this area and access of it’s information.

24. What is returned by the cursor attribute SL%ROWCOUNT?

Ans:

 It returns the number of rows that are processed by a SL statement.

25. What are the advantages of APEX?

Ans:

 The following are the advantages of APEX:

• No license required, it is free.
• To build an application it is a fast learning curve for developers.
• We can build web applications that are fast & secure through APEX.
• There is a lot of scope for customization of the application as per our requirement.
• Deployment of applications is very simple as “Import & Export”. If any hard code with reference to values varies between environments are avoided.
• Apex Framework & meta-data is stored in Oracle tables. It runs on live Oracle DB’s.

26. Check the below scenario : We need to convert from meter to inches. Instead of doing manually, need to create an object which should execute each time as a converter. What is the suitable way to implement this ?

Ans:

Best way to write a function, which should have meter to inches conversion logic and should be executed from the SL statements.

27. If we have a function, declared in a package body, can be called from the outside of the package?

Ans:

No, because that kind of function will be treated as a private one, not the public or global one.

28. Can we use one select query in another select query ?

Ans:

Yes. Using subquery.

29. We have given table emp

Id	Name	Ph_no
1	ABC	234
2	XYZ
3	MNP	345

Then what should be the output of the following uery :

Select a.*,nvl2(aph_no,’null’,’not null’. null_col from emp;

Ans:

Output should be like this :

Id	Name	Ph_no	null_col
1	ABC	234	null
2	XYZ		not null
3	MNP	345	null

30. you need to compare two values,   if both are equal, then the result will be null  and if not equal then the first value will be returned.

Which function should you use?

A – NVL

B – NVL2

C – NULLIF

D – COALESCE

Ans:

 C – NULLIF

31. Which of the following is  true about the COUNT function?

A – COUNT(expression. returns the number of rows with non-null values for the expression.

B – COUNT(DISTINCT expression. returns the number of unique, non-null values in the column.

C – COUNT(*. returns the number of rows in the table.

D – All are true.

Ans:

D – All are true.

32. Mention the types of APEX Users?

Ans:

 Actually, they are divided into 3 they are:

• Developer.
• Instance Administrators.
• Workspace Administrators.

33. Is a NULL value is equal zero or a blank space? If not then what is the difference?

Ans:

A NULL value is not equal as zero or a blank space. But still it’s a value which can occupy space. But blank space and 0 are the character and number respectively.

34. For a materialized view, is that possible to auto refresh the structure of the base table ? So that whenever that is changed, the corresponding mat view is also changed.

Ans:

 No, it’s not possible. Mat view can be refreshed only for data, not for the table structure. So, if the structure is changed, we need to drop and re-create the mat view.

35. Can any view have any DML operation ?

Ans:

 Yes. Using instead of triggers, a view can manipulate the data.

36. Can sorting be possible using a column alias?

Ans:

Yes. A column alias instead of the actual column name can be used in the ORDER BY clause.

37. What is the requirement of a MERGE statement ?

Ans:

The MERGE statement is doing update or insertion of data conditionally into a database table.  It performs an UPDATE if the row/s is/are already exists, or an INSERT if the row/s does not exist.

38. Which DDL statement is used to add, modify or drop columns in a database table?

Ans:

The ALTER TABLE statement.

39.  Please select the correct answer from the below statements :

i. Union returns all the dataset including the duplicate one

ii. Union All returns all the dataset including the duplicate one

Ans:

 ii. Union All returns all the dataset including the duplicate one

40. How do you find a number as integer or floating ?

Ans:

Note : Taking example of an integer value : 111

41. What are the NVL and the NVL2 functions in SL? How do they differ?

Ans:

 NVL is required only to replace null value of a particular column, whereas NVL2 is required to replace the null as well as the not value of a particular column.

So, NVL has required 2 parameters and NVL2 3 parameters.

42. How to find the 4th highest salary from the Emp table. Write the query.

Ans:

43. How do you find a duplicate record?

Ans:

44. How to Delete a duplicate record?

Ans:

45. How to create a similar table structure, but without records of an existing table ?

Ans:

46. How to replace all the numeric characters with ‘NUMBERS’ and Alphabets with ‘ALPHABETS’

Example: [‘a’, ‘z’, ‘E’, 3, ‘f’, 6, 2, 0, ‘’] should output like:

[‘ALPHABETS’, ‘ALPHABETS’, ‘ALPHABETS’, ‘NUMBERS’, ‘ALPHABETS’,’NUMBERS’, ‘NUMBERS’, ‘NUMBERS’, ‘ALPHABETS’]

Ans:

47.  What is query to find Nth highest salary for employee using With Clause

Ans:

Note : N means any numbers can be put

48. How to find a table name with its owner ?

Ans:

49. How to put a condition with case insensitivity

Ans:

50. Skills required for APEX developers?

Ans:

Basics of PL/SQL & SQL are required & the added advantage would be having skills related to customizing look and feel with Javascript, CSS, HTML.

51. What should be the SL for the following output (rounded to next 10.:

Input	Output
13.2	20
9.9	10
101.001	10

Ans:

 select cell (cell(101.000001./10.*10 from dual;

52. Whether Foreign key contains null values ?

Ans:

No. Although logically it can’t be as any primary key can not contain any null values. But Oracle gives this facility for data convenience.

53. How do you declare a user-defined exception?

Ans:

User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION.

Syntax:

54. How to add the email validation using only one query?

Ans:

55. What does the term “Oracle” mean?

Ans:

Oracle has been provided by the Oracle Corporation. It is one of the most popular databases which is used for maintaining the relational management concepts and also for other purposes like online transaction processing, warehousing the data, and for grid enterprise computing.

56. Name the number format used for the identification of Oracle database software release.

Ans:

Following are the number format that is used for identification of Oracle database software release:

• Release number for major DB
• Release number for DB maintenance
• Release number for application server
• Release number for component-specific
• Release number for platform-specific

57. Name the language used in Oracle.

Ans:

Oracle is developed using C language.

58. Differentiate between varchar and varchar2.

Ans:

Varchar and varchar2 are the data types that are used in Oracle. Following is a table explaining the difference between varchar and varchar2:

Varchar	Varchar2
The storage capacity is up to 2000 bytes	The storage capacity is up to 4000 bytes
Null values are accepted by Varchar	No null spaces are accepted by Varchar2

59. Name the components of the physical database structure that are used in Oracle.

Ans:

Following are the components of the physical database structure that are used in Oracle:

• Data files that are one or more.
• Redo log files that are two or more
• Control files that are one or more

60. Name the components of the logical database structure that are used in Oracle.

Ans:

 Following are the components of logical database structure that are used in Oracle:

• Tablespaces
• Schema objects of the database

61. What do you understand by the term tablespace?

Ans:

 Tablespace is one of the logical database structures that is used in Oracle. It is a set of related logical structures.

62. What do you understand by the term SYSTEM tablespace?

Ans:

It is an automatically created system tablespace. Whenever a new system is created in the Oracle database, the database is named SYSTEM tablespace. The entire database’s dictionary is stored in the SYSTEM database.

63. What do you understand by the term table in Oracle?

Ans:

Table is one of the basic units of Oracle in the Oracle database. It contains all the accessible information related to a user that is saved in rows and columns.

64. What do the following numbers mean in the below-given version: Oracle version 9.3.0.5.0?

Ans:

Following is the meaning of the Oracle version 9.3.0.5.0:

• 9 is the release number for major database
• 3 is the release number for database maintenance
• 0 is the release number for application server
• 5 is the release number for component-specific
• 0 is the release number for platform-specific

65. What is Apex Interface?

Ans:

 If you are running on an oracle database and you want to build a rich web application with reports, forms, charts, drill downs and dashboards with limited Java experienced developers in a short time frame, Oracle APEX is the most likely candidate for consideration.

66. How to find the current date and time in Oracle?

Ans:

The SYSDATA(. function is used for finding the current date and time in Oracle.

67. Name the syntax used for converting a date into a char.

Ans:

Following is the syntax used for converting a date into a char:

68. Name the types of database objects in Oracle.

Ans:

Following are the types of database objects in Oracle:

• Tables
• Tablespaces
• Views
• Indexes
• Synonyms

69. What are the uses of different types of database objects in Oracle?

Ans:

Following are the uses of different types of database objects in Oracle:

• Tables are used for organizing the set of elements in a vertical and horizontal fashion.
• Tablespaces are used as logical storage units in Oracle.
• Views are the virtual tables that are derived from one or more tables.
• Indexes are used as a performance tuning method for processing the records.
• Synonyms are the names that are given to the tables

70. Name the different types of synonyms.

Ans:

Following are the different types of synonyms:

• Private which is accessed by only the owner
• The public which is accessed by any database user

71. What are the uses of synonyms?

Ans:

 Following uses of synonyms:

• It is used for hiding the real name and owner of the object
• It is used for providing public access to the object
• It is used for simplifying the SL statements
• It is used for getting a clear knowledge of tables, views, and the programs that use remote databases.

72. Name the types of joins used in writing SUBQUERIES.

Ans:

Following are the types of joins that are used in writing SUBQUERIES:

• Self-join
• Outer join
• Eui-join
• Anti-join
• Inner join
• Cross join

73. Name the types of modules that are used in Oracle.

Ans:

Following are the types of modules that are used in Oracle:

• Form module
• Menu module
• Object library module
• SL library module

74. What is the use of the ANALYZE command in Oracle?

Ans:

The ANALYZE command in Oracle is used for performing different types of functions like indexing, table formation, and for clustering. Following are the different uses of ANALYZE command:

• It is used for identifying migrated and chained rows of the table.
• It is used for validating the structure of an object.
• It is used for collecting the statistics of an object which is later used for storing them in the data dictionary.
• It is also used for deleting the statistics from the data dictionary.

75. Name the memory layers used in the Oracle shared pool.

Ans:

Following are the memory layers that are used in the Oracle shared pool:

• Library cache
• Data dictionary cache

76. Differentiate between TRUNCATE and DELETE command.

Ans:

Both the commands are used for removing the data from the database. Following is a table explaining the difference between these commands:

Truncate	Delete
It is a DDL operation	It is a DML operation
It is used for getting the free space from the object storage	It is not used for getting the free space from the object storage
It cannot be used for rolling back the structures of the database	It can be used for rolling back the database structure.

77. What is the use of join?

Ans:

Join is used for extracting the data from multiple tables by using the common column or the conditions.

78. Differentiate between SUBSTR and INSTR function.

Ans:

Following is a table explaining the difference between the SUBSTR and INSTR function:

SUBSTR	INSTR
This function is used for returning the sub-part that is identified by the numeric values	This function is used for returning the position of the number to the substring within the string

79. Differentiate between primary key and a unique key.

Ans:

Following is a table explaining the difference between primary key and a unique key

Primary key	Unique key
It is used for identifying each row of the table in a unique way	It is used for preventing duplicate values being getting created in the table column
There is only one primary key on the table	There can be multiple unique keys in the table
Null values are not held by the primary key	Null values can be held by the primary key
They have clustered index	They don’t have a clustered index

80. What is the main difference between the TRANSLATE command and REPLACE?

Ans:

The main difference between TRANSLATE and REPLACE command is that, TRANSLATE command is used translating characters one after the other in the string that is been provided while the REPLACE command is used for replacing only one character that is assigned from the string.

Example:

81. Name a few aggregate functions in Oracle.

Ans:

Following is the list of aggregate functions in Oracle:

• STDEV
• COUNT
• MIN
• MAX
• AVG
• SUM

82. What are the uses of aggregate functions in Oracle?

Ans:

The uses of aggregate functions in Oracle is that they are used for performing summary operations on a set of values that provide a single value.

83. Name a few set operators that are used in Oracle.

Ans:

Following are the set operators that are used in Oracle:

• UNION
• UNION ALL
• MINUS
• INTERSECT

84. What are the uses of set operators?

Ans:

Following are the uses of different types of set operators:

• The UNION operator is used for returning the rows from both the tables except for the duplicate values.
• UNION ALL operator is used for returning the rows from both the tables along with duplicate values.
• MINUS operator is used for returning the values from the first table that are not present in the second table.
• INTERSECT operator is used for returning only the common values from both the tables.

85. Name a few TCL statements that are used in Oracle.

Ans:

 Following are the set of TCL statements that are used in Oracle:

• COMMIT
• ROLLBACK
• SAVEPOINT

86. What are the uses of TCL statements?

Ans:

Following are the uses of TCL (Transaction Control Statement .statements:

• A COMMIT statement is used for making a permanent transaction.
• ROLLBACK statement is used for rolling back the state of DB to the last point of the commit.
• SAVEPOINT statement is used for specifying the transaction point, which can be rolled back later.

87. What is the main purpose of the TCL statement?

Ans:

 The main purpose of the TCL statement is that transactions always occurs when a set of SL statements are executed in one short. The controlling of these executed statements can be done with the help of the TCL statement. TCL stands for Transaction Control Statement.

88. What does the term DB objects mean?

Ans:

DB objects stand for database objects. It is used for storing data or references to the data.

89. Name a few DB objects in Oracle.

Ans:

Following are some of the DB objects in Oracle:

• Triggers
• Tables
• Views
• Constraints
• Stored procedures
• Indexes

90. What is the main difference between a nested table and the normal table?

Ans:

The main difference between a nested table and the normal table is that the nested table consists of databases that are stored in columns of the table while the normal table consists of all the nested table references. The nested tables have no rows.

91. Can images be saved in the database?

Ans:

Yes, images can be saved in the database.

92. How can one save an image in the database?

Ans:

An image can be saved in the database by using BLOB. BLOB stands for Binary Large Object. It is a data type which is used for saving images, videos and audio files in the database. The holding capacity of the data type is up to 4GB.

93. What does the term database schema mean?

Ans:

   Database schema is a collection of database objects that are owned by the database users who can either create or manipulate the new objects within the schema.

94.  Name a few database schemas that are used in Oracle.

Ans:

Following is the list of the database schema that is used in Oracle:

• Table
• Indexes
• Clusters
• Functions
• Stored procs
• View

95. Differentiate between view and table.

Ans:

Following is a table differentiating between view and table:

View	Table
It is used for saving SL query results.	It cannot be used for saving SL query results.
The data in view cannot be updated or deleted once created.	The data in the table can be updated as well as deleted once created.

96. What does the deadlock situation mean?

Ans:

The deadlock situation occurs when two or more users are waiting for the data simultaneously as the data are locked by each other, resulting in blocked user sessions.

97. What does the term index mean?

Ans:

 An index is a schema object which is used for searching the data efficiently within the table. The columns that accessed the most, will contain the indexes. These indexes can be either clustered or non-clustered.

98. Name a few attributes that are found in a cursor.

Ans:

 Following are the attributes that are found in a cursor:

• %FOUND
• NOT FOUND
• %ISOPEN
• %ROWCOUNT

99. What are the uses of various attributes in a cursor?

Ans:

Following are the various attributes in a cursor:

• %FOUND: It is used for returning the invalid cursor if the cursor is found declared but closed.
• NOT FOUND: It is used for returning the null if the fetching has not happened and the cursor is open.
• %ISOPEN: It is used for returning true if the cursor is open else it is false.
• %ROWCOUNT: It is used for returning the count of the rows that are fetched.

100. Differentiate between stored procedures and functions.

Ans:

 Following is a table explaining the difference between stored procedures and functions:

Stored procedure	Functions
Stored procedures can either return a single value or multiple values	Functions always return a single value
These can call for functions	These cannot call for stored procedures
These support blocks like try or catch	These do not support the blocks like try or catch
DML statements like insert, update, and return are included	DML statements cannot be included.