The Oracle decode function

The decode function can be used in SQL for and IF-THEN-ELSE construction. It's an alternative for the CASE statement which was introduced in Oracle 8.

Syntax:
decode( expression , compare_value, return_value, [,compare, return_value] ... [,default_return_value] )
with:
expression is the value to evaluate
compare_value is the value that can match the evaluated value
return_value is the value that is returned if compare_value equals the value.
The default_return_value is the value that is returned if no match is found.

To evaluate this expression, Oracle compares the expression to each compare_value one by one. If expression is equal to a compare_value, Oracle returns the corresponding return_value. If no match is found, Oracle returns the default_return_value. If no default value is specified, the null value will be returned.

Sample code
select id, decode(status,'A','Accepted','D','Denied','Other')
from contracts;

Will return for each id:
If status = 'A' : 'Accepted'
If status = 'D' : 'Denied'
Else : 'Other'

Oracle automatically converts the values for expression and compare_value to the datatype of the first compare_value. Also the datatype of the return_value is converted to the datatype of the first return_value. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.
Note: two null values are considered equivalent in the decode statement.

RMAN Commands

RMAN


RMAN is short for Recovery Manager.

When the databases use the controlfile as catalog.

Following is a short list of the most common commands.

To connect to RMAN:>>

rman target /

 
 show full backups:>> list backup of database;
 show backups:>> list backup summary;
 show the archivelogs:>> list archivelog all;
 show the datafiles:>> report schema;
 show RMAN settings:>> show all;
 remove a backup:>> delete backupset #;
 remove several backups:>> delete noprompt backup of database completed before 'sysdate-7';
 remove old archivelogs:>> delete noprompt archivelog until time 'sysdate-7';
 test restore of backup:>> restore database validate;
 test restore of controlfile:>> restore controlfile validate;
 check validity:>> crosscheck backup;
 see what needs backing up:>> report need backup;

CHR - function

CHR: The CHR function returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set.

Example: CHR(10)CHR(13) = carriage return plus line feed.

In Oracle/PLSQL, the CHR function is the opposite of the ascii function. It returns the character based on the NUMBER code.

The syntax for the chr function is: CHR( number_code [, USING NCHAR_CS])
where the number_code is the NUMBER code used to retrieve the character.

Use of the CHR function (either with or without the optional USING NCHAR_CS clause) results in code that is not portable between ASCII- and EBCDIC-based machine architectures.

For single-byte character sets, if n > 256, then Oracle returns the binary equivalent of n mod 256. For multibyte character sets, n must resolve to one entire codepoint. Invalid codepoints are not validated, and the result of specifying invalid codepoints is indeterminate.

Create a sequence AAAAA - ZZZZZ

Create a sequence AAAAA - ZZZZZ

--sequence AAAAA to ZZZZZ....... wow.

 select   CHR(65+MOD((rownum-1)/(26*26*26*26), 26))
        ||CHR(65+MOD((rownum-1)/(26*26*26), 26))
        ||CHR(65+MOD((rownum-1)/(26*26), 26))
        ||CHR(65+MOD((rownum-1)/26, 26))
        ||CHR(65+MOD((rownum-1), 26)) as AA_ZZ_sequence
   from dual 
connect by level <= 26*26*26*26*26;

This is along the lines of a trick sql statement and demonstrates what can be achieved when you use the connect by terminology.

Functions

SQL functions are broken down into character functions, number functions, and date functions. Be sure you know how to use these functions for the Oracle Certified Partner (OCP) exam:

Text Functions

• lpad(x,y[,z]) and rpad(x,y[,z]) Return data in string or column x padded on the left or right side, respectively, to width y. The optional value z indicates the character(s) that lpad() or rpad() use to pad the column data. If no character z is specified, a space is used.
• lower(x), upper(x), and initcap(x) Return data in string or column x in lowercase or uppercase characters, respectively, or change the initial letter in the data from column x to a capital letter.
• length(x) Returns the number of characters in string or column x.
• substr(x,y[,z]) Returns a substring of string or column x, starting at the character in position number y to the end, which is optionally defined by the character appearing in the position z of the string.
• instr(x,y) Determines whether a substring y given can be found in string x.
• trim() A single-row function that behaves like a combination of ltrim() and rtrim(). Trim() accepts a string describing the data you would like to trim from a column value using the following syntax: trim([[keyword] 'x' from] column). Here keyword is replaced by leading, trailing, or both, or it's omitted. Also, x is replaced with the character to be trimmed, or it's omitted. If x is omitted, Oracle assumes it must trim whitespace. Finally, column is the name of the column in the table to be trimmed.

Arithmetic Functions

• abs(x) Obtains the absolute value for a number. For example, the absolute value of –1 is 1, whereas the absolute value of 6 is 6.
• round(x,y) Rounds x to the decimal precision of y. if y is negative, it rounds to the precision of y places to the left of the decimal point. This can also be used on DATE columns.
• ceil(x) Similar to executing round on an integer, except ceil always rounds up.
• floor(x) Similar to ceil, except floor always rounds down.
• mod(x,y) The modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced.
• sign(x) Displays an integer value corresponding to the sign of x: 1 if x is positive, -1 if x is negative.
• sqrt(x) The square root of x.
• trunc(x,y) Truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point.
• vsize(x) The storage size in bytes for x.

List Functions

• greatest(x,y,…) Returns the highest value from the list of text, strings, numbers or dates.
• least(x,y,…) Returns the lowest value from the list of text strings, numbers, or dates.
• decode(column name, val1, sub1, val2, sub2,…) Works on the same principle as the if-then-else statement does in many common programming languages.

Date Functions

• add_months(x,y) Returns a date corresponding to date x plus y months.
• last_day(x) Returns the date of the last day of the month that contains date x.
• months_between(x,y) Returns a number of months between dates x and y. If date x is earlier than y, the result is negative; otherwise, the result is positive. If dates x and y contain the same day of different months, the result is an integer; otherwise, the result is a decimal.
• new_time(x,y,z) Returns the current date and time for date x in time zone y as it would be in time zone z.
• next_day(x) Identifies the name of the next day from given date, x.

Several conversion functions are available for transforming data from text to numeric datatypes and back, numbers to dates and back, text to ROWID and back, and so on.

Conversion Functions

• to_char(x) Converts the value x to a character or converts a date to a character string using formatting conventions.
• to_number(x) Converts nonnumeric value x to a number.
• to_date(x[,y]) Converts the nondate value x to a date using the format specified by y.
• to_multi_byte(x) Converts the single-byte character string x to multibyte characters according to national language standards.
• to_single_byte(x) Converts the multibyte character string x to single-byte characters according to national language standards.
• chartorowid(x) Converts the string of characters x into an Oracle ROWID.
• rowidtochar(x) Converts the ROWID value into the string of characters x of VARCHAR2 datatype.

Execute Immediate

This is one of those utilities that you use on a day to day basis without giving a second thought once you learn just enough to be dangerous.

The syntax is fairly straightforward:

EXECUTE IMMEDIATE {sql stmnt}
[INTO var.....]
[USING [IN | OUT | IN OUT] {bind}....]
[{RETURNING | RETURN} INTO {bind} ];

This parses and executes a sql statement in a single step. This is for any SQL statement except multi-row queries - for that you use a cursor.

Please note that the EXECUTE IMMEDIATE statement ends with a semi-colon but the SQL statement being executed does not.

Converting Between Character Sets

If you have extended characters with accents you can convert them consistently and safely as follows:

SELECT CONVERT ('ediária', 'US7ASCII', 'WE8ISO8859P1') AS converted 
  FROM DUAL;

ediaria

Consistent and already available for you to use.

Number to Words Conversion

This is something useful only as a trick or a quick hack.

How do you get from a number to the same number spelled out in words.

