34) What are database links used for?
Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.
23) Does SQL*Plus also have a PL/SQL Engine?
No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.
2 How to Delete a Trigger?
To delete a trigger, you need to use the command DROP TRIGGER.
35) What does fetching a cursor do?
Fetching a cursor reads Result Set row by row.
6) What are the two types of exceptions.
Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.
It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.
40) Differ between DECODE and CASE.
DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.
select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);
CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.
An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.
There are several situations to use autonomous transactions like event logging and auditing.
What is the Basic Structure of PL/SQL?
PL/SQL uses a block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
19) What is the importance of SQLCODE and SQLERRM?
SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.
2) Differentiate between % ROWTYPE and TYPE RECORD.
% ROWTYPE is used when a query returns an entire row of a table or view. TYPE RECORD on the other hand, is used when a query returns column of different tables or views.
Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
e_rec smp %ROWTYPE
Cursor c1 is select smpno,dept from smp;
e_rec c1 %ROWTYPE
Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.
4 Explain the Day-to-day Activities in PL/SQL.
18) How many triggers can be applied to a table?
A maximum of 12 triggers can be applied to one table.
53) Mention what are different methods to trace the PL/SQL code?
Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes
2 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.
3 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: