The life of an SQL statement

What happens when Oracle processes an SQL Statement? SQL statement must always be parsed. Then, it can be executed any number of times. If it is a select statement, the result-data needs be fetched for each execution. Parses One of the goals of the parse phase is to generate a query execution plan (QEP). Does the statement correspond to an open cursor, ie, does the statement already exist in the library cache. If yes, the statement needs not be parsed anymore and can directly be executed. If the cursor is not open, it might still be that it is cached in the cursor cache. If yes, the statement needs not be parsed anymore and can directly be executed. If not, the statement has to be verified syntactically and semantically: Syntax This step checks if the syntax of the statement is correct. For example, a statement like select foo frm bar is syntactically not correct (frm instead of from). Semantic A statement might be invalid even if the syntax is correct. For example select foo from bar is invalid if there is no table or view named bar, or if there is such a table, but without a column named foo. Also, the table might exist, but the user trying to execute the query does not have the necessary object privileges. If the statement is syntactically and semantically correct, it is placed into the library cache (which is part of the Shared Pool). Opening the cursor A cursor for the statement is opened. The statement is hashed and compared with the hashed values in the sql area. If it is in the sql area, it is a soft parse, otherwise, it's a hard parse. Only in the case of a hard parse, the statement undergoes the following steps (view merging, statement transformation, optimization): View merging If the query contains views, the query might be rewritten to join the view's base tables instead of the views. Statement Transformation Transforms complex statements into simpler ones through subquery unnesting or in/or transformations Optimization The CBO uses "gathered??" statistics to minimize the cost to execute the query. The result of the optimization is the query evaluation plan (QEP). If bind variable peeking is used, the resulting execution plan might be dependant on the first bound bind-value. Execute Memory for bind variables is allocated and filled with the actual bind-values. The execution plan is executed. Oracle checks if the data it needs for the query are already in the buffer cache. If not, it reads the data off the disk into the buffer cache. The record(s) that are changed are locked. No other session must be able to change the record while they're updated. Also, before and after images describing the changes are written to the redo log buffer and the rollback segments. The original block receives a pointer to the rollback segment. Then, the data is changed. Fetch Data is fetched from database blocks. Rows that don't match the predicate are removed. If needed (for example in an order by statement), the data is sorted. The data is then returned to the application. The execution plan The query execution plan is also stored in the library cache. EXPLAIN PLAN can be used to see how the statement is executed. Private SQL Area Each session that issues an SQL statement has a private SQL area associated with this statement. The first step for Oracle when it executes an SQL statement is to establish a run time area (within the private SQL area) for the statement.

Printing Barcode in Oracle Reports 6i

1. Download Barcode font.
   
   http://www.barcodesinc.com/free-barcode-font/free3of9.zip


2. Install Barcode form on Application server.


3. Open 'UIFONT.ALI' file located in $D2k Home$\TOOLS\COMMON60


4. Add font name in alias file e.g.:"Free 3 of 9" <tab> = "Free 3 of 9", Save
   it.


5. Create a report use aliased barcode name instead of normal font.


6. Run the report generate pdf.

This pdf will contain your barcode.

Some important Queries

n-th salary:

Select distinct sal from emp a where &n = (select count (distinct sal) from emp where a.sal<= sal);

First ‘n’ maximum salaries

Select sal from emp where

Sal >= (select distinct sal from emp a where &n = (select count(distinct sal) from emp

Where a.sal <= sal));

Selecting columns without duplicate

Select sal from emp a where rowid in (select min(rowid) from emp where a.sal = sal);

Deletion of Dulipcate Rows

Delete emp a where rowid not in (select min(rowid) from emp where a.sal = sal);

n-th row selection :

