PL/SQL (Quick Interview Guide)

1) PL/SQL is a procedural language, extension to SQL. It offers modern software engineering features such as,
a) Data Encapsulation
b) Exception handling
c) Information hiding & Overloading using Packages
d) Object orientation

2) PL/SQL is not an Oracle product, but the technology used by the oracle server and by certain Oracle tool. Blocks of PL/SQL are passed to and processed by a PL/SQL Engine, which may reside within the tool or within the Oracle Server. When the blocks of PL/SQL are submitted from Oracle Pre-compiler such as Pro*C or Pro*Cobol program, user exit, iSQL*Plus, or Server Manager, the PL/SQL Engine in the Server processes them. It separates SQL Statements and sends them individually to the ‘SQL Statements Executor’.

3) A single transfer is required to send the block of PL/SQL from the application to the Oracle Server, thus improving performance. PL/SQL Code is stored in Oracle Server as a subprograms that can be referenced by any number of applications connected to the database.

4) Many Oracle Tools like Oracle Forms Developer, Oracle Reports Developer, Oracle Graphics Developer have their own PL/SQL Engine, which is independent of the engine present in Oracle Server.
Procedural Statements are processed by Procedural Statement Executor, which resides in PL/SQL Engine and SQL Statements are processed by SQL Statement Executor, which resides in Oracle Server. This reduces the work that is sent to the oracle server and number of memory cursors that are required.

5) Benefits of PL/SQL:

a) It can be used to group SQL statements together within a single block and to send the entire block to the server in a single call, thereby reducing network traffic. Without PL/SQL, the SQL Statements are sent to the Oracle Server one at a time.

b) PL/SQL can also operate with Oracle Server application development tools such as Forms & Reports. By adding procedural processing power to these tools, PL/SQL enhances performance.

c) Procedures & Functions of Forms & Reports are different from those stored in the Database, although there general structure is same. Because stored Subprograms are database objects and are stored in data dictionary and can be accessed from any number of applications, including Forms & Reports.

6) Oracle Portal is an Oracle Portal Developer tool previously known as WebDB.

7) PL/SQL Contains one or more blocks. They are mainly of two types:

a) Anonymous Blocks: Unnamed block which are declared at the point in an application where they are to be executed and are passed to the PL/SQL Engine for execution at run time.
b) Subprograms: Named PL/SQL Blocks that can accept parameters and can be invoked. We can declare them either as procedures or functions.

8) Block structure for Anonymous Block is:
a) Declare(Optional)
b) Begin(Mandatory)
c) Exception(Optional)
d) End(Mandatory)
Declare, Begin & Exception are not followed by Semicolons. End and all other PL/SQL Statements require semicolons to terminate the statement.

9) Variables: This are used mainly for the following:
a) Temporary storage of Data
b) Manipulation of Stored Values
c) Reusability
d) Ease of Maintenance
Forward references are not allowed, you must declare a variable before referencing it in other statements.

There are 2 main types of Variables:
> PL/SQL Variables:
a) Scalar (Holds a Single Value with Scalar Datatype- Varchar2, Number, Date.etc)
b) Composite (Eg: Record Datatype, Table Datatype)
c) Reference (Hold values called pointers, Eg: Refcursor)
d) LOB (Hold Large objects like Graphic Images)

> Non PL/SQL Variables: Bind and Host Variable

10) Constant Variables Must be initialized in its declaration, otherwise, you get a compilation error. Other variables are also initialized with Default Reserved Word or with assignment operator(:=). It is always a good programming practice to initialize all variables.

11) Following points are important while declaring a variable:
a) Two Variables can have the same name provided they are in different blocks.
b) The Variable name should not be the same as the name of Table Column.
c) Variable name must not be longer than 30 characters. The first character must be a letter; the remaining characters can be letters, numbers, or special symbols

12) %Type Provides the Datatype of Database column to the Scalar Variable
%Rowtype is used to Declare a Record based on a Collection of Table columns.

13) Non PL/SQL Variables declared in Host Environment can be Referenced in PL/SQL Anonymous blocks by prefix Colon (:) but not in Sub-Programs.
While Declaring this Variable Do not specify Size for Datatype Number but Always Specify the Size for Datatype Char & Varchar2

14) Lexical Units can be classified as:
a) Delimeters (Arithmetic, Relational, Assignment & Comment Operators)
b) Identifiers (Variables Name)
c) Literals (Character & Date Literals must Enclosed in Single Quotes)
d) Comments (Starts with /* and Ends with */ )

15) We Cannot use ‘DECODE’ and Group Functions in PL/SQL Statements. However they can be used in SQL Statements of PL/SQL blocks.

16) The Scope of an identifier is that region of a Program unit from which you can reference the identifier. An identifier is visible only in Regions from which you can reference the identifier using unqualified name.
If the Block does not find the identifier declared locally, it looks up to the declarative section of the Enclosing(Parent) blocks. The block never looks down to Enclosed(Child) blocks or sideways to sibling blocks.

17) PL/SQL does not Directly Support DDL & DCL Statements. Select Statement Embedded in PL/SQL must require INTO Clause, Where Clause is optional.

18) Output Variables: PL/SQL Variables through which Values pass from Database to the PL/SQL Blocks
Input Variables: PL/SQL Variables through which Values pass from the PL/SQL Blocks to the Database

19) SQL Cursor: Whenever SQL Statement is issued, Oracle Server opens an area of memory in which the command is Parsed & Executed. This area is called a Cursor.
There are 2 types of Cursor:
a) Implicit Cursor (Used for Single Row Operations, No need to Open & Close)
b) Explicit Cursor (Used for Multiple Row Operations, Declared Explicitly by User and need Opening, Fetching & Closing always)
Explicit Cursor points to the current row in the Active Set. The set of rows returned by a multiple row query is called the Active Set. Close Statement Releases the Active Set of Rows.

20) Cursor Attributes: This are used in PL/SQL Statements only to Gather Information about the Execution of DML Statements. They are mainly,
a) SQL%ROWCOUNT (No. of Rows affected by SQL Statements)
b) SQL%FOUND (Evaluates to True if 1 or more rows are affected)
c) SQL%NOTFOUND (Evaluates to True if no rows are affected)
d) SQL%ISOPEN (Always evaluates to False because PL/SQL Closes Implicit Cursors immediately after they are executed)

21) There are 3 Types of Control Structures:
a) IF Statement
b) Case Expressions
c) Loop Statement

22) There are 3 forms of IF Statement:
a) IF-THEN-END IF
b) IF-THEN-ELSE-END IF
c) IF-THEN-ELSIF-END IF

23) Operations with Null:
a) True AND Null = Null
b) False AND Null = False
c) True OR Null = True
d) False OR Null = Null

24) There are 3 Types of Loops:
a) Basic Loop (Performs Repetitive Actions without Condition)
b) FOR Loop (Performs iterative Actions based on a count)
c) WHILE Loop (Perform iterative Actions based on a Condition)

25) Always Use,
a) Basic Loop when the Statements inside the loop must execute at least once.
b) While Loop if the condition has to be evaluated at the start of each iteration.
c) For Loop if the number of iterations is known.

26) There are 2 types of Composite Datatypes:
a) PL/SQL Records
b) PL/SQL Collections
1) INDEX BY Table
2) Nested Table
3) VARRAY

27) Advanced Explicit Cursor:
a) We can pass parameters to the Cursor. Each Formal Parameters in the Cursor Declarations must have a corresponding Actual Parameter in the Open Statement. When the Cursor is Opened you pass values to each of the Parameter by Position or by name. You can pass values from PL/SQL or Host Variables or from literals.
b) ‘For Update of col_name(s)’ clause in the cursor query is used to lock the affected rows when the cursor is opened, because Oracle Server releases locks at the End of Transaction. This is the last clause in Select statement even after Order by Clause if present. Optional ‘NOWAIT’ Keyword is used to tell the Oracle not to Wait if the requested number of rows are locked by another user, Control immediately returned to the program to do other work before trying to acquire the Lock. If this keyword is not used, Oracle waits until the rows are available.
c) ‘Where Current Of cursor_name’ clause is used for referencing the current row from an Explicit cursor.

28) Exception: An Exception is an identifier in PL/SQL that is raised during the execution of block. A block terminates when PL/SQL raises an exception. If PL/SQL successfully handles the exception, then the exception does not propagate to the enclosing block or calling environment, the block terminates successfully. However if there is no corresponding exception handler, block terminates with failure & exception is propagated to the calling environment
There are 3 types of Exception:
a) Predefined Exception
b) Non Predefined Exception
c) User Defined Exception

