1) What is PL/SQL?
It is defined as SQL having Procedural features of Programming Language i.e. Procedural Language extension of SQL.
2) Enlist the section of PL/SQL block.
It consist of three block
- Declaration Section
- Execution Section
- Exception Handling or Error Section
3) Give the advantage of PL/SQL.
- Structured Block
- Better Performance
- Error Handling
- Procedural Language Capability
4) Enlist the types of Data types in PL/SQL.
- Scalar Data types: e.g. number, date, char, long, Boolean etc.
- Composite Data types: e.g. Record, Table etc.
5) Write syntax to declare the variable?
Syntax:
DECLARE
Var_name datatype [NOT NULL := value]
e.g. tut_example varchar2(10) NOT NULL :="Best tutorial"
Syntax: to directly assign values to variable from database.
SELECT column_name
INTO variable_name
FROM table_name
[WHERE condition];
6) Write some programming constructs supported by PL/SQL.
- Variable and Constant
- Flow Control
- Cursor Management
- Exception Handling
- Triggers
7) Write single statement to concatenate Tutorials and Examples.
Website: = 'Tutorials' || 'Examples';
8) Give the precedence order among (AND, OR, NOT, NULL).
Highest order: NOT
Lowest order: OR
9) What is Trigger in PL/SQL?
Trigger is initiated when query is fetched for any INSERT, UPDATE, DELETE etc where there is change in the structure of Database. It maintains the "Referral Integrity" of the PL/SQL.
10) Enlist the command that is use to create PL/SQL Packages.
- CREATE PACKAGE command is used for creating the specification part.
- BODY command is used for creating the body part.
11) What are the uses of Trigger?
- Creating validation mechanisms
- Creating logs to register the use of a table
- Update other tables
12) Differentiate between Execution of Trigger and Stored Procedures ?
Trigger is executed automatically whereas Stored Procedures has to be invoked.
13) Which command is used to delete the package?
DELETE PACKAGE is used to delete the package.
14) Define CURSOR in PL/SQL.
It is a temporary area which is generated at the time of SQL statement execution in the system memory. It contains information about select statement and row data affected in database.
15) Enlist the types of cursor.
- Implicit Cursor: It is created when DML statement is executed e.g. INSERT, UPDATE AND DELETE.
- Explicit Cursor: It is created when we execute SELECT Statement which returns more then one rows.
16) Explain Raise_application_error.
It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.
17) When is a declare statement required?
DECLARE statement is used by PL SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.
18) 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.
19) What packages are available to PL SQL developers?
DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
20) What is SQL*Loader?
SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file.
21) Explain the concept of exception.
An exception occurs when unwanted situation arises. The situation can be exceptional to normal functioning of the program. It can occur due to system error, user error and application error.
22) Define user defined exceptions.
We use user defined exception only when oracle doesn't raise its own exception. In this procedure we raise an exception by using RAISE command.
23) Define Row level trigger.
Row level trigger is fired each time a row is affected by DML statements like Insert, Update and Delete. When no rows affected, the trigger is not executed at all.
24) Define Statement level triggers.
It is fired when statement affects rows in a table but the processing required is completely independent of the number of rows affected.
25) Explain how can you save or place your messages in a table?
To save msg in a table, you can do it in two ways:
- Load individual messages with calls to the add_text procedure.
- Load sets of messages from a database table with the load_from_dbms procedure.
