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.

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

Cache and NoCache

• The cache option specifies how many sequence values will be stored in memory for faster access.

• Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.

Nowait

NoWait specifies that the database should not wait for a lock to be released.

ORA-01000 - Maximun open cursors exceeded

If the error fired, have to increase MAXOPENCURSOR parameter in init.oraor have to alter the system parameter

ALTER SYSTEM SET OPEN_CURSORS = 1000 SCOPE=BOTH

It sets an array up to 1000, but not allocate 1000 cursors.

init.ora

The init.ora file stores the initialization parameters of oracle. The values that are currently in effect can be viwed through v$parameter.The default location of init.ora is %ORACLE_HOME%\database on windows.The location can be changed by changing ORA_%ORACLE_SID%_PFILE. The default name for the file is init%ORACLE_SID%.ora.However, it is possible to start the database with another init.ora file than the default one. In this case, there is noway to determine which init.ora was used when the database is running.

Query Collections

SELECT REPLACE(TRANSLATE('bernandas_S','@#^&$%*','.'),'.',NULL) FROM table_name;

outp

bernandas S

Sequence of trigger when form is opened & closed

Form Load

1. Pre-Logon From
2. On-Logon Form
3. Post-Logon Form
4. Pre-Form Form
5. When-Create-Record Block
6. Pre-Block Block
7. Pre-Record Block
8. Pre-Text-Item Item
9. When-New-Form-Instance Form
10. When-New-Block-Instance Block
11. When-New-Record-Instance Block
12. When-New-Iten-Instance Item

--------------------------------------------
Form Exit

1. Post-Text-Item Item
2. Post-Record Block
3. Post-Block Block
4. Post-Form Form
5. On-Rollback Form
6. Pre-Logout Form
7. On-Logout Form
8. Post-Logout Form

The TRUNC() function

ON NUMBERS
This function allows to make two types of action on a number:
- Keep the number of wished decimals (precision > = 0)
- Centre the whole part of a number in a range (precision <>
------------------------------------
select
trunc(12.98764, 5) "+5 dec."
trunc(12.98764, 4) "+4 dec."
trunc(12.98764, 3) "+3 dec."
trunc(12.98764, 2) "+2 dec."
trunc(12.98764, 1) "+1 dec."
trunc(12.98764, 0) "+0 dec."
from dual ;

+5 dec. +4 dec. +3 dec. +2 dec. +1 dec. +0 dec.
---------- ---------- ---------- ---------- ---------- ----------
12,98764 12,9876 12,987 12,98 12,9 12
---------------------------------------------------------------

select
trunc(1234567,-6) "million"
trunc(1234567,-5) "cent-mille"
trunc(1234567,-4) "dix-mille"
trunc(1234567,-3) "mille"
trunc(1234567,-2) "cent"
trunc(1234567,-1) "dix" from dual ;

million cent-mille dix-mille mille cent dix
---------- ---------- ---------- ---------- ---------- ----------
1000000 1200000 1230000 1234000 1234500 1234560
--------------------------------------------------------------------


ON DATE
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;
SQL> SELECT SYSDATE "Current date" FROM DUAL ;

Curent date
-------------------
08/10/2004 14:08:48

-- date truncated to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'YEAR') "'YEAR'" FROM DUAL;

'YEAR'
-------------------
01/01/2004 00:00:00

-- date truncated to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IYYY') "'IYYY'" FROM DUAL;

'IYYY'
-------------------
29/12/2003 00:00:00

-- date truncated to the 1st day of the quarter --
SQL> SELECT TRUNC(SYSDATE, 'Q') "'Q'" FROM DUAL;

'Q'
-------------------
01/10/2004 00:00:00

-- date truncated to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'MONTH') "'MONTH'" FROM DUAL;

'MONTH'
-------------------
01/10/2004 00:00:00

-- date truncated to the 1st day of the week --
SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;

'DAY'
-------------------
04/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'W') "'W'" FROM DUAL;

'W'
-------------------
08/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'WW') "'WW'" FROM DUAL;

'WW'
-------------------
07/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IW') "'IW'" FROM DUAL;

'IW'
-------------------
04/10/2004 00:00:00

-- date truncated to the day (suppress hours) --
SQL> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL;

'DD'
-------------------
08/10/2004 00:00:00

-- date truncated to the hour (suppress minutes) --
SQL> SELECT TRUNC(SYSDATE, 'HH') "'HH'" FROM DUAL;

'HH'
-------------------
08/10/2004 14:00:00

-- date truncated to the minute (suppress seconds) --
SQL> SELECT TRUNC(SYSDATE, 'MI') "'MI'" FROM DUAL;

'MI'
-------------------
08/10/2004 14:08:00

VARRAYS

• Enable storage of multiple values for a single column per row
• Number of multiple values are fixed by definition
• Limited to one column per table
• Array values are stored in the table itself (in line storage)
• Query performance likely to suffer
• Indexing a varray column is not possible
• Selection of array values is complex
• DML operations require use of constructor methods
• NULL values need to be specified explicitly

ROWID

SELECT department_id, rowid FROM hr.departments;
DEPARTMENT_ID ROWID
————- ——————
10 AAABQMAAFAAAAA6AAA
20 AAABQMAAFAAAAA6AAB
30 AAABQMAAFAAAAA6AAC
40 AAABQMAAFAAAAA6AAD

In this example:
• AAABQM is the data object number
• AAF is the relative file number
• AAAAA6 is the block number
• AAA is the row number for the department with ID = 10

• every row in a non clustered table of Oracle database is assigned a unique ROWID
• corresponds to the physical address of the row
• every table contains a pseudo column called ROWID
• fastest way to access a row
• Import, Export operations affect the values of ROWID
• the ROWID of a deleted row may be reassigned by Oracle
• cannot be modified
• can be queried
• may not be supported in non-Oracle databases
• can be used to understand a table's organization
• use of locks while using ROWID is recommended
• ORACLE8 uses extended rowid format
• Object Ids can refer to various row objects
• ROWID changes especially with index organized or partitioned tables. Because ROWIDs represent the physical location of a record/row, the ROWID will chang every time the record is physically moved
• The ROWID is intended to be immutable (that is, unchangeable)
• The ROWID is a valid data type of a column and is available in both SQL and Pl/SQL. It stores the ROWID pseudo column value of a row in the database.
• It's important to note that ROWID values are not necessarily unique within a database. It is entirely possible for two rows of two different tables stored in the same cluster to have the same ROWID
• ROWID may change if the row is physically moved on disk, such as:

                    Doing an export or import of the table
                    Doing ALTER TABLE XXXX MOVE
                    Doing ALTER TABLE XXXX SHRINK SPACE
                    Doing FLASHBACK TABLE XXXX
                    When splitting a partition
                    When updating a value so that it moves to a new partition
                    When combining two partitions

View

A view is the result of a SQL query stored in the Oracle data dictionary. One can think of a view as a virtual table or presentation of data from one or more tables. Views are useful for security and information hiding, but can cause problems if nested too deep. View details can be queried from the dictionary by querying either USER_VIEWS, ALL_VIEWS or DBA_VIEWS. Can perform DML operations on simple view(Does not contains GROUP BY clause, CONNECT BY clause, JOINS, sub query or snapshot of operations). Cannot perform DML operations on complex view(Contains atleast anyone of the above).

Advantages

• Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of table.
• Hide data complexity.
• Simplify commands for the user.
• Present the data in different prespective from the base table.
• Stores complex queries

Pseudo column

A Pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but they can not be updated. They provide extra information about a row set.

Eg:

ROW ID

ROW NUM

CURRVAL

NEXTVAL... etc.,

Joins

A SQL join clause combines records from two or more tables in a relational database, resulting in a new, temporary table, sometimes called joined table. It relates the tables by means of values common between them.

Types of Join

    
- Inner join

        - Equi-join

            - Natural join

        - Cross join

- Outer join

    - Left Outer join

    - Right Outer join

    - Full Outer join

Inner join

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate.

Eg:

SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID

Equi join

is a specific type of comparator-based join, that uses only equality comparions in the join condition

Natural join

The join condition arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables.

Eg:

SELECT *
FROM employee NATURAL JOIN department

Cross or Cartesian join

A cross join return the cartesian product of the sets of records from the two joined tables. It equates to an inner join where the join-condition always evaluates to true or join condition is absent in statement.

Eg:

SELECT *
FROM employee CROSS JOIN department

SELECT *
FROM employee, department;

Outer joins

An outer join does not require each record in the two tables to have a matching record. The joined table retains each record - even if no other matching record exists. Outer joins subdivided further into left outer joins, right outer joins, depending on which table(s) one retains the rows from (left, right or both)

Left Outer join

A leftouter join returns all the values from the left table and matched values from the right table. (NULL in case of no matching)

Eg:

SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT * FROM employee, department

where employee.DepartmentID (+) = department.DepartmentID

Right outer join

A right outer join returns all the values from the right table and matched values from the left table. (NULL in case of no matching)

Eg:

SELECT *

FROM employee LEFT OUTER JOIN department

ON employee.DepartmentID = department.DepartmentID

SELECT * FROM employee, department
where employee.DepartmentID (+) = department.DepartmentID

Full outer join

A full outer join combines the result of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Eg:

SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM employee
RIGHT JOIN department
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL

SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM department
LEFT JOIN employee
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL

SELECT *

FROM department RIGHT JOIN employee

ON employee.DepartmentID = department.DepartmentID

UNION

SELECT *

FROM employee RIGHT JOIN department

ON employee.DepartmentID = department.DepartmentID

WHERE employee.DepartmentID IS NULL

CO-RELATED SUBQUERY

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.
eg:
SELECT field1 from table1 X
WHERE field2>(select avg(field2) from table1 Y where field1=X.field1);

SQLCODE and SQLERRM

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

%ROWTYPE and %TYPE

%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

Mutating table error

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

Anonymous block

An anonymous block is an unnamed sequence of actions. Since they are unnamed, anonymous blocks cannot be referenced by other program units.
In contrast to anonymous blocks, stored/ named code blocks include Packages, Procedures, and Functions.

---

begin
DBMS_OUTPUT.put_line('Hello world!');
end;

---
declare
i number;
begin;
i := 10;
end;
---

TNSNAMES.ORA

TNS (Transparent Network Substrate) . TNSNAMES.ORA is a configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.

=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = )(Port = ))
)
(CONNECT_DATA =
(SERVICE_NAME = )
)
)

The TNSNAMES.ORA files is located on both client and server systems.

IN vs Exists

Both are used as a filter for searching. IN clause wants the inner query to drive the outer query. EXISTS clause wants the outer query to be run first, using each value to fetch a value from the inner query. in clause is better if the results of the sub query are very small.exists forces to specify join condition, which invokes INDEX SCAN. so its faster than in clause.

- IN = inside to outside

- EXISTS = outside to inside

---

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

When a procedure or package gets invalidated?

- when the objects dropped, which used in the procedure or package.

- when the owner alters the name of the procedure or package.

ACID

Refers to the basic properties of a database transaction.

Atomicity

- the entire sequence of actions must be either completed or aborted. the transaction cannot be partially successful.

Consistency

- the transaction takes the resources from one consistent state to another.

Isolation

- a transaction's effect is not visible to other transactions until the transaction is committed.

Durability

- changes made by the committed transaction are permanent and must survive system failure.

Automomus transaction

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. when an autonomous transaction is called, the origination transaction gets suspended. control is returned when the autonomous transaction does a commit or rollback. A procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION. You may need to increase the transaction parameter to allow for the extra concurrent transactions.

Cartesian join

A Cartesian join is a join of every row of one table to every row of another table. this normally happens when no matching join columns are specified. a query must have at least n-1 joins to avoid a Cartesian product.

Coalesce

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 (expression_1, expression_2, ...,expression_n).

Nvl

Nvl is an oralce sql function that will return a non-null value if a null value is passed to it.

Nvl2

Nvl2 is an oracle sql function that will return different values based on whether the input value is null or not. NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.

Decode

Decode is a sql function that provides similar functionality to an if then else or case statement. SELECT decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') FROM employees;default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return NULL (no matches found).

Nullif

Nullif is an sql function that returns a null value if both parameters are equal in value. if not,the first value will be returned.

Eg:

SELECT NULLIF(1, 2) FROM dual;

RETURN 1.

SELECT NULLIF(1, 1) FROM dual;

RETURN NULL