Abhyas

Practise makes perfect

Oracle Articles Oracle Interview Questions

Oracle DECODE function

Oracle DECODE function

Oracle decode function allow to transform data values at retrieval time.

Syntax :

DECODE(expr, search, result
             [, search, result ]...
       [, default ]
      )

 Example :

 We have a column named Floor, with values of GF, FF, SF and TF. When we run SQL queries, we want to transform these values into Ground Floor, First Floor, Second Floor and Third Floor.

select
decode (
floor,
‘GF’,’Ground Floor’,
‘FF’,’First Floor’,
‘SF’,’Second Floor’,
‘TF’,’Third Floor’,
‘NOT KNOWN’
)
from
apartment;

Oracle 9i Tutorial Oracle Articles

Oracle PL/SQL Quick Help : Developers

A PL/SQL Block consists of three sections:
1.The Declaration section (optional).
2.The Execution section (mandatory).
3.The Exception (or Error) Handling section (optional).
Sample below :
      DECLARE
     Variable declaration
BEGIN
     Program Execution
EXCEPTION
     Exception handling
END;
PL/SQL Variables
The General Syntax to declare a variable is:
variable_name datatype [NOT NULL := value ];
variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable.
value or DEFAULT value is also an optional specification, where you can initialize a variable.
Each variable declaration is a separate statement and must be terminated by a semicolon.
Example :
DECLARE Empcode number(4);
EmpName varchar2(10) NOT NULL := “Abhi”;

assign values to variables
•       variable_name:= value;
•       SELECT column_nameINTO variable_name FROM table_name [WHERE condition];

Scope of Variables

•       Local variables – These are declared in a inner block and cannot be referenced by outside Blocks.
•       Global variables – These are declared in a outer block and can be referenced by its itself and by its inner blocks.
•       Example :
DECLARE
 var_num number;  // Global variables
BEGIN
var_num := 100;
DECLARE
var_sqr number;  // Local variables
BEGIN
var_sqr := var_num * var_num2
END;
END;

PL/SQL Constants

The General Syntax to declare a constant is: constant_name CONSTANT datatype := VALUE;

Example :
DECLARE empCode CONSTANT number (3) := 10;

Conditional Statements in PL/SQL
IF condition 1 THEN  
statement 1;  
statement 2;
ELSIF condtion2 THEN  
statement 3;
 ELSE  
statement 4;
END IF
Parameters in Procedure and Functions
1)      IN type parameter: These types of parameters are used to send values to stored procedures.
CREATE [OR REPLACE] PROCEDURE procedure_name ( param_name1 IN datatype, param_name12 IN datatype … )
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.
CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)

Types of Exception

a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

Named System Exceptions
•       CURSOR_ALREADY_OPEN
When you open a cursor that is already open.
ORA-06511
•       INVALID_CURSOR
When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.
ORA-01001
•       NO_DATA_FOUND
When a SELECT…INTO clause does not return any row from a table.
ORA-01403
•       TOO_MANY_ROWS
When you SELECT or fetch more than one row into a record or variable.
ORA-01422
•       ZERO_DIVIDE
When you attempt to divide a number by zero.
ORA-01476
Example :
BEGIN
Execution section
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line (‘No record found.’);
END;
User-defined Exceptions
DECLARE 
userDefinedExp EXCEPTION;  
 BEGIN    
RAISE userDefinedExp;     
EXCEPTION    WHEN userDefinedExp THEN         
raise_application_error(-2100, ‘The User Defined Exception.’);
END;
RAISE_APPLICATION_ERROR
•       RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.
•       RAISE_APPLICATION_ERROR (error_number, error_message);
• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.
Unnamed System Exceptions
DECLARE  
rec_exception EXCEPTION;   
PRAGMA   
EXCEPTION_INIT (rec_exception, -2296);
BEGIN  
EXCEPTION   
WHEN rec_exception   THEN
Dbms_output.put_line(‘Unnamed System Exceptions.’);
END;

You Missed

Online Quiz Quiz

Country & Capital Quiz

IEO Olympiad Preparation IEO Olympiad Preparation - Class 1 IEO Olympiad Preparation - Class 2 IEO Olympiad Preparation - Class 3 IEO Olympiad Preparation - Class 4 IEO Olympiad Preparation - Class 5 Olympiad English Class 2 SOF Olympiad SOF Olympiad - IEO SOF Olympiad Preparation

IEO Olympiad Preparation Class 1 to 5 – Singular and Plural

Olympiad IGKO Class 1 Olympiad IGKO Class 2 Olympiad IGKO Class 3 Olympiad IGKO Class 4 Olympiad IGKO Class 5 Olympiad IGKO Preparation Class 1 - 10 Olympiad Preparation - Class 1 - 10

IGKP Preparation Class 1 to Class 5 – Statue & Location Quiz

Olympiad IGKO Class 1 Olympiad IGKO Class 2 Olympiad IGKO Class 3 Olympiad IGKO Class 4 Olympiad Preparation - Class 1 - 10

IGKO Preparation Class 1 to 5 – Location