29) Most oftenly used predefined exceptions are:
a) NO_DATA_FOUND (Select Statement returns no rows)
b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)
c) INVALID_CURSOR (Illegal cursor operations occured)
d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)
e) INVALID_NUMBER (Conversion of Character to number fails)
f) ZERO_DIVIDE
g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)

30) Trapping a Nonpredefined Oracle Server Exception:
a) Declare the name for the exception in Declarative section
b) Associate the declared exception with Standard Oracle Server error using ‘Pragma Exception_Init’, which is the compiler directive that tells the compiler to associate an exception name with Oracle error number
c) Reference the declared exception with corresponding exception handler routine.

31) When an exception is trapped in the When Others exception handler which is the last handler, 2 functions are used to identify those errors:
a) SQLCODE (Returns the error code)
b) SQLERRM (Returns the error message)
This functions are not directly used in SQL statement, instead they must assign their values to local variables and then this variables are used in SQL Statements.

32) Trapping User Defined Exception:
a) Declare the name for the exception in Declarative section
b) Use the ‘Raise’ Statement to raise the exception explicitly within the executable section.
c) Reference the declared exception with corresponding exception handler routine.

33) When a subblock handles an exception, it terminates normally and control resumes in the enclosing block, the remaining executable actions in that block are bypassed.

34) ‘Raise_Application_Error (Error_No, Error_Message)’ procedure communicate a predefined exception interactively by returning a nonstandard error code & message. They can be used either in Executable or Exception section or both. Error Number ranges from –20000 to –20999.

35) Modularization: It is the process of breaking up large blocks of code into smaller groups of called modules. After code is modularized, the modules can be reused by the same program or shared by other programs. It is easier to maintain & debug code of smaller modules than a single large program. Also modules can be easily extended for customization by incorporating more functionality, if required, without affecting the remaining modules of program.

36) Block Structure for PL/SQL Program is,
Header IS/AS
Declarative Section
Begin
Executable Section
Exception
Exception Section
End ;

37) Procedure: It is a type of Subprogram that performs an Action. It can be stored in Database as a Schema object for Repeated Execution. ‘Create Procedure’ Statement Stores the Source Code in the Data Dictionary even if procedure contains compilation Errors. When Procedure is Created, P-Code(Pseudocode) is generated based on Parsed code. PL/SQL Engine Executes this code when Procedure is Invoked.

38) Formal Parameters: Variables Declared in the Parameter list of Subprogram Specification.
Actual Parameters: Variables referenced in Parameter list of Subprogram Call

39) There are 3 Types of Parameters:
a) IN (Passes a Constant value from the Calling Environment into the procedure)
b) OUT (Passes a value from the procedure to the Calling Environment)
c) IN OUT (Passes a Value from the Calling Environment into the procedure & a possibly different value from the procedure back to the calling Environment using the same Parameter)

40) By Default IN Parameters are passed by Reference and OUT & IN OUT are passed by value
41) There are 3 Methods for Passing Parameters:
a) Positional (List Values in the order in which the Parameters are declared)
b) Named Association (List Values in Arbitrary Order by associating each one with its parameter name, using special syntax( =>)
c) Combination (List the first Value positionally, and the remainder using the special syntax of the named method)

42) Function: It is a named PL/SQL Block that can accept parameters and be invoked. It is used always to compute a value. A function is called as part of an expression i.e. we can invoke a function from Select Statement. DML Cannot be used in Functions.

43) Difference between Procedure & Functions:

Procedures
1) Executes as PL/SQL Statement
2) Do not Contain Return Clause in the Header
3) Can Return none, one, or many Values
4) Can Contain a Return Statement

Functions
1) Invokes as part of an Expression
2) Must contain a Return clause in the the Header
3) Must Return a Single Value
4) Must Contain at least one Return Statement

44) Package:

a) A Package usually has a Specification & Body stored separately in the Database. A Specifications is an interface to your Applications. It declares the types, variables, constants, exceptions, cursors, Pragmas which are directive to the compiler & subprograms available for use. The body fully Defines Cursors & Subprograms.

b) When you call a packaged PL/SQL Constructs for the first time, the whole package is loaded into memory. Thus later calls to constructs in the same package require no disk input/output.

c) Public package constructs are those that are declared in package specification & defined in the package body. Private package constructs are those that are defined solely within package body.

d) We can have specification without body but we cannot have body without specification.

e) We must declare a variable before it can be referred and define subprograms before called from other subprograms, because PL/SQL does not allow forward references.

f) Changes to the package body do not require recompilation of dependant constructs, whereas changes to the package specification require recompilation of every stored subprogram that references the package.

g) Data Hiding: The package body hides the definition of the private constructs so that only the package is affected if the definition changes.

h) Overloading: We can overload procedures & functions of package only i.e. We can create Multiple subprograms with the same name in the same package, each taking parameters of different number or datatype.

i) One time only Procedure: It is executed only once, when the package is first invoked within the user session. The keyword END is not used at the end of this procedure.

j) Side Effects: This are changes to database tables or public packaged variables declared in package specification. Side effects delay the execution of Query. We can use Pragma Restrict_References to ask PL/SQL Compiler to verify that function has only the side effects that you expect.

k) The state of a package variable or cursor persists across transaction within a session. The state does not persist from session to session for the same user, nor does it persist from user to user.

45) Oracle Supplied Packages:

a) DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL. Dynamic SQL Statements can be created dynamically at runtime using Variables. Such statements like DML, DDL & DCL cannot be executed statically in PL/SQL.
Eg: You use dynamic SQL to create a procedure that operates on a table whose name is not known until Runtime.
‘Execute Immediate’ statement can also perform dynamic single row Queries.

DBMS_SQL Package uses following procedures or functions:
1) OPEN_CURSOR (Opens a new cursor & assigns a cursor ID number)
2) PARSE (Parses DDL & DML Statement)
3) BIND_VARIABLE (Binds the given value to the variable)
4) EXECUTE (Execute SQL statements & returns no. of rows processed)
5) FETCH_ROWS (Retrieves a row for the specified cursor)
6) CLOSE_CURSOR (Close the specified cursor)

b) DBMS_DDL.ALTER_COMPILE(‘object_type’, ‘owner’, ‘object_name’) is used to recompile our modified PL/SQL program units

c) DBMS_DDL.ANALYZE_OBJECT(‘object_type’, ‘owner’, ‘name’, ‘method’) is used to analyze a single object. Methods includes Compute, Estimate, or Delete.

d) DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.

Available Subprograms includes,
1) SUBMIT (Submits a job to a job queue)
2) CHANGE (Alters a specified job that has already been submitted to queue)
3) WHAT (Alters a job description for a specified job)
4) NEXT_DATE (Alters next execution time for a specified job)
5) INTERVAL (Alters the interval between executions for a specified job)
6) BROKEN (Disables job execution)
7) RUN (Forces a specified job to run)
8) REMOVE (Removes a specified job from a job queue)

e) DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.
Available procedures includes,
1) Put(Appends text from Procedure to the current line of line output Buffer)
2) New_Line (Places an end_of_line marker in the output Buffer)
3) Put_Line (Combines the action of Put & New_Line)
4) Get_Line(Retrieves the current line from output buffer into the procedure)
5) Get_Lines (Retrieves an array of lines from output buffer into Procedure)
6) Enable/Disable(Enables or Disables calls to the Dbms_Output Package)

f) UTL_FILE: With this package, you can read from & write to Operating system files. It is available with database version 7.3 and later and PL/SQL version 2.3 and later.
Procedures & functions are,
1) FOPEN(A Function that opens a file for input or output & returns a file handle used in subsequent I/O operations)
2) IS_OPEN(A Function that returns a Boolean value whenever a file handle refers to an open file)
3) GET_LINE(A Procedure that reads a line of text from the opened file & Places the text in the output buffer parameter)
4) PUT, PUT_LINE(A Procedure that writes a text string stored in the buffer parameter to the opened file)
5) PUTF (A formatted put procedure with 2 format specifiers:%s for string & %n for new line character)
6) NEW_LINE ( Procedure that terminates a line in an output file)
7) FFLUSH (Procedure that writes all data buffered in memory to a file)
8) FCLOSE (Procedure that closes an opened file)
9) FCLOSE_ALL (Procedure that closes all opened file handlers for session)

g) UTL_HTTP: This package allows to make HTTP Requests directly from the database. By coupling this package with DBMS_JOBS package, you can easily schedule reoccurring requests from database server out to the web. It contains 2 Functions:
1) Request ( It returns upto first 2000 bytes of data retrieved from the URL)
2) Request_Pieces (Returns a PL/SQL table of 2000 byte pieces of data retrieved from URL)
Both the functions take a string URL as a parameter, contact the site & returns HTML data obtained from the site.