Select * from table_name x where &n = (select count(rowid) from table_name where x.rowid >= rowid;

Adding 10 minites to sysdate:

Select to_char(sysdate+(10/1440),’dd mm yyyy hh:mi’) from dual;

2-nd maximum salary:

Select max(sal) from emp where sal not in (select max(sal) from emp);

+

Count of occurrence of a particular character in a sentence/paragraph:

Select length(translate(‘saravana is a software programmer.’,’abcdefghijklmnopqrstuvwxyz., ‘,’a’)) from dual;

Query for Outer join:

Select emp.deptno,emp.dname,emp.ename from emp,dept where emp.deptno(+) = dept.deptno;

Example for Start with & Connect by Prior:

Select * from emp start with job = ‘PRESIDENT’ –other conditions also –

connect by prior empno = mgr – other conditons also –

PL/SQL Package

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.

Packages usually have two parts, a specification (always required) (spec) and a body (optional)

Specs contain declaration of objects and are public.

Body contain codes and private declaration and are private to package and optional initialization part.

Use of package

• We can declare a single cursor for multi location use with slight difference, easy to maintain.
• Recursive call of procedure and functions.
• Overloaded procedures and functions by declaring same names but different sets (numbers & types) of parameters.
• Declare global variables, available through out session and can manipulate from different subprograms.
• Useful in passing collections between pro-units

Advantages of PL/SQL Packages

• Encapsulate logically related types, items, and subprograms in a named PL/SQL module.
• We can code and compile a spec without its body then code the body after application design, don’t wait for complete package to be written.
• Hiding private data and implementation detail from end user.
• Public variables and cursors sharing by all subprograms that execute in the environment in same session.
• It (full spec’s and body) loaded into memory (into shared memory pool) when a packaged subprogram for the first time, so that no disk I/O required when another subprogram
• When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.
• If we change the body of a packaged function, Oracle does not recompile other subprograms that call the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes. This leads to stop cascading dependencies and avoid unnecessary recompiling.

We cannot refer remote packaged variables (using database link), either directly or indirectly

Inside a package, you cannot reference host variables (e.g.: :emp_id, :sal etc.).

A package can contain initialization part and run only once, the first time you reference the package.

Example:-
Create or replce package pack1 as
Function f1(---------) ;
Prodecure p1(-------);
End pack1;

Create or replce package body pack1 as
Num1 number; -- to be used in initialization private
Function f1(---------) as
Begin
Return 123;
End;

Prodecure p1(-------)
Begin
Null;
end;
begin
insert into audit table;
num:=1;
End pack1;

Here bold code act as an initialization part, and will execute only once after calling any sub program in a session.

A public packaged cursor can be manipulated from any subprogram.

You can pin (Stick) frequently accessed packages in the shared memory pool, using the supplied package DBMS_SHARED_POOL. When a package is pinned, it is not aged out by the least recently used (LRU) algorithm that Oracle normally uses. The package remains in memory no matter how full the pool gets or how frequently you access the package.

You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.

You cannot apply AUTONOMOUS_TRANSACTION Pragma to an entire package. Instead, you can apply the pragma to each packaged subprogram.

Calculating time difference

SQL> SELECT to_number( to_char(to_date('1','J') +
2 (date1 - date2), 'J') - 1) days,
3 to_char(to_date('00:00:00','HH24:MI:SS') +
4 (date1 - date2), 'HH24:MI:SS') time
5 FROM dates;

DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00

Cursors: Key Points

Cursors
A cursor is a name for a specific private SQL area in which information for processing
the specific statement is kept. PL/SQL uses both implicit and explicit cursors. PL/SQL
implicitly declares a cursor for all SQL data manipulation statements on a set of rows,
including queries that return only one row. For queries that return more than one row,
you can explicitly declare a cursor to process the rows individually.
Cursor attribute for implicit cursor
Sql%found : Until a SQL data manipulation statement is executed, %FOUND yields NULL. Thereafter,
%FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or
more rows, or a SELECT INTO statement returned one or more rows.

%ISOPEN : Oracle closes the SQL cursor automatically after executing its associated SQL
statement. As a result, %ISOPEN always yields FALSE.
%ROWCOUNT Attribute: How Many Rows Affected So Far

If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined
exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows
that satisfy the query
The SQL%ROWCOUNT attribute is not related to the state of a transaction. When a
rollback to a savepoint is performed, the value of SQL%ROWCOUNT is not restored to the
old value before the savepoint was taken. Also, when an autonomous transaction is
exited, SQL%ROWCOUNT is not restored to the original value in the parent transaction.
It might be in a different scope

The %NOTFOUND attribute is not useful in combination with the SELECT INTO
statement:
– If a SELECT INTO statement fails to return a row, PL/SQL raises the
predefined exception NO_DATA_FOUND immediately, interrupting the flow of
control before you can check %NOTFOUND.
– A SELECT INTO statement that calls a SQL aggregate function always returns
a value or a null. After such a statement, the %NOTFOUND attribute is always
FALSE, so checking it is unnecessary.

The value of the SQL%ROWCOUNT attribute refers to the most recently executed SQL

Welcome

Imran welcomes all of you.