Oracle NVL function

Oracle NVL function

         NVL function replaces null value with same type data,

for ex:  If employee has no comm , instead of displaying NULL , u can display string like 'No Commission'

Display Employees getting no comm as zero.
SQL> select ename,nvl(comm,0) from emp;
ENAME      NVL(COMM,0)

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

SMITH      0

ALLEN      300

WARD       500

JONES      0

MARTIN     1400

BLAKE      0

CLARK      0

SCOTT      0

KING       0

TURNER     0

ADAMS      0


JAMES      0

FORD       0

MILLER     0

14 rows selected.

.
Query:

SQL> select ename,nvl(to_char(comm),'no comm') from emp;

ENAME      NVL(TO_CHAR(COMM),'NOCOMM')
---------- ----------------------------------------
SMITH      no comm
ALLEN      300
WARD       500
JONES      no comm
MARTIN     1400
BLAKE      no comm
CLARK      no comm
SCOTT      no comm
KING       no comm
TURNER     0
ADAMS      no comm

ENAME      NVL(TO_CHAR(COMM),'NOCOMM')
---------- ----------------------------------------
JAMES      no comm
FORD       no comm
MILLER     no comm

14 rows selected.

Ex2:  Get total salary of each department and Grand Total of employees,title total sum as "Grand Total".

SQL> select nvl(to_char(deptno),'Grand Total'), sum(sal) from emp group by  rollup(deptno);

O/P:

NVL(TO_CHAR(DEPTNO),'GRANDTOTAL')          SUM(SAL)
---------------------------------------- ----------
10                                             8750
20                                            10875
30                                             9400
Grand Total                                   29025


Note: In this example, we grouping by department and then calculating sum(sal) for each department and then applying rollup to produce grand total.

If you don't use  NVL,  Grand Total will be null value. So NVL replacing null values with String.

Tags:Oracle NVL function,Oracle 11g NVL function, Replace Null values with String using NVL,Using NVL in Oracle 11g.

Find Oracle Version

Find Oracle Version

 

Select * v$version  ;

 

BANNER
--------------------------------------------------------------------

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Tags:Find Oracle Version,Find PL/SQL Version,Find Pl/SQL Release version

Oracle REVERSE String function

Oracle REVERSE String function

String REVERSE in Oracle reverses the the order of character sequence.

         Syntax:    Reverse(characterSequence)

select REVERSE('Audi') from dual;

O/P:

 

REVE
----
iduA

Tags:Oracle REVERSE String function,Reverse String in Oracle,String Reverse in Oracle,Oracle 11g String Reverse

Oracle Pseudocolumns

Oracle 11g Pseudocolumns

• ROWNUM Pseudocolumn
• XMLDATA Pseudocolumn
• COLUMN_VALUE Pseudocolumn
• OBJECT_ID Pseudocolumn
• OBJECT_VALUE Pseudocolumn
• ORA_ROWSCN Pseudocolumn
• ROWID Pseudocolumn
• VERSION_QUERY Pseudocolumn
• Sequence Pseudocolumn
• Hirarchical Query Pseudocolumn 

Tags: Oracle Pseudocolumns,Oracle 11g Pseudocolumns,ROWNUM Pseudocolumn,XMLDATA Pseudocolumn,COLUMN_VALUE Pseudocolumn,OBJECT_ID Pseudocolumn,OBJECT_VALUE Pseudocolumn,ORA_ROWSCN Pseudocolumn,ROWID Pseudocolumn,VERSION_QUERY Pseudocolumn,Sequence Pseudocolumn,Hirarchical Query Pseudocolumn

Oracle Pseudocolumns

Oracle 11g Pseudocolumns

Oracle Data Types

Oracle 11g Data Types

Oracle Datatypes 11g

Oracle Datatypes 11g

DataType	Description
Varachar2	Variable length character String
NVARCHAR2	Variable length unicodecharacter String
Number	Variable length character String
Float	Variable length character String
Long	Character Data upto 2GB
Date	Simple Date from jan 1 4712 to Dec 31 9999
BINARY_FLOAT	Simple Date from jan 1 4712 to Dec 31 9999
BINARY_DOUBLE	Simple Date from jan 1 4712 to Dec 31 9999
TIMESTAMP	Year month day hour minute second and fractional seconds upto 9 digit precesions
TIMESTAMP [(fractional_seconds)] WITH TIME ZONE	Year month day hour minute second and fractional seconds upto 9 digit precesions and Time Zone Information
TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE	•Data is normalized to the database time zone
INTERVAL YEAR [(year_precision)] TO MONTH	Stores period of time in years and months.

Execute Oracle script file in SqlPlus

Execute Oracle script file in SqlPlus.


If ur Oracle sqlplus is located in c:\oraclexe\app\oracle\product\11.2.0\server\bin>


go to c:\oraclexe\app\oracle\product\11.2.0\server\bin> type sqlplus
enter username and password.


If your .sql script is located in C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin

from sqlplus type

SQL>@../rdbms/admin/scott.sql


if user is in in same directory as script directory


type SQL>@scriptname.sql



Tags: execute .sql in sqlplus, executing .sql in sql plus,execute an sql script in sqlplus.