h) UTL_TCP: This package enables PL/SQL applications to communicate with external TCP/IP based servers. It is useful to PL/SQL applications that uses Internet Protocols.
1) OPEN_CONNECTION
2) CLOSE_CONNECTION
3) READ_BINARY (Receives binary, text or text line data from open connection)
4) WRITE_BINARY(Transmits binary, text or text line data to open connection)

46) Trigger: It is a PL/SQL Block or a procedure associated with a table, view, schema or the database that executes implicitly whenever a particular event takes place. They are of mainly 2 types:
a) Application Triggers: Fires whenever a particular DML event occurs within an application
b) Database Triggers: Executes when a data event such as DML on a table, an Instead Of trigger on a view, or DDL statements are issued. It also executes when some user actions or database system action occurs, Eg: User logs on or DBA shuts down the database

Trigger body consists of 3 main components:
a) Trigger Timing: Before, After (For Table), Instead Of (For views)
b) Triggering Event: Insert, Update & Delete
c) Trigger type: Statement & Row
Note: 1) Colon is not used in When Condition in front of qualifier old & new
2) New & Old Qualifiers can be Referenced only in Row Level Triggers

47) A Statement Trigger is fired once on behalf of the triggering event, even if no rows are affected at all, whereas Row trigger fires each time the table is affected by the triggering event, if triggering event affects no rows then Row trigger is not executed.

48) Instead Of Trigger: This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contain joins or Group Functions. This triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.

49) Managing Triggers:
a) Alter Trigger trigger_name Disable/Enable
b) Alter Table table_name Disable/Enable All Triggers
c) Alter Trigger trigger_name Compile

50) Triggering User Event:
a) CREATE, ALTER or DROP
Triggering Database or System Event:
a) AFTER SERVERERROR
b) AFTER LOGON
c) BEFORE LOGOFF
d) AFTER STARTUP
e) BEFORE SHUTDOWN

51) Mutating Table: Table under the state of Transition is called Mutating Table. If table is being modified by DML statement and trigger code also queries the same table that is being modified then table goes under Mutation. Mutating Table is not considered for Statement Triggers.
We Cannot Write Commit, Rollback & Savepoint in Triggers, Because Triggers are Implicitly Invoked. Hence Transaction of Triggers are Committed Later.

> Sequence of Firing Database Triggers are,
a) Before Row Level Trigger
b) Before Statement Level Trigger
c) After Row Level Trigger
d) Statement Operation
e) After Statement Level Trigger

52) Dependencies: A stored procedure could contain a Select statement that selects columns from a table. For this reason, the stored procedure is called a dependent object, whereas table is called a referenced object.

53) To Manage Dependencies all schema objects have a status that is recorded in the data dictionary:
a) Valid: Schema object has been compiled & can be used when referenced
b) Invalid: Schema object must be compiled before it can be used

54) It is strongly recommended that you recompile local dependent objects manually, rather than relying on an automatic mechanism.

55) Remote Dependencies: In this case, objects are on separate nodes i.e.Separate Schema
This are governed by the mode chosen by the user,

a) Timestamp: Each PL/SQL Program units carries a timestamp that is set when it is created or compiled. Whenever you alter a PL/SQL program unit or a relevant schema object , all of its dependent program units are marked as invalid & must be recompiled before they can execute

b) Signature: For each PL/SQL program unit, both the timestamp & the signature are recorded. The signature of PL/SQL construct contains following information
1) Name of the construct (Procedure, function or package)
2) Base types of parameters of constructs
3) Modes of the parameters (IN, OUT, INOUT)
4) Number of the parameters
So if a remote program is changed & recompiled but the Signature does not change, then the local procedure can execute the remote procedure. With the timestamp method, an error would have been raised because the timestamps would not have matched.

56) Pragma Autonomous_Transaction is the Compiler Directive that tells the Compiler to Execute Transaction independently in any other Transaction

57) Difference between PL/SQL Table & Nested Table:
PL/SQL Table> Index by Tables are not Stored in Database
Nested Table
> Nested Tables are Stored in Database as Database Columns

58) Difference between Nested Table & Varray:
Nested Tablea) This are Sparse
b) We can Delete its Individual Elements
c) It do not have an Upper Boundary
d) This are Stored in System Generated Table

Varraya) This are Dense
b) We cannot Delete its Elements
c) This are Fixed Size & always need to specify the size
d) This are Stored in Tablespaces