1. if you do not intialize a value to a local variable then by default a null value will be intialized 2. Not null variable must be intialized only at the place of declaration 3. null value compared with any existing value within an if condition by default causes the control of the program to execute the statement within the else part. 4. % type is an attribute which when used is enabling the programmar to automatically visualize the changes in a variable whenever a change is made to the column of a table provided the variable had been declared with respect to that column usage of this attribute : <variable name> <table name> . <col name> %type; 5. Into operator : This operator is used to copy the data from a column into the variable provided the variable is used for display or calculation usage : select <col name> into <output variable name> from <table name> where <col name> = <input variable name>; 6. %rowtype is an attribute that can be used to declare 1 variable which can hold the data from multiple columns in the same table usage : <variable name> <table name> %rowtype; 7. ANY ERROR which had been reported by the data base which is of the form ORA- <Number>, then that type of error is referred to as a run time error. 8. ANY ERROR which had been reported by the data base which is of the form SP OR PLS - <Number>, then that type of error is referred to as a COMPILE time error. 9. Raise_application_error() --> This statement has to be used to print a message as a error message. this statment will accept 2 parameters p1 --> error no (range : -20001 to +20990). -20001 is called as a standard error no which is compatbile on every version of oracle starting from oracle 7.x p2 --> message 10. in a function, the identifier to be used for a argument and a return variable cannot be the same 11. You can call a function in oracle without passing any parameters is by defining those parameters as default pars using the default key word. 12. Default pars must be positioned at the last in a function prototype 13. Whenever a function had been defined for performing any updation operation within a table then that function has to be called from a block and not by using a select command differences between procedures and functions 1. function returns utmost only 1 value 2. procedure can logically return more than 1 value 1. procedure cannot return any value 2. function returns a value 1. Function cannot be defined with 0 pars but can be called with 0 pars 2. procedure can be defined and called with 0 pars 1. cursors can be defined in a procedure because a procedure can return more than 1 value 2. cursors cannot be defined in a function because function utmost returns only 1 value 1. function can be called from a procedure 2. procedure cannot be called from a function because a procedure cannot return a value. but you can call one procedure from another procedure 1. functions cannot used to raise an exception 2. procedures can be used to raise and handle exceptions 1. Functions can accept only IN parameters 2. Procedure can accept IN, OUT and IN OUT parameters syntax for defining a procedure create [or replace] procedure <procedure name>(args) is [<local variable declarations>]; begin statements; end [<procedure name>]; calling a procedure: a. exec <procedure name>(args) out parameters: These type of parameters have to be defined in a procedure for the following reasons: 1. If you expect a procedure to return a value, the return value can be stored in a OUT par 2. If you want the value of a procedure to be stored in a session variable steps of usinf OUT pars: 1. Out pars should be declared as session variables : sql > var <variable name> <data type> 2. The session variables have to be used as bind variables in the procedure call sql > exec <procedure name>(: <session variable name>) (note no space between : and variable name) if the parameter to be passed to a procedure needs to be modified then that parameter has to be declared as IN OUT par. In out par has to be declared as a session variable and using the block u can assign a value to that parameter like: begin : <session var name > := <value>; end; call the procedure using in out pars exec <proc name>(:<session var name>) packages : It is a collection of procedures, functions, cursors and variables why to create a package? 1. procedure cannot be compiled from a different user privilage wheras a package can be compiled from a different user privilage hence the procedure needs to be defined within a package how to create a package? in order to create a package, first of all we have to create a package specification package specification contains the prototypes of all the procedures, functions and also some local variable declarations. sytntax: create or replace package <package name> is function <function name>(args) return <data type>; procedure <procedure name>(args); end; question : can you define a procedure in the package body which is not declared in the package specification? procedure cannot be defined in the package body without declaring it's prototype in the package specificication because the procedure call must always match with the procedure prototype at run time procedure declared in a package specification and not defined within the package body also amounts to a compile time error. question : can you declare a variable in a package spec and intialize that var in the package body?