Exclude File Extension

SELECT
SUBSTR('&FileName', 1, LENGTH('&FileName') - INSTR(REVERSE('&FileName'),'.', 1, 1))
FROM DUAL

RAISE_APPLICATION_ERROR

The RAISE_APPLICATION_ERROR is actually a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are define between -20000 to -20099. The message can be anything that fit with varchar2(2000).

Top N Salaries

SQL> Select
rownum as Rank,ename,sal
from (select ename,sal from emp order by sal desc)
where rownum <=3

Find Duplicate Rows

SQL> SELECT * FROM EMP WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM EMP GROUP BY SAL);

User Acoount Lock/Unlock

SQL> ALTER USER username ACCOUNT LOCK;
SQL> ALTER USER username ACCOUNT UNLOCK;
To change the password for a user:
SQL> alter user username identified by new_password;
All at once:

-- Expire, forces to change the password at first login
SQL> alter user username identified by password account lock password expire;

Difference between Post database commit and Post-form commit

Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in, issuing the command. The Post-database commit trigger fires after oracle forms issues the commit to finalized transactions.

Difference between Pre-Select and Pre-Query

Pre-Select fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued.

Pre-Query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode.

Pre-Query tigger fires before Pre-Select trigger.

Triggers created for Master Detaisl Relationship

* NON-ISOLATED (default)

on check delete master

on clear details

on populate details

* ISOLATED

on clear details

on populate details

* CASCADE

pre delete

on clear details

on populate details

Various Master Details Relationship

Non Isolated : The Master cannot be deleted when a child is existing

Isolated : The Master can be deleted when the child is existing

Cascading : The child gets deleted when the Master is deleted

Difference between Post Query and Pre Query.

A post query will fire for every row that is fetched but the pre query will fire only once

Difference between View and a Snapshot

A View is a stored select statement, so the data is obtained from the base table on the fly, every time the view is queried.

A Snapshot is a physical copy of data from one or more tables. This data can be refreshed on demand or at specified intervals to get the changes made on the base tables.

Difference between foreign key and reference key

Foreign key is the key i.e. attribute which refers to another table primary key.
Reference key is the primary key of table referred by another table.

DB Link

A data base link is a schema objects in one database that enables to access objects on another database. The other database need not be an oracle database system.

Subquery Factoring

Subquery factoring, also known as the WITH clause, provides a convenient and flexible way to define subquries and in-line views. The primary purpose of subquery factoring is to reduce repeated table accesses by generating temporary data sets during subquery execution.

Extract Function

Extract Function extracts a value from a date or interval value.

Syntax:

EXTRACT (

{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

|{ TIMEZONE_HOUR | TIMEZONE_MINUTE }

|{ TIMEZONE_REGION |TIMEZONE_ABBR }

FROM { date_value |interval_value } )

Can only Exctract YEAR, MONTH and DAY from date.

Eg:

extract(YEAR FROM DATE '2003-08-22')      would return 2003
extract(MONTH FROM DATE '2003-08-22')  would return 8
extract(DAY FROM DATE '2003-08-22')       would return 22

Function Purity Level

The function purity level defines what structures the function reads or modifies.
Purity levels

• WNDS - Writes No Database State i.e. Function does not modify any database tables (No DML)
• RNDS - Reads No Database State i.e. Function does not read any tables (No select)
• WNPS - Writes No Package State i.e. Function does not modify any packaged variables (packaged variables are variables declared in a package specification)
• RNPS - Reads No Package State i.e. Function does not read any packaged variables

Syntax:

PRAGMA RESTRICT_REFERENCES (function_name, WNDS [, WNPS] [, RNDS] [, RNPS])

Tells the compiler the purity level you believe your function meets or exceeds

Forward declaration in packages

PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body, terminated by semicolon. Can use procedure/function in the body, before its defined.

Eg:
create or repalce package body pkg
is
procedure a(...);
procedure b(...);
begin
a(...);
end;
procedure a(...)
begin
null;
end;
end pkg;

Synonym

A Synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. Can be either private or public. Public synonym is valid for each schema. Private synonym is valid for the owner alone. Synonyms cannot be used in a drop table, drop view or truncate table/cluster statements.

Syntax:

create [or replace] [public] synonym [schema .] synonym_name

for [schema .] object_name [@dblink];

Used to shorten the specification of long or complex object names.Synonyms are used to Mask the real name and owner of the object.

Provide public access to an object.

Provide location transparency for tables, views or procedures of a remote database.

Simplify the SQL statements for the user.

Difference between & and &&

& - is used to create a temporary substitution variable that will prompt you for a value every time it is reffered.

Eg: SQL> SELECT sal FROM emp WHERE ename LIKE '&NAME';

Enter value for name: SCOTT
old 1: SELECT sal FROM emp WHERE ename LIKE '&NAME'
new 1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

SAL
----------
3000

SQL> /


Enter value for name: SCOTT
old 1: SELECT sal FROM emp WHERE ename LIKE '&NAME'
new 1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

SAL
----------
3000

&& - is used to create a permanent substitution variable. Once you have entered a value its value will used every time the variable is referenced.

Eg: SQL> SELECT sal FROM emp WHERE ename LIKE '&&NAME';

Enter value for name: SCOTT
old 1: SELECT sal FROM emp WHERE ename LIKE '&&NAME'
new 1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

SAL
----------
3000

SQL> /

old 1: SELECT sal FROM emp WHERE ename LIKE '&&NAME'
new 1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

SAL
----------
3000

COALESCE Function

The COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.

Syntax:

coalesce(expr_1, expr_2,...expr_n)

SYS_CONTEXT

Sys_Context funtion used to retrieve information about the oracle environment.

Syntax:
sys_context(namespace, parameter, [length])

• NAMESPACE is an oracle namespace that has alrady been created.
• PARAMETER is valid attribute that has been set using the DBMS_SESSION.set_context procedure.
• length is optional. It is the length of the return value in bytes.

For more and example Click Here

To_Char on Numbers

To Print Salary in words

select nameto_char(to_date(salary,'J'),'Jsp') from join;

Cascading Triggers

The trigger actions of one trigger can activate another trigger. Oracle transactions are atomic. No commit or rollback are allowed in triggers. Can call functions and procedures in triggers.

All Friday's in a Current year

SELECT req_dates, TO_CHAR (req_dates, 'DY')

FROM (SELECT TRUNC (SYSDATE, 'YY')+ ( ROWNUM- 1) req_dates

FROM (SELECT 1 FROM DUAL GROUP BY CUBE (1, 2, 3, 4, 5, 6, 7, 8, 9))

WHERE ROWNUM <= 365)

WHERE TO_CHAR (req_dates, 'DY') IN ('FRI');

Subquery

A subquery is query within a query. These subqueries ca reside in the WHERE clause, the FROM clause, or the SELECT clause. A subquery is a query that is nested inside a SELECT, UPDATE or DELETE statement, or inside another subquery. A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select. A subquery can also be found in the FROM clause. These are called inline views. Oracle allows up to 255 level of subqueries in the WHERE clause. can be aliased. Oracle allows an unlimited number of subqueries in the FROM clause. Scalar subqueries - allows us to place individual queries inside the SELECT clause.

• The Subquery (inner query) executes once before the main query
• The result of the subquery is used by the main query (outer query)
• Place subquery on the right side of the comparison operator.
• Do not add an order by clause to a subquery
• use single row operators with single row subqueries
• use multiple row operators with multiple row subqueries

Single row operators :- =, >, >= ...etc

Multiple row operators :-

1. IN - Equal to any number in the list
2. ANY - Compare value to each value returned by the subquery
3. ALL - Compare value to every value returned by the subquery

Count()

The COUNT column function returns the total number of non-NULL values in the column or expression. The ALL keyword can precede the specified column name for clarity, bu the query result is the same whether you include the ALL keyword or omit it.

SELECT COUNT(ALL COL_NAME) FROM TABLE_NAME;

Count(1) to get better performance as the database engine will not have to fetch back the data fields. Count(*) need to retrieve all fields from the table. Count(1) retrieve the numeric value of 1 for each record that meets your criteria.

Having Clause

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns. To apply one or more qualifying conditions to group s after they are formed. The effect of the HAVING clause on groups is similar to the way the WHERE clause qualifies individual rows. One advantage of using a HAVING clause is that you can include aggregates in the search condition, whereas you cannot include aggregates in the search condition of WHERE clause.

Group by clause

The Group by clause can be used in select statement to collect data across multiple records and group the results by one or more columns. This clause is most often combined with aggregate functions that produce summary values.

NOCOPY

The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value.When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables, which are then used by the subprogram being called. On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the original parameter values being left unchanged. The process of copying large parameters, such as records, collections, and objects requires both time and memory which affects performance.With the NOCOPY hint the parameters are passed by reference and on successful completion the outcome is the same, but unhandled exceptions may leave the parameters in an altered state, so programs must handle errors or cope with the suspect values.The nocopy.sql script compares the performance of both methods by passing a populated collection as a parameter.

nocopy.sql

SET SERVEROUTPUT ON

DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(32767);
l_tab t_tab := t_tab();
l_start NUMBER;
PROCEDURE in_out (p_tab IN OUT t_tab) IS
BEGIN
NULL;
END;

PROCEDURE in_out_nocopy (p_tab IN OUT NOCOPY t_tab) IS
BEGIN
NULL;
END;

BEGIN
l_tab.extend;
l_tab(1) := '1234567890123456789012345678901234567890';
l_tab.extend(999999, 1); -- Copy element 1 into 2..1000000
-- Time normal IN OUT
l_start := DBMS_UTILITY.get_time;
in_out(l_tab);
DBMS_OUTPUT.put_line('IN OUT : ' (DBMS_UTILITY.get_time - l_start));
-- Time IN OUT NOCOPY
l_start := DBMS_UTILITY.get_time;
in_out_nocopy(l_tab); -- pass IN OUT NOCOPY parameter
DBMS_OUTPUT.put_line('IN OUT NOCOPY: ' (DBMS_UTILITY.get_time - l_start));
END;
/

The output of the script clearly demonstrates the performance improvements possible when using the NOCOPY hint.

SQL> @nocopy.sql
IN OUT : 122
IN OUT NOCOPY: 0
PL/SQL procedure successfully completed.

PRAGMA

Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

1) Autonomous Transaction
Autonomous Transactions is the child transaction, which are Independent of Parent transactions. In Our Example, p1 is child transaction, which is used in the Parent transaction.

Example: -
CREATE or REPLACE Procedure p1 IS
Pragma Autonomous_transaction;
BEGIN
INSERT INTO TEST_T VALUES (1111,’BERNA1’);
COMMIT;
END;


In the Declaration section, you will declare this Transaction as the Autonomous Transaction.

DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’PRABHU’);
P1;
ROLLBACK;
END;

NOW Table has (1111,’BERNA’) Record. COMMIT in the PROCEDURE P1 have not commit the Outside (p1) DML operations. It will just commit p1 transactions.
The ROLLBACK will not rollback BERNA record, it will just rollback the PRABHU record.

CREATE or REPLACE Procedure p1 IS
BEGIN
INSERT INTO TEST_T VALUES (1111,’BERNA1’);
COMMIT;
END;

If I remove the Pragma Autonomous_transaction From the declaration section, then this transaction will become the normal transaction. Now if you try to use the same parent transaction as given below.

>> delete from TEST_T;

DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’PRABHU’);
P1; -- This transaction has ended with the COMMIT;
ROLLBACK;
END;

After executing the above transaction, you can see BOTH records got Inserted (BERNA and PRABHU records). Here COMMIT in P1 will commit both transactions (BERNA and PRABHU Records Insert) And then Rollback. Since, there are no transactions happening between COMMIT and ROLLBACK. Our ROLLBACK is not doing any ROLLBACK.

Note: - IF COMMIT is not given in P1 then, the ROLLBACK will do the ROLLBACK both the INSERT transaction (BERNA Record which is in p1 procedure and PRABHU Record).

2) Pragma Restrict_references
It gives the Purity Level of the Function in the package.

CREATE OR REPLACE PACKAGE PKG12 AS
FUNCTION F1 RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (F1, WNDS,RNDS,WNPS,RNPS);
END PKG12;

CREATE OR REPLACE PACKAGE BODY PKG12 AS
FUNCTION F1 RETURN NUMBER IS
X NUMBER;
BEGIN
SELECT EMPNO INTO X FROM SCOTT.EMPWHERE ENAME LIKE ‘SCOTT’;
DBMS_OUTPUT.PUT_LINE (X);
RETURN (X);
END F1;
END PKG12;

You will get the Violate It’s Associated Pragma Error. This in Purity Level, we said
It cannot read from the database. RNDS (In Our Function F1, we have SELECT STATEMENT which is reading the data from the database).

3) Pragma SERIALLY_REUSABLE
We may use this feature for improving the performance or to meet certain requirements.
This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.
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.
The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.

WITH PRAGMA SERIALLY_REUSABLE
The following example creates a serially reusable package:

CREATE PACKAGE pkg1 IS
PRAGMA SERIALLY_REUSABLE;
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
/

CREATE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;

PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put_line('Num: ' pkg1.num);
END;

END pkg1;
/

begin
pkg1.init_pkg_state(10);
pkg1.PRINT_PKG_STATE;
end;

Num: 10

begin
pkg1.PRINT_PKG_STATE;
end;

Num: 0

Note: - The first block is changing the value of the variable (num) to 10 and if I check the value in same block then it is showing the changed value that is 10. But, if I try to check the value of the (num) variable then it should the default value given to it (i.e.) “0”
WITHOUT PRAGMA SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE pkg1 IS
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;

CREATE PACKAGE BODY pkg1 IS
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;

PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put_line('Num: ' pkg1.num);
END;

END pkg1;

begin
pkg1.init_pkg_state(10);
pkg1.PRINT_PKG_STATE;
end;

>>Num: 10

begin
pkg1.PRINT_PKG_STATE;
end;


>>Num: 10


Note: - Now, you may noticed the difference. The second block is giving us the changed value.

DROP PACKAGE pkg1;

(There are many other pragma's like Pragma Exception_init etc. I have not convered these concepts in this article. I will cover them in Exception concept article).

To get the digits from a string

CREATE OR REPLACE FUNCTION get_digits (i_str IN varchar2)
RETURN varchar2
IS
temp_str varchar2 (100);
retval varchar2 (100) := '';
temp_chr varchar2 (1);
BEGIN
temp_str := i_str;
WHILE LENGTH (temp_str) > 0
LOOP
temp_chr := SUBSTR (temp_str, 1, 1);
IF ASCII (temp_chr) BETWEEN 48 AND 57
THEN
retval := retval temp_chr;
END IF;
temp_str := SUBSTR (temp_str, 2);
END LOOP;
RETURN retval;
END;

To test a string for numeric characters

SELECT LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) FROM DUAL;

OUTPUT

----------

LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' ')));	would return 1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' ')));	would return 2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' ')));	would return null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')));	would return null

Truncate Vs Delete

Importing Dump

C:\Documents and Settings\Welcome>SQLPLUS /NOLOG
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 7 17:42:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> CONN SYS AS SYSDBA

Enter password:Connected.

SQL> SHOW USER;

USER is "SYS"

SQL> CREATE TABLESPACE TBS01 DATAFILE 'E:\oracle\DBF\TBS01.DBF' SIZE 10M AUTOEXTEND ON NEXT 10M;
Tablespace created.
SQL> CREATE USER DICGC IDENTIFIED BY DICGC DEFAULT TABLESPACE TBS01 TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON TBS01;
User created.
SQL> ALTER USER DICGC QUOTA 0 ON SYSTEM;
User altered.
SQL> GRANT CREATE SESSION, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DBA TO DICGC;
Grant succeeded.
D:\DICGC\Mon>IMP DICGC/DICGC@ORCL FILE=MON.DMP LOG=EXPMON.LOG IGNORE=Y COMMIT=Y FULL=Y
Import: Release 10.2.0.1.0 - Production on Tue Oct 7 17:52:38 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ORION, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set. importing ORION's objects into DICGC. . importing table "AUTO_TEMP" 0 rows imported. . importing table "DI01_DICGC" 4507 rows imported

Ref Cursor

A ref cursor variable is not a cursor, but a variable that points to a cursor. A ref cursor allows you to store the results of a 'bulk collect' from a table into PL/SQL table and then reference the array with the ref cursor as a pointer.

Cursors

Cursors:
======
Cursor is a pointer which points to the memory location where the processed information is stored.PL/SQL uses cursor to retrieve the processed information stored in the memory for manipulation - one row at a time.

Types of Cursors:
============
Simple Cursors
==========

Implicit Cursors
==========

Implicit Cursors are those which implicitly opens,fetches and closes.All DML statements and SELECT statements are implicit cursors. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. its transparent to your application code - no cursor declaration is required. SQL statements with implicit cursors are used for COMMIT, ROLLBACK, INSERT, UPDATE, DELETE and SELECT (single row and multi-row) queries.

Implicit Cursor Attributes
================
sql%found
sql%notfound
sql%isopen

Explicit Cursors
==========
Explicit cursors are those which has to be explicitly opened, fetched and closed.If we use cursor for loops then it implicitly opens,fetches and closes. Requires a PL/SQL Program cursor declaration. Used for multi-row SELECT queries.

Explicit Cursor Attributes
================
%isopen
%isfound
%isnotfound

Ref Cursors
========
Ref cursors are those, in which, select statements are dynamically associated at run time.

Week Cursors
=========
Syntax:
DECLARE
TYPE C1 AS REF CURSOR
TYPE1 C1;
BEGIN
IF A THEN
OPEN TYPE1 FOR SELECT * FROM EMPLOYEE;
IF B THEN
OPEN TYPE1 FOR SELECT * FROM DEPARTMENT;
END;

Strong Cursors
==========
Syntax:
DECLARE
TYPE C1 AS REF CURSOR RETURN EMPLOYEE%ROWTYPE --strong Cursors
TYPE1 C1;
BEGIN
IF A THEN
OPEN TYPE1 FOR SELECT * FROM EMPLOYEE;
IF B THEN
OPEN TYPE1 FOR SELECT * FROM DEPARTMENT;
END;

Parametrised Cursors
==============
In parametrised cursors we can define parameters to the cursor at design time. And while opening the cursor we have to pass the cursors parameters.

Note:

===

There are no explicit cursors for INSERT, UPDATE and DELETE statements. however there is always an implicit cursor for any INSERT, UPDATE or DELETE.

-------------------------------------------------------------------------------------------------