PLSQL

Nov 182015
 

Basic Tuning for SQL Query   Ordering of the FROM clause.  When we are fetching data from more than one table, write the table with fewer rows at the end of the FROM clause. Therefore, the table returning the least number of rows should be last in the FROM list. Example: SELECT COL1 FROM TAB1,

[Read More]
Dec 012014
 

Performance Tuning on SQL and PL/SQL Performance Tuning can be performed by following few guidelines: 1. Database must be normalized which will help to decrease the storage requirement and faster search performance. Normalization Normalization is the process of efficiently organizing data in a database. It deals with elimination of redundant i.e. duplicate data and ensures

[Read More]
Oct 202014
 

Example 1: For Sending Email to single recipient from Oracle database follow following steps: Step 1: Compile below “Send_Mail” Procedure. CREATE OR REPLACE PROCEDURE send_mail (p_to  IN VARCHAR2, p_from     IN VARCHAR2, p_subject   IN VARCHAR2, p_html_msg IN VARCHAR2 DEFAULT NULL,  p_smtp_host IN VARCHAR2, p_smtp_port IN NUMBER DEFAULT 25) AS l_mail_conn   UTL_SMTP.connection; l_boundary   VARCHAR2(50) := ‘—-=*#abc1234321cba#*=’; BEGIN

[Read More]
Oct 062014
 

What is a PL/SQL? PL/SQL is a procedural extension to SQL with design features of programming language such as Data encapsulation, Information hiding, Exception handling etc. Oracle Server and oracle tools (oracle developer) have their own PL/SQL engine. PL/SQL Engine processes the entire PL/SQL block and filters out the SQL and procedural Statements separately, this reduces

[Read More]
Sep 302014
 

PL/SQL Packages  Package group logically related PL/SQL types, items and subprograms. Package cannot be invoked, parameterized or nested. Package allows the oracle server to load multiple objects into memory at once, thus later calls to constructs in the same package requires no disk I/O. Package consists of two parts Package Specification: Contains Public Constructs. Accessible

[Read More]
Sep 242014
 

PLSQL block consists of two different types: Anonymous block and Subprograms. Anonymous Blocks are unnamed block that does not accept parameters whereas Subprograms are named PL/SQL block that can accept parameters and can be invoked. Subprograms further are of two types: Procedures and Functions. The main difference between Procedure and Function is: Procedure Function Is

[Read More]
Sep 152014
 

Trigger is a PL/SQL block or a PL/SQL Procedure associated with a table, view, schema or the database. Trigger is executed implicitly whenever a particular event takes place. Design Triggers to perform related actions and to centralise global operations. Database triggers fire for every user each time the event occurs for which the Trigger is

[Read More]
Sep 042014
 

Top 51 Questions for PL/SQL that are frequently asked in Interview. 1) Describe in brief about PL/SQL? 2) What are the benefits of using PL/SQL? 3) What are the sections described in PL/SQL block? 4) Describe the types of PL/SQL block? 5) What is a PL/SQL variable? 6) Describe %Type in PL/SQL? 7) What are different types of Modes used in PL/SQL

[Read More]
Aug 112014
 

  Zero is a number and a Space is a character. Character strings and date values are enclosed in single quotation marks. Character values are case sensitive and date values are format sensitive.  The default date format is DD-MON-RR. Oracle database stores date in a format: Century, year, month, day, hours, minutes and seconds.  An

[Read More]
Aug 062014
 
Best 51 Oracle PLSQL Questions and Answers

Question 1: Describe in brief about PL/SQL? PL/SQL is a procedural extension to SQL with design features of programming language such as Data encapsulation, Information hiding, Exception handling etc. Oracle Server and oracle tools (oracle developer) have their own PL/SQL engine. PL/SQL Engine processes the entire PL/SQL block and filters out the SQL and procedural

[Read More]