LATEST Oracle Apex Interview Questions [2020] & Answers | ACTE

LATEST Oracle Apex Interview Questions & Answers

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

About author

Praveen (Sr Technical Project Manager )

He is a Proficient Technical Expert for Respective Industry Domain & Serving 9+ Years. Also, Dedicated to Imparts the Informative Knowledge's to Freshers. He Share's this Blogs for us.

(5.0) | 16547 Ratings 4710

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:

  • oracleasm createdisk ORADATA /dev/sdb1

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 :

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

    Subscribe For Free Demo

    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:

    • 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 occur as it will always get the ‘others’ exception handler in first place.

    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 :

    IdNamePh_nonull_col
    1ABC234null
    2XYZnot null
    3MNP345null

    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.

    Course Curriculum

    UPGRADE Your Career with Oracle Apex Training By Highly Experienced Faculty

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    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:

    •  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

    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:

    • SELECT Salary, salary_rank FROM
    • (
    • SELECT DISTINCT Salary,RANK(. OVER (ORDER BY salary DESC. AS salary_rank  FROM hr.Employees
    • ) where salary_rank <= 4;

    43. How do you find a duplicate record?

    Ans:

    • select attribute id,count(1. from attr_adi group by attribute id having count(1. > 1;

    44. How to Delete a duplicate record?

    Ans:

    • delete from attr_adi where rowid not in (select min(rowid. from attr_adi group by attributeid.;

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

    Ans:

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

    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:

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

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

    Ans:

    • 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

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

    Ans:

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

    49. How to put a condition with case insensitivity

    Ans:

    •  select * from emp where lower(ename. = lower(‘John Smith’.;

    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.

    Course Curriculum

    Learn Oracle APEX Certification Course & Advance Your Career

    Weekday / Weekend BatchesSee Batch Details

    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:

    • <exception_name> EXCEPTION;

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

    Ans:

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

    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:

    • to_char(. function

    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
    Oracle Apex Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    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:

    • TRANSLATE (‘Mississippi”,is”,16. => M166161pp1
    • REPLACE (‘Mississippi”,is”,16. => M16s16ippi

    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.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free