101) In PL/SQL, what are the differences between Stored Procedure and Stored Function?
The key differences between stored procedure and stored function are:
• Returning the value in a stored procedure is optional, while returning the value in a stored function is required.
• A stored procedure can have both input and output parameters, while a stored function can only have either an input parameter or an output parameter.
• Exception handling is possible in a stored procedure, whereas it is not possible in a stored function.
102) In PL/SQL, what is the Purpose of the DBMS_OUTPUT Package?
The PL/SQL output is shown on the screen using the DMS_OUTPUT package. get_line, put_Line, new_line, and many more are found in DBMS_OUTPUT. The put_line procedure, which is a part of the DBMS_OUPUT package, is used to display the information in the line.
103) Explain what PL/SQL package consists of?
PL/SQL consists of two major parts, they are package specification and package body.
1. Package specification: it acts as a public interface for your application which includes procedures, types, etc.
2. Package Body: It contains the code required to implement the Package Specification.
104) Explain what the benefits of PL/SQL Packages are?
These are the benefits of PL/SQL Packages
• We can store functions and procedures in a single unit called a package.
• Packages provide security to grant privileges.
• Functions and procedures, within the package, shares a common variable among them.
• Packages support even if the functions are overloaded.
• Packages enhance the performance even when multiple objects loaded into memory.
105) Explain different methods to trace the PL/SQL code?
Tracing code is a necessary technique to test the performance of the code during runtime. We have different methods in PL/SQL to trace the code, which are,
• DBMS_ TRACE
• DBMS_ APPLICATION_INFO
• Tkproof utilities and trcsess
• DBMS_SESSION and DBMS_MONITOR
106) What does it mean by PL/SQL Cursors?
In PL/SQL to retrieve and process more, it requires a special resource, and that resource is known as Cursor. A cursor is defined as a pointer to the context area. The context area is an area of memory that contains information and SQL statements for processing the statements.
107) What is the difference between Implicit and Explicit Cursors?
An implicit cursor used in PL/SQL to declare, all SQL data manipulation statements. An implicit cursor is used to declare SQL statements such as open, close, fetch, etc.
An explicit cursor is a cursor and which is explicitly designed to select the statement with the help of a cursor. This explicit cursor is used to execute the multirow select function. An explicit function is used PL/SQL to execute tasks such as update, insert, delete, etc.
108) What is a trigger?
It is a program in PL/SQL, stored in the database, and executed instantly before or after the UPDATE, INSERT and DELETE commands.
109) What are the uses of database triggers?
Triggers are programs that are automatically fired or executed when some events happen and are used for:
• To implement complex security authorizations.
• To drive column values.
• To maintain duplicate tables.
• To implement complex business rules.
• To bring transparency in log events.
110) How is an error different from an exception?
In PL/SQL, an error condition is called an exception. Design faults, coding mistakes, hardware failures, and so on can cause run-time errors. Exception handling in PL/SQL allows your program to continue operating in the presence of errors. When an error occurs, an exception is raised, stopping normal execution. The control then transfers to the exception-handling part of
your PL/SQL block or subprogram.
111) What is the difference between a mutating table and a constraining table?
• A table that is being modified by the usage of the DML statement currently is known as a mutating table. It can also be a table that has triggers defined on it.
• A table used for reading for the purpose of referential integrity constraint is called a constraining table
112) Explain exception handling in PL/SQL.
PL/SQL offers customized exception handling. When an error occurs, an error handling code is included in the program itself.
There are 3 types of exceptions –
Pre-defined exceptions – common errors that are already defined. Example – NO_DATA_FOUND
Undefined exceptions – the errors that do not have predefined names.
User-defined exceptions – handled by the code written by the user.
113) How Many Triggers can be Applied to a Table?
A maximum of 12 triggers can be added to a table.
114) What is a Mutating Table Error?
A mutating table error occurs when a trigger tries to update a row that is currently in use. It can be fixed by using views or temporary tables so that the database selects one and updates the other.
115) What does the PLVtab Enable you to do when you Show the Contents of PL/SQL Tables?
PLVtab enables you to do following when you show the contents of PL/SQL tables:
• Display or suppress the row numbers for the table values
• Show a prefix before each row of the table
• Display or suppress a header for the table
116) How can you Save or Place your msg in a Table?
To save a msg in a table, you either load the individual messages with calls to the add_text procedure or load sets of messages from a database table using the load_from_dbms procedure.
117) What are Pseudocolumns and how do they work? How can Pseudocolumns be used in Procedure Statements?
Pseudocolumns aren’t genuine table columns but they behave like them. Pseudocolumns are used to retrieve specific information in SQL statements. Although pseudocolumns are recognized by PL/SQL as part of SQL statements, they cannot be used directly in a procedural language.
The following are the pseudocolumns that are used:
• CURRVAL and NEXTVAL
• LEVEL
• ROWID
• ROWNUM
118) What is raise_application_error?
This procedure can be used to send user-defined error messages from stored subprograms. You can prevent returning unhandled exceptions by reporting failures to your application. It appears in two places, the executable section and the exceptional section.
119) In PL/SQL, how can you verify whether an Update Statement is Executed or not?
The SQL % NOTFOUND attribute can be used to determine whether or not the UPDATE statement successfully changed any records. If the last SQL statement run had no effect on any rows, this variable returns TRUE.
120) Explain the Day-to-day Activities in PL/SQL.
• Create database objects—tables, synonyms, sequences, etc.
• Implement business rules, create procedures, functions, etc.
• Impose business rules, create constraints, triggers, etc.
• Create cursors for data manipulation