Here is something that works perfectly well but was not intended to be used in the way this example shows. It is a mistreatment of the to_char function based on a manipulating a date for output.

SELECT n, TO_CHAR (DATE '-4712-01-01' + (n - 1), 'jspth')
  FROM (SELECT 1721058 n
          FROM DUAL);

one million seven hundred twenty-one thousand fifty-eighth

So it does work - just don't rely on it - it is after all a to_char on a date field.

Quick Text Count

Ever wondered how a search engine gives you such a quick answer: 5000 pages match your search term....

Here's how to do it in Oracle using Oracle Text looking for the words my and search.

declare
lcount number;
begin
 lcount := ctx_query.count_hits(index_name => 'IDX_IM_SEARCHIDX',
                                text_query => 'my and search',                        
                                exact => false);
dbms_output.put_line('Number of matching docs '||lcount);
end;

The call to count_hits can be adjusted for accuracy. (exact => true) takes longer to complete but is accurate while (exact => false) gives a best guess, usually very close anyway.

Note no column was queried. The index was hit directly and assuming there is no thesaurus or fuzzy matching taking place results are extremely fast.

REGEXP_REPLACE

Get rid of extra spaces.

WITH t AS (SELECT 'NO MORE  EXTRA      SPACES' str FROM dual)
SELECT REGEXP_REPLACE(str, ' {2,}', ' ') str
  FROM t;

STR                 
--------------------
NO MORE EXTRA SPACES


1 row selected.

This is actually a very neat example of solving a simple problem using regular expressions. Of course regular expressions are so much more powerful, indeed books have been written about them, but that is beyond the scope of this simple example.

CUBE

I just found this script in my collection

SELECT *
  FROM (SELECT   c1, c2, c3, c4, c5, c6
          FROM (SELECT 'a' c1, 'b' c2, 'c' c3, 'd' c4, 'e' c5, 'f' c6
                  FROM DUAL)
         GROUP BY CUBE (c1, c2, c3, c4, c5, c6));


C1 C2 C3 C4 C5 C6
               
               f
            e   
            e  f
         d      
         d     f
         d  e   
         d  e  f
      c         
      c        f
      c     e   
      c     e  f
      c  d      
      c  d     f
      c  d  e   
      c  d  e  f
   b            
   b           f
   b        e   
   b        e  f
   b     d     
   b     d     f
   b     d  e  
   b     d  e  f
   b  c         
   b  c        f
   b  c     e   
   b  c     e  f
   b  c  d      
   b  c  d     f
   b  c  d  e   
   b  c  d  e  f
a               
a              f
a           e   
a           e  f
a        d     
a        d     f
a        d  e  
a        d  e  f
a     c        
a     c        f
a     c     e  
a     c     e  f
a     c  d     
a     c  d     f
a     c  d  e   
a     c  d  e  f
a  b            
a  b           f
a  b        e   
a  b        e  f
a  b     d      
a  b     d     f
a  b     d  e   
a  b     d  e  f
a  b  c         
a  b  c        f
a  b  c     e   
a  b  c     e  f
a  b  c  d      
a  b  c  d     f
a  b  c  d  e   
a  b  c  d  e  f

and it illustrates the beauty (and complexity) of CUBE.

A very simple SQL example that shows off what is possible from something very simple.

NVL

Returning data is the function of a SELECT statement, and it does it very well. But what do we do with that data where we don't know what it is - the dreaded null.

It is not valid to say that a null value returned from a select statement is incorrect. It is entirely possible that for the row of data returned that particular piece of data is unknown, or was unknown at data entry time.

We do however, have a problem when it comes to performing calculations with nulls. A null value in a column will not affect a sum or avg calculation though they treat it differently.

Running the following example:

create table t (x number);

insert into t values (1);
insert into t values (1);
insert into t values (1);
insert into t values (null);
insert into t values (1);

select sum(x) , avg(x) , count(x) from t;

gives.....

    SUM(X)     AVG(X)   COUNT(X)
---------- ---------- ----------
         4          1          4


1 row selected.

The average and count seem to be wrong. Actually - the way they are written they 'ignore' rows with null values. Sum on the other hand seems to assume that null means zero. Correct me if I am wrong.

We can affect the results by telling the SUM and AVG functions to 'fill-in' a value if there is a null by using NVL.

SELECT SUM (NVL (x, 0)), AVG (NVL (x, 0)), 
       COUNT (NVL (x, 0)), COUNT (*)
  FROM t;

SUM(NVL(X,0)) AVG(NVL(X,0)) COUNT(NVL(X,0))   COUNT(*)
------------- ------------- --------------- ----------
            4            .8               5          5


1 row selected.

And all because of a null..........

Number to Words

A neat trick to convert a number to the same number in words ( 12 becomes twelve ).

select n, to_char(date '-4712-01-01' + (n-1),'jspth')
  from (select 1721058 n from dual);

Try it out and play with the NLS LANG settings for different results.

When is a Null Null

Since the dawn of the computer age 'NULL' has confused and misled developers and designers alike. What makes NULL deserve so much attention and frustration in Oracle.

Let's look at what NULL is for a start.

Null is an unknown value. It is not no value it is indeterminate.

So by that definition we can never know the value of something that is null. It is not less than or greater than a value. In fact a NULL cannot be compared to another NULL for equality or otherwise. One unknown value is not equal to another unknown value neither are they not equal.

This introduces the IS NULL operator and allows a demonstration to sum up what has been said above in a more Oracle centric way.

SELECT 'NULL = NULL' AS "result"
  FROM DUAL
 WHERE NULL = NULL
 UNION ALL
SELECT 'NULL <> NULL' AS "result"
  FROM DUAL
 WHERE NULL <> NULL
 UNION ALL
SELECT 'NULL <= 0' AS "result"
  FROM DUAL
 WHERE NULL <= 0
 UNION ALL
SELECT 'NULL >= 0' AS "result"
  FROM DUAL
 WHERE NULL >= 0
 UNION ALL
SELECT 'NULL IS NULL' AS "result"
  FROM DUAL
 WHERE NULL IS NULL;

result
---------------
NULL IS NULL

The only way to find out if something is null or has a null value is to test it for 'nullness'.

SUBSTR

We have all used SUBSTR at some time in our varied programming careers and can be fairly justified that we get what we need from the function.

As in previous articles there is more to this simple function than meets the eye.

The basic format of all SUBSTR type functions is that they take an input string, a start position and a number of characters and return that portion of the input string from the start position to the length of the number of characters requested.

for example:

SELECT SUBSTR ('1234567890', 5, 3)
  FROM DUAL;


SUB
---
567

1 row selected.

starts at position 5 and returns the next three characters from the input string.

There are several overloaded variations on this.

To return the remaining string from position 5 to the end you would write something like

SELECT SUBSTR ('1234567890', 5)
  FROM DUAL;


SUBSTR
------
567890

1 row selected.

The last argument has been omitted, and the assumption, on the part of the programmer, is that the number of characters requested is to the end of the string.

So now what? For 99% of what we want to do this is all we want and need to know. With a little manipulation we can extract portions of any string if we know its length and have a starting position.

What we should be doing is either, having a quick dip into the manual to see if that is all there is to SUBSTR, or trying to break it by throwing assorted combinations of inputs at it. So here goes:

SELECT SUBSTR (1234567890, 5, 2)
  FROM DUAL;


SU
--
56

1 row selected.

Well it seems to handle implicit conversions of numbers into strings, how about trying some negative number inputs? Lets see if we can get the number of characters before a start postion.

SELECT SUBSTR ('1234567890', 5 , -2)
  FROM DUAL;






1 row selected.

Not quite broken, but no error thrown. So that didn't work out too well, lets now try reversing the whole idea and seeing if a negative starting position has any effect?

SELECT SUBSTR ('1234567890', -4 , 2)
  FROM DUAL;


SU
--
78

1 row selected.

So from this example we can go 4 positions back from the end of the input string to start and return the 2 characters from there onwards. Omitting the number of characters returns the remainder of the input string. This means that I can now find the last x number of characters of any length input string without knowing it's length.

As an exercise to you, try repeating the last SQL statement but replace -4 with -14.

TRUNC

TRUNC is one of those functions in Oracle you use and cast away probably without realising the true power available in the command.

Most programmers are aware that to 'TRUNC' a number removes all information to the right of the decimal point. So for example

SELECT TRUNC(2.7) FROM DUAL;


TRUNC(2.7)
----------
      2

1 row selected.

Some programmers are aware that you can TRUNC a date data type to return the same date with the time portion set to midnight (00:00:00).

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') as NO_TRUNC ,
    TO_CHAR(TRUNC(SYSDATE),'DD/MM/YYYY HH24:MI:SS') as WITH_TRUNC
FROM DUAL;


NO_TRUNC            WITH_TRUNC      
------------------- -------------------
17/04/2007 10:35:00 17/04/2007 00:00:00

1 row selected.

And there most programmers knowledge of TRUNC stops. Probably because that is sufficient for most programming tasks that need to be taken care of. The other reason is, probably, that most programmers first encountered TRUNC while examining other people's code and thought that that was all there was to know about using this function. There is more to TRUNC than just this 'neat' trick. As with most inbuilt functions, TRUNC has been overloaded to accept additional arguments.

TRUNC no longer has to be wrapped in a SQL statement using the dual table but can also be use in direct variable assignments in PL/SQL as follows:

DECLARE
dt   DATE := TRUNC (SYSDATE);
BEGIN
NULL;
END;

Not the most elegant of code, but it does illustrate the point.

When dealing with number data types the additional argument is the number of decimal places to TRUNC to. Note that changing the number to negative replaces numbers to the left of the decimal separator with zeros.

SELECT TRUNC (77.77, 1) AS pos, TRUNC (77.77, -1) AS neg
FROM DUAL;


    POS        NEG
---------- ----------
   77.7         70

1 row selected.

For date data types TRUNC has been overloaded to accept a format mask. If you are familiar with casting date data types to their character equivalents using the TO_CHAR function you should be aware of format masks. The format masks I tend to use most are 'MM' for month and 'YYYY' for year, which give you the first day of the month and first day of the year respectively.

SELECT TO_CHAR (TRUNC (SYSDATE, 'MM'), 'DD-MON-YYYY') AS mon,
    TO_CHAR (TRUNC (SYSDATE, 'YYYY'), 'DD-MON-YYYY') AS yr
FROM DUAL;


MON         YR      
----------- -----------
01-APR-2007 01-JAN-2007

1 row selected.

So there you have it, some hidden functionality of a simple inbuilt function revealed to you. No doubt there may be more available features in later releases of Oracle, but for now 99% of my coding and SQL needs have been satisfied.

Remember that even the most innocuous of commands may have hidden features. Features that are only hidden in the manual waiting for you to discover them.

A List of Values

How often do you need to generate a list of numbers. OK well perhaps not every day, but the technique shown here should be easily adaptable to a number of situations.

WITH tbl AS
    (SELECT     ROWNUM AS RN
           FROM DUAL
     CONNECT BY LEVEL < 10 )
SELECT *
 FROM tbl;

This will generate a result set of nine rows each row containing one of the numbers 1 - 9.

Notice I could have just written

SELECT     ROWNUM AS rn
     FROM DUAL
CONNECT BY LEVEL < 10 

This example also introduces the WITH keyword which I personally find extremely useful. Other colleagues find it annoying, though I expect that will change once they become accustomed to using it more.

Personally I like the structure using WITH enforces on my coding. I also find understanding the workings of SQL statements much easier. Maybe it's because the inner workings are broken up into more accesible chunks, with the inline view code moved out of the FROM clause, decluttering the SQL.