Oracle Interview Questions and Answers

oracle interview questions and answers

Although a student becomes adept at handling any type of question after successfully completing the Oracle tutorial and certification from the institutes of GangBoard, this list of Oracle interview questions and answers will come in handy for a quick revision and when you are a bundle of nerves before the interview. However, this Oracle interview question and answer is for not only learned Oracle professionals but also beginners and those with no knowledge can use it to understand the terminologies and the fundamentals of Oracle. The Q & A includes topics from beginner, intermediate, to the most advanced level explained in Layman’s language.

Best Oracle Interview Questions and Answers

This field is growing at an astounding pace due to the high demand for certified professionals. Companies prefer individuals who are certified in the technology. We have observed that students who are adept in the use of Oracle and even score high marks in the certification fail to justify the same during the interview. This is one of the reasons why we have compiled the elaborate list of questions on Oracle and the answers are provided by the expert tutors who themselves are Oracle professionals and several are recruiters themselves for leading multinationals hiring Oracle certified professionals.

These Oracle interview questions and answers are based on mega trending topics. If you master most of these topics, there will be no Oracle interview question that you cannot answer and secure your dream job.

Q1) What is the use of Oracle ASM?

Answer:

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

Q2) Difference between normal file system and ASM?

Answer: Load balancing is high in ASM file system compare to the traditional file system. IN oracle asm we call it as 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.

Q3) What is ORACLE AFD?

Answer: Oralce asm Filter driver(ASMFD). It is a kernel module resides in the I/O paths of the oracle ASM disks. It is optional one. After completed 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 cleanup the oracle asmlib. Usually when the server restarted the need rebind the disk devices with oracleasm, to simplifies this the Oracle ASMFD we need to configure. It will allow only oracle I/O requested and will rejects the invalid or non Oracle I/O requests.

Q4) How to create Disk Group?

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

To create the disk we need to use the command

oracleasm createdisk ORADATA /dev/sdb1

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

Q5) How to list the Disk Groups?

Answer:

  • 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

Q6) How to drop Disk groups?

Answer:

  • 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.

Q7) Difference between the ORACLE asmlib and ORACLE afd?

Answer:

  • Oracle asm usually rebind the asm files once the server restarted
  • Oracle asm might allow non oracle I/O to Oracle files
  • Oracl 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 verion

Q8) How to start ASM instance?

Answer: 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 minimal amount of SGA compare to normal instance.

Q9) What is the use of ASM instance?

Answer: 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 minimal amount of SGA compare to normal instance. In cluster database we will have separate ASM instance for each nodes.

Q10) What is ACFS file system?

Answer:

  • Oralce 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

 Q11) What is the use of OCR file?

Answer:

OCR is a file that contain the information about the Oracle database configuration information to manages cluster.  It will create and placed on shared storage while installation oracle cluster ware.

Q12) What is the use of Voting Disk?

Answer:

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

Q13) What is the use of CRS in oracle RAC?

Answer:

CRS- cluster ready service provides the cluster software which is installed in each hosts via OUI cluster installation.  Oracle clusterwate manages as called as CRS resources. Like database , instances VIP address , listener and services

Q14) What are all the cluster ware storage?

Answer:

  • Raw devices
  • OCFS file system
  • ASM file system

Q15) What is cache fusion in oracle RAC?

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

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

Q16) How to start database in Oracle Rac database?

Answer: 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

Q17) How the Function and Procedure are differ to each other ?

Answer:

  • Functions are generally used for computation purpose, whereas procedures are used to implement the business logic.
  • Functions can be called through SQL query, but procedure can’t.
  • Function must returns a value, whereas procedure may or may not.

Q18) Can we create package body, without creating a package specification ?

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

Q19) Explain the mutation of the trigger

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

Q20) How to generate primary key on a table without using sequence ?

Answer: We can refer the following example for that :

DECLARE

a number(2) := 10;

BEGIN

— while loop execution

WHILE a < 20 LOOP

dbms_output.put_line (‘value of a: ‘ || a);

a := a + 1;

IF a > 15 THEN

— terminate the loop using the exit statement

EXIT;

END IF;

END LOOP;

END;

Q21) How to handle the exception for duplicate record entry?

Answer:  We can use dup_val_index as an exception handler.

Q22) What will happen, if we will write the exception section like below :

Answer: EXCEPTION

WHEN others THEN

dbms_output.put_line(‘Error!’);

WHEN no_data_found THEN

dbms_output.put_line(‘No such customer!’);

no_data_found exception will never be occurred as it will always get the ‘others’ exception handler at first place.

Q23) What are the PL/SQL cursors?

Answer: Oracle uses workspaces to execute the SQL commands. That means, when Oracle processes a SQL 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.

Q24) What is returned by the cursor attribute SQL%ROWCOUNT?

Answer: It returns the number of rows that are processed by a SQL statement.

Q25) We have the following datasets from team_a and event_a table:

sql> SELECT * FROM team_a;

+—-+————–+

| no | fname        |

+—-+————–+

| A1 | A. Das       |

| A2 | B. Moothoot  |

| A3 | C. Paul      |

+—-+————–+

sql> SELECT * FROM event_a;

+—-+—————-+———–+

| no | action         | a_id      |

+—-+—————-+———–+

| B1 | 10 meter dash  | A2        |

| B3 | cross-country  | A5        |

| B4 | triathalon     | NULL      |

Now, what should be the answer of the following query :

SELECT * FROM team_a WHERE no NOT IN (SELECT a_id FROM event_a)

Answer:  The answer should be null as if there is any null exists in a dataset, then the output of the whold set is becoming the null only.

Q26) 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 ?

Answer:  Best way to write a function, which should have meter to inches conversion logic and should be execute from the SQL statements.

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

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

Q28) Can we use one select query in another select query ?

Answer: Yes. Using subquery.

Q29) 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 query :

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

Answer:

Output should be like this :

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

Q30) 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

Answer: C – NULLIF

Q31) 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.

Answer: D – All are true.

Q32) Check the follwing example :

insert into emp values (1,’abc’);

insert into emp values (2,’bcd’);

insert into emp values (3,’cde’);

savepoint sp1;

insert into emp values (4,’def’);

insert into emp values (5,’efg’);

insert into emp values (6,’fgh’);

savepoint sp2;

insert into emp values (7,’ghi’);

insert into emp values (8,’hij’);

savepoint sp3;

Now if you want to keet id 7 and 8, but not 4,5,6. So you have executed the below command :

rollback to sp1;

What will happen ?

Answer: After savepoint sp1, all the inserted records will be deleted.

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

Answer: 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.

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

Answer: 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.

Q35) Can any view have any DML operation ?

Answer: yes. Using of instead of triggers, a view can manipulate the data.

Q36) Can sorting is possible using a column alias?

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

Q37) What is the requirement of MERGE statement ?

Answer:  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.

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

Answer: The ALTER TABLE statement.

Q39)  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

Q40) How do you find a number as integer or floating ?

Answer:  select case when 111 – floor(111) < 1 and 111- floor(111) > 0 then ‘Decimal’ else ‘Integer’ end from dual;

Note : Taking example of an integer value : 111

Q41) What are the NVL and the NVL2 functions in SQL? How do they differ?

Answer:  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 parameter and NVL2 3 parameters.

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

Answer: SELECT Salary, salary_rank FROM

(

SELECT DISTINCT Salary,RANK() OVER (ORDER BY salary DESC) AS salary_rank  FROM hr.Employees

) where salary_rank <= 4;

Q43) How do you find a duplicate record?

Answer: select attributeid,count(1) from attr_adi group by attributeid having count(1) > 1;

Q44) How to Delete a duplicate record?

Answer: delete from attr_adi where rowid not in (select min(rowid) from attr_adi group by attributeid);

Q45) How to a create alike table structure, but without records of an existing table ?

Answer: create table emp_1 as select * from emp where 1=2 ;

Q46) How to replace all the numeric characters with ‘NUMBERS’ and Alphabets with ‘ALPHABETS’

Example:

[‘a’, ‘z’, ‘E’, 3, ‘f’, 6, 2, 0, ‘Q’]

should output:

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

Ans : SELECT col1, case when upper(col1) = lower(col1) then ‘NUMBERS’ else ‘ALPHABETS’ end as alphanumeric from table;

Q47)  What is Query to find Nth highest salary for employee using With Clause

Answer:

WITH NTH AS

( SELECT Name, Sal, EID, RN = ROW_NUMBER()

OVER (ORDER BY Sal DESC)

FROM Emp

)

SELECT Name, Sal, EID

FROM NTH

WHERE RN = N

Note : N means any numbers can be put

Q48) How to find a table name with its owner ?

Answer: Select owner,table_name from all_tables where table_name = <table> ;

Q49) How to put a condition with case incensitivity

Answer: select * from emp where lower(ename) = lower(‘John Smith’);

Q50) How can you fetch first 5 characters of the string?

Answer: select substr(name,1,5) from table;

Q51) What should be the SQL for the following output (rounded to next 10):

Input OutPut
13.2 20
9.9 10
101.001 10

Answer: select cell (cell(101.000001)/10)*10 from dual;

Q52) Whether Foreign key contains null values ?

Answer: No. Although logically it can’t be as any primary key can not contains any null values. But Oracle gives this facility for the data conveniency.

Q53) How do you declare a user-defined exception?

Answer: User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. Syntax − <exception_name> EXCEPTION;

Q54) How to add the email validation using only one query?

Answer:

SELECT email from emp

where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

Q55) What does the term “Oracle” mean?

Answer: Oracle is 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.

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

Answer: 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

Q57) Name the language used in Oracle.

Answer: Oracle is developed using C language.

Q58) Differentiate between varchar and varchar2.

Answer: 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

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

Answer: 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

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

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

  • Tablespaces
  • Schema objects of the database

Q61) What do you understand by the term tablespace?

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

Q62) What do you understand by the term SYSTEM tablespace?

Answer: 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.

Q63) What do you understand by the term table in Oracle?

Answer: 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.

Q64) What do the following numbers mean in the below-given version:

Oracle version 9.3.0.5.0?

Answer: 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

Q65) Name the syntax used for finding the current date and time in the format “YYYY-MM-DD”.

Answer: The syntax used for finding the current date and time in the format “YYYY-MM-DD” is as follows:

SELECT TO_CHAR (SYSDATA,” YYYY-MM-DD HH24:MI:SS”) “Current_Date” FROM DUAL;

Q66) How to find the current date and time in Oracle?

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

Q67) Name the syntax used for converting a date into a char.

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

Syntax: to_char() function

Q68) Name the types of database objects in Oracle.

Answer: Following are the types of database objects in Oracle:

  • Tables
  • Tablespaces
  • Views
  • Indexes
  • Synonyms

Q69) What are the uses of different types of database objects in Oracle?

Answer: 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 the 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

Q70) Name the different types of synonyms.

Answer: Following are the different types of synonyms:

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

Q71) What are the uses of synonyms?

Answer: 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 SQL statements
  • It is used for getting a clear knowledge of tables, views, and the programs that use remote databases.

Q72) Name the types of joins used in writing SUBQUERIES.

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

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

Q73) Name the types of modules that are used in Oracle.

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

  • Form module
  • Menu module
  • Object library module
  • SQL library module

Q74) What is the use of the ANALYZE command in Oracle?

Answer: 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.

Q75) Name the memory layers used in the Oracle shared pool.

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

  • Library cache
  • Data dictionary cache

Q76) Differentiate between TRUNCATE and DELETE command.

Answer: 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.

Q77) What is the use of join?

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

Q78) Differentiate between SUBSTR and INSTR function.

Answer: 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

Q79) Differentiate between primary key and a unique key.

Answer: 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

Q80) What is the main difference between the TRANSLATE command and REPLACE?

Answer: 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:

TRANSLATE (‘Missisippi”,is”,16) => M166161pp1

REPLACE (‘Missisippi”,is”,16) => M16s16ippi

Q81) Name a few aggregate functions in Oracle.

Answer: Following is the list of aggregate functions in Oracle:

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

Q82) What are the uses of aggregate functions in Oracle?

Answer: 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.

Q83) Name a few set operators that are used in Oracle.

Answer: Following are the set operators that are used in Oracle:

  • UNION
  • UNION ALL
  • MINUS
  • INTERSECT

Q84) What are the uses of set operators?

Answer: 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.

Q85) Name a few TCL statements that are used in Oracle.

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

  • COMMIT
  • ROLLBACK
  • SAVEPOINT

Q86) What are the uses of TCL statements?

Answer: 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.

Q87) What is the main purpose of the TCL statement?

Answer: The main purpose of the TCL statement is that transactions always occurs when a set of SQL 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.

Q88) What does the term DB objects mean?

Answer: DB objects stands for database objects. It is used for storing data or references to the data.

Q89) Name a few DB objects in Oracle.

Answer: Following are some of the DB objects in Oracle:

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

Q90) What is the main difference between a nested table and the normal table?

Answer: The main difference between a nested table and the normal table is that the nested table consists of databased 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.

Q91) Can images be saved in the database?

Answer: Yes, images can be saved in the database.

Q92) How can one save an image in the database?

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

Q93) What does the term database schema mean?

Answer: 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.

Q94)  Name a few database schemas that are used in Oracle.

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

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

Q95) Differentiate between view and table.

Answer: Following is a table differentiating between view and table:

View Table
It is used for saving SQL query results It cannot be used for saving SQL 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

Q96) What does the deadlock situation mean?

Answer: 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.

Q97) What does the term index mean?

Answer: 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.

Q98) Name a few attributes that are found in a cursor.

Answer: Following are the attributes that are found in a cursor:

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

Q99) What are the uses of various attributes in a cursor?

Answer: 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.

Q100) Differentiate between stored procedures and functions.

Answer: 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.

Q101) Name a few triggers.

Answer: Following are the triggers that are found in Oracle:

  • Row-level
  • Statement level

Q102) Name a few temporal data types in Oracle.

Answer: Following are the different types of data types in Oracle:

  • Date data type
  • Timestamp data type
  • Interval data type

Q103) What are the uses of temporal data types in Oracle?

Answer: Following are the uses of temporal data types:

  • A date data type is used for different formats of date
  • A timestamp data type is used for different formats of time stamps.
  • Interval data type is used as intervals between dates and timings.

Q104) Name the syntax used for converting a string into a date.

Answer: Following is the syntax used for converting a string into a date:

Syntax: to_date (string, format)

Leave a Reply

Your email address will not be published. Required fields are marked *

Looking for Online Training