Pl/Sql FAQs

PL/SQL DOCUMENTATION

1.EXPLAIN PL/SQL, PL/SQLBLOCK?

PL/SQL is a procedural language that has both interactive SQL and procedural language constructs such as iterations, conditional branching.

PL/SQL block is a block-structured language. Each block is supposed to perform one logical unit of job.

2.MAIN BLOCKS OF PL/SQL?

PL/SQL block is having 3 parts

· Declaration part

· Executable part

· Exception handling part

Declaration part: PL/SQL enforces the declaration of variables before their use in executable portion.

All variables (or) constants have to be declared in this block.

Syntax: variable name data type;

Executable part: this is main section of the block, all the procedural and SQL statements are defined here.

Exception handling part: this is used for responding to runtime errors encountered by a program.

MANDATORY BLOCKS IN PL/SQL: executable block in PL/SQL is the mandatory block other two blocks declarative and exception blocks are optional blocks.

3.WHAT IS NAMED BLOCK?

Named blocks are the blocks that have a name associated with 3 types

· Labeled blocks: blocks with a label that gives the block name.

· Sub program: consists of procedures and functions.

· Triggers: consists of pl/sql block that is associated with an event that occurs in the database.

4.WHAT IS ANONYMOUS BLOCK?

These blocks are generally constructed dynamically and execute only once.

Block is often issued from a client program to call a sub program in the database.

5.EXPLAIN % TYPE, %ROWTYPE IN PL/SQL?

% TYPE: it is used to give data type of predefined variable and database column.

Ex: declare

Item code number (10);

I code item code% type;

%ROWTYPE: it is used to provide record data type to a variable.

The variable can stop row of the table (or) row fetched from the cursor.

6.DATA TYPES IN PL/SQL?

· Scalar data type: number, character, Boolean, date/time.

· Composite data type: table, record.

· Reference data type: ref cursor.

· Lob types: (large objects) Bfile (variable stores locator of the file)

BLob (for storing large raw data like graphics or sound data)

Clob(stores location, which provides location of data)

7.EXPLIAIN PL/SQL TABLES, VARRAYS, NESTED TABLES?

PL/SQL TABLES: these are temporary array like objects used in pl/sql block.

These can have one column & a primary key.

These are declared in the declarative part of any block, sub program

Or package.

Syntax: STEP1: type is table of index by binary-integer.

STEP2:

NESTED TABLES: similarly to PL/SQL block along with adding the ability to store

nested tables within a database table will be there.

Syntax: type table name is table of table type;

VARRAYS: this are implemented differently, elements are inserted into varray starting at index1 upto maximum length declared in varray.

Syntax: type type-name is varry(max-size) of element-type(not null);

8.CAN WE PASS PL/SQL TABLE AS APARAMETER TO ANOTHER PROCEDURE OR NOT, IF SOHOW WILL IT BE PASSED? GIVE THE SYNTAX?

9.EXPLAIN AUTONOMOUS TRANSACTION, RESTRICT-REFERENCE AND EXCEPTION-INIT?

AUTONOMOUS TRANSACTION: it is used when ever in a transaction with in another transaction should be committed or rollback irrespective of parent transaction commit or rollback.

RESTRICT-REFERENCE: it is used to assert the purity level for the user-defined functions.

Syntax: PRAGMA RESTRICT-REFERENCES (function-name, [rnds], [wnds], [rnps], [wnps])

EXCEPTION-INIT: used for associating a named exception with in a particular oracle error

Syntax: PRAGMA EXCEPTION-INIT (exception-name, oracle error number)

10.WHAT IS PRAGMA?

Pragmas are compiler directives, it serves as instructions to the pl/sql compiler.

The compiler will act on the pragma during the compilation of the block.

11.WHAT IS EXCEPTION HANDLING IN PL/SQL& TYPES OF EXCEPTION?

Exception handling is used to handle the errors according to users way and functions

It will be used to generate error messages and replacing default messages.

These are 2 types standard & user defined exceptions.

STANDARD EXCEPTIONS 0R BUILT IN EXCEPTIONS: -

Oracle package standard had defined exceptions for certain common errors

Some of them are:

· Too-many-rows (ora-01422)

· No-data-found (ora-1403)

· Value-error (ora-06502)

· Zero-divide (ora-01476)

· Invalid-number (ora-01722)

· Dup-value-on-index (ora-00001)

· Program-error (ora-06501)

USER DEFINED EXCEPTIONS: The user defines these exceptions and these are used to take care of abnormal conditions that are application specific.

12.EXPLAIN SQL CODE & SQLERRM?

These are functions, which return error code and error message of the recent error.

SQL CODE: it returns error code as negative number.

For NO-DATA-FOUND it returns ‘+100’.

SQL ERRRM: it returns length of the messages in 512 characters, which includes code, message, tablename&column name.

Syntax: ercode: =sqlcode;

Er msg: =sqlerrm;

Insert into error table values (errcode, ermsg);

13.WHAT IS RAISE-APPLICATION-ERROR?

This is a procedure used to generate user-defined errors.

Syntax: raise-application-error (errorcode, errormessage, true/false);

Error code- (range is –20000 to-20999).

Error message (length is 2048 bytes).

True/false- true indicates error is put in stack

False is mentioned then the error replace all the previous errors.

14. What is a cursor?

Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

15. What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

16.For Update Of Clause: -when declaring the cursor itself we will be mentioning for update clause then we can update the records inside of the cursor.

17.Where Current Of Clause: -

18.PROCEDURE: -A procedure is a logically grouped set of SQL and PL/SQL statements that perform a specific task. it may or may not return a value.

Procedures are made up of

· Declaration part

· Executable part

· Exceptional part

Here declarative part and executable part are mandatory while exceptional part is optional.

Syntax: -CREATE OR REPLACE PROCEDURE procedure name {IN, OUT, INOUT}

{IS, AS}

Variable declaration;

Constant declaration;

Begin

PL/SQL subprogram body;

Exception

Exception block;

End;

19.FUNCTION: - A procedure is a logically grouped set of SQL and PL/SQL statements that perform a specific task. It returns a value.

Functions having

Declaration block

Executable block

Exception block

Syntax: - CREATE OR REPLACE FUNCTION function name {IN}

Return data type {IS, AS}

Variable declaration;

Constant declaration;

Begin

PL/SQL subprogram body;

Exception

Exception block;

End;

20.difference between procedure and function?

· Procedures may or may not return a value but function should return a value

· Procedures we cannot use inside of select statement

Functions are used inside of select statement

21.PARAMETER MODES (IN, OUT, INOUT)

IN: - when we pass the parameter in IN mode that will work like a constant inside a procedure.

OUT: -this is used to return a value.

INOUT: -this will be using in both way.

22.ACTUAL PARAMETERS: -while calling the procedure will pass the values this will be calling it as actual parameters

The procedure declaration variables will be receiving these values called FORMAL parameters

23.PROCEDURE OVERLOADING: -multiple procedures that are declared with the same name are called overloading procedures.

24.FUNCTION OVERLOADING: - multiple functions that are declared with the same name are called overloading functions.

25.PROCEDURE, FUNCTION FORWARD DECLARATION: -

26.PACKAGE: -A package is an oracle object, which holds other objects with in it

these objects may be

· Procedures

· Functions

· Cursors , which are logically related.

· Variables

· Constants

27.COMPONENTS OF PACKAGE: -

A package has usually two components

Specification

Body

A package specification declares the types, memory variables, constants, exceptions, cursors and subprograms that are a variable for use.

A package body fully defines cursors, procedures and thus implements the specification.

28.package body with out specification is possible or not?

29.can we define cursor inside with out package? If so how to call the cursor?

30.what is cursor variable?

31. We created specification and body, if we delete specification whether the body will present or not?

32.we have package body and specification inside of the package we are writing procedure to insert some thing to a table, if we delete the table, the above package will valid or not?

33.we have package and we have grants to execute that package inside of that we have table, here we don’t have privileges to this table? Whether this table will execute or not?

34.TRIGGERS:

Trigger is a pl/sql block, which will fire automatically whenever some event occurs like insert, update and delete.

Types of triggers: -

Event

Insert

Update level

Delete

Row level

Time statement level

Before

After

Row level triggers: -it will fire for each row

Statement level triggers: -it will fire only once for the whole statements.

35.can we use DLL command inside of trigger? If not then what is the alternative.

36.TRIGGER PREDICATES?

These are 3 types

· Inserting

· Updating

· Deleting

Whenever we want to do inserting inside of the trigger that time we will be using inserting predicate trigger.llly for updating and deleting.

37. : NEW and :OLD

Both will be used in triggers to get the new and old values.

In case of update both NEW and OLD are valid.

In case of delete only OLD is valid.

In case of insert only NEW is valid.

Both: NEW and :OLD will work only for row level triggers.