81) Define an Overloading Procedure.
An overloading procedure repeats the same name but with different parameter types. In Oracle, we can also apply an overloading procedure using a package.
82) What Data Types are Present in PL/SQL?
There are various kinds of data types present in PL/SQL. They are:
1. Scalar: The scalar data type is a one-dimensional data type with no internal components. CHAR, DATE, LONG, VARCHAR2, NUMBER, and BOOLEAN are some examples of the scalar data type.
2. Composite: The composite data type is made up of different data types that are easy to update and have internal components that can be utilized and modified together. For instance, RECORD, TABLE, VARRAY, and so on.
3. Reference: The reference data type stores pointers, which are values that relate to other programs or data elements. REF CURSOR is an example of the reference data type.
4. Large Object: The large object data type stores locators, which define the location of large items stored out of line such as video clips, graphic images, and so on. BLOB, BFILE, CLOB, and NCLOB are examples of the large object data type.
83) What is Exception Handling?
Exception handling is a mechanism that is implemented to deal with runtime errors. It can be adjusted in PL/SQL. PL/SQL provides the exception block that raises the exception, thus helping the programmer to find the fault and resolve it. When an error occurs, the program’s error handling code is included.
There are two different types of exceptions defined in PL/SQL:
• User-defined exception
• System-defined exception
84) What are the Various Types of Parameters in PL/SQL?
There are three types of parameters in PL/SQL. They are as follows:
• IN: The IN parameter allows you to send values to the procedure that is being called. The IN parameter can be set to default values. It behaves as a constant and cannot be changed.
• OUT: The OUT parameter returns a value to the caller. The OUT parameter is an uninitialized variable that cannot be used in expressions.
• IN OUT: The IN OUT parameter sends starting values to a procedure and returns the updated values to the caller. This parameter should be treated as an initialized variable and given a value.
85) What are PL/SQL Records?
PL/SQL records are a collection of values. To put it another way, PL/SQL records are a collection of many pieces of information, each of which is of a simpler type and can be associated with one another as fields.
Three types of records are supported in PL/SQL:
• Records based on tables
• Records created by programmers
• Records that are based on a cursor
86) Why do we use Index in a Table?
We use an index in a table to allow quick access to rows. For procedures that return a small percentage of a table’s rows, an index allows quicker access to data.
87) What is the Difference among Functions, Procedures, and Packages in PL/SQL?
• Functions: The main purpose of PL/SQL functions is to compute and return a single value. The functions have a return type in their specifications and must return a specified value in that type.
• Procedures: Procedures do not have a return type and should not return any value, but they can have a return statement that simply stops its execution and returns to the caller. Procedures are used to return multiple values; otherwise, they are generally similar to functions.
• Packages: Packages are schema objects that group logically related PL/SQL types, items, and subprograms. You can also say that packages are a group of functions, procedures, variables, and record TYPE statements. Packages provide modularity, which aids in application development. Packages are used to hide information from unauthorized users.
88) What is a Stored Procedure?
A stored procedure is a sequence of statements or a named PL/SQL block that performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as a schema object and can be nested, invoked, and parameterized.
89) What is the Overloading of a Procedure?
When the name of the same procedure is repeated with the parameters of different data types and parameters in different places, then that is referred to as procedure overloading.
90) What is meant by Expressions?
Expressions are made up of a series of literals and variables that are separated by operators. Operators are used in PL/SQL to manipulate, compare, and calculate data. Expressions are made up of two parts, operators and operands.
91) Which Cursor Attributes are the Result of a Saved DML Statement, when it is Executed?
The statement’s result is saved in four cursor attributes. The four attributes are:
• SQL% FOUND
• SQL% NOTFOUND
• SQL% ROWCOUNT
• SQL% ISOPEN
92) What is a Cursor? Why is it Required?
A cursor is a temporary work area that is created in system memory when an SQL statement is executed. A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data, which is retrieved from the database, to manipulate it. A cursor can hold more than one row but can process only one row at a time. A cursor is required to process rows individually for queries.
93) What are the Types of Cursors?
There are two types of cursors:
• Implicit Cursor: When PL/SQL executes an SQL statement, it automatically constructs a cursor without specifying one; these cursors are known as implicit PL/SQL uses implicit cursors for the following statements:
o INSERT
o UPDATE
o DELETE
o SELECT
• Explicit Cursor: A programmer declares and names an explicit cursor for the queries that return more than one row. An explicit cursor is a SELECT statement that is declared explicitly in the current block’s declaration section or in a package
definition. The following are the commands that are used for explicit cursors in PL/SQL:
o OPEN
o FETCH
o CLOSE
94) What is the Open Cursor Command Function?
When the OPEN cursor command is used to open a cursor, it performs the following operations:
• Set aside a processing memory region
• Paese the statement SELECT
• Use the memory addresses to assign values to input variables
• Recognize the active set of rows that meet the selection criteria
• Place the pointer exactly before the active set’s first row
95) How to Delete a Trigger?
To delete a trigger, you need to use the command DROP TRIGGER.
96) What are the Advantages of Stored Procedures?
Stored procedures have various advantages to help you design sophisticated database systems.
Some of the advantages of stored procedures as listed below:
• Better performance
• Higher productivity
• Ease of use
• Increased scalability
• Interoperability
• Advance security
• Replication
97) What are the Various Types of Schema Objects that can be Created by PL/SQL?
There are various types of schema objects that are created by PL/SQL.
Some of them are mentioned below:
• Stored procedures, packages, and functions
• Object tables, object types, and object views
• Database triggers
• Database links
• Cursors
• Table
• View
98) What is %TYPE?
The %TYPE property is used to declare a column in a table that includes the value of that column. The variable’s data type is the same as the table’s column.
99) What is %ROWTYPE?
The %ROWTYPE property is used to declare a variable that contains the structure of the records in a table. The variable’s data type is the same as the table’s columns.
100) Differentiate between a Temporary Tablespace and a Permanent Tablespace
A temporary tablespace is used to store temporary items such as sort structures, while a permanent tablespace is used to store things that will be used as the database’s genuine objects.
