Data Base Tips/ SQL Commands...

Wednesday, July 27, 2005

Uid function

In Oracle/PLSQL, the uid function returns the id number for a user's session (the user who is currently logged in).

The syntax for the uid function is:


select uid 
from dual;


For example:


select uid 
into parm_uid
from dual;


The variable called parm_uid will now contain the id number for the user's session.  The value could be 25, for example.

 

Tuesday, July 26, 2005

SQLERRM Function

The SQLERRM function returns the error message associated with the most recently raised error exception.  This function should only be used within the Exception Handling section of your code:

EXCEPTION
    WHEN exception_name1 THEN
        [statements]

    WHEN exception_name2 THEN
        [statements]

    WHEN exception_name_n THEN
        [statements]

    WHEN OTHERS THEN
        [statements]  

END [procedure_name];

   
You could use the SQLERRM function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-2 0001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);
      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;
  
 

ETL- Extract, Transform, and Load

DEFINITION:

ETL stands for extract, transform and load, the processes that enable companies to move data from multiple sources, reformat and cleanse it, and load it into another database, a data mart or a data warehouse for analysis, or on another operational system to support a business process.

Moving and Improving Data

chk out this link to know more...

Monday, July 25, 2005

SQLCODE Function SQLCODE Function

The SQLCODE function returns the error number associated with the most recently raised error exception.  This function should only be used within the Exception Handling section of your code:

EXCEPTION 
    WHEN exception_name1 THEN 
        [statements]

    WHEN exception_name2 THEN 
        [statements]

    WHEN exception_name_n THEN 
        [statements]

    WHEN OTHERS THEN 
        [statements] 

END [procedure_name];

You could use the SQLCODE function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;



Saturday, July 23, 2005

To_Date Function

In Oracle/PLSQL, the to_date function converts a string to a date.

The syntax for the to_date function is:

 

to_date (string1, [format_mask], [nls_language] )

 

string1 is the string that will be converted to a date.

format_mask is optional.  This is the format that will be used to convert string1 to a date.

nls_language is optional.  This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter  These parameters can be used in many combinations.

 

Parameter

Explanation

YEAR

Year, spelled out

YYYY

4-digit year

YYY
YY
Y

Last 3, 2, or 1 digit(s) of year.

IYY
IY
I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard

RRRR

Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

RM

Roman numeral month (I-XII; JAN = I).

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

D

Day of week (1-7).

DAY

Name of day.

DD

Day of month (1-31).

DDD

Day of year (1-366).

DY

Abbreviated name of day.

J

Julian day; the number of days since January 1, 4712 BC.

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

FF

Fractional seconds.  Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds.  For example, 'FF4'.

AM, A.M., PM, or P.M.

Meridian indicator

AD or A.D

AD indicator

BC or B.C.

BC indicator

TZD

Daylight savings information.  For example, 'PST'

TZH

Time zone hour.

TZM

Time zone minute.

TZR

Time zone region.

 

For example:

to_date ('2003/07/09', 'yyyy/mm/dd');

would return a date value of July 9, 2003.

to_date ('070903', 'MMDDYY');

would return a date value of July 9, 2003.

to_date ('20020315', 'yyyymmdd');

would return a date value of Mar 15, 2002.

 


To_Char Function

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

 

to_char (value, [format_mask], [nls_language] )

 

value can either be a number or date that will be converted to a string.

format_mask is optional.  This is the format that will be used to convert value to a string.

nls_language is optional.  This is the nls language used to convert value to a string.

 

Examples - Numbers
 

The following are number examples for the to_char function.

to_char (1210.73, '9999.9')

would return '1210.7'

to_char (1210.73, '9,999.99')

would return '1,210.73'

to_char (1210.73, '$9,999.00')

would return '$1,210.73'

to_char (21, '000099')

would return '000021'

 

Examples – Dates

 

The following is a list of valid parameters when the to_char function is used to convert a date to a string.  These parameters can be used in many combinations.

Parameter

Explanation

YEAR

Year, spelled out

YYYY

4-digit year

YYY
YY
Y

Last 3, 2, or 1 digit(s) of year.

IYY
IY
I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

RM

Roman numeral month (I-XII; JAN = I).

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

D

Day of week (1-7).

DAY

Name of day.

DD

Day of month (1-31).

DDD

Day of year (1-366).

DY

Abbreviated name of day.

J

Julian day; the number of days since January 1, 4712 BC.

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

FF

Fractional seconds.


 

The following are date examples for the to_char function.

 

to_char (sysdate, 'yyyy/mm/dd');

would return '2003/07/09'

to_char (sysdate, 'Month DD, YYYY');

would return 'July 09, 2003'

to_char (sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char (sysdate, 'MON DDth, YYYY');

would return 'JUL 09TH, 2003'

to_char (sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char (sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

 

You will notice that in some examples, the format_mask parameter begins with "FM".  This means that zeros and blanks are suppressed.  This can be seen in the examples below.

 

to_char (sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char (sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char (sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

 

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

 


Trunc Function (with dates)

In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure.

The syntax for the trunc function is:

 

trunc ( date,  [ format ] )

 

date is the date to truncate.

 

Format is the unit of measure to apply for truncating.  If the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

 
Below are the valid format parameters:


Unit

Valid format parameters

Year

SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y

ISO Year

IYYY, IY, I

Quarter

Q

Month

MONTH, MON, MM, RM

Week

WW

IW

IW

W

W

Day

DDD, DD, J 

Start day of the week

DAY, DY, D

Hour

HH, HH12, HH24

Minute

MI

Example:

trunc (to_date ('22-AUG-03'), 'YEAR')

would return '01-JAN-03'

trunc (to_date ('22-AUG-03'), 'Q')

would return '01-JUL-03'

trunc (to_date ('22-AUG-03'), 'MONTH')

would return '01-AUG-03'

trunc (to_date ('22-AUG-03'), 'DDD')

would return '22-AUG-03'

trunc (to_date ('22-AUG-03'), 'DAY')

would return '17-AUG-03'


DB Monitoring...!

A website useful in DB monitoring and tunning. http://www.rocket99.com/oracle/oracle22.html

sys_context function

In Oracle/PLSQL, the sys_context function can be used to retrieve information about the Oracle environment.

The syntax for the sys_context function is:

 

sys_context (namespace, parameter, [length])

 

Namespace is an Oracle namespace that has already been created.  If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.

Parameter is a 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.  If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytes.

 

 

The valid parameters for the namespace called 'USERENV' are as follows:

Parameter                                   Explanation    

CLIENT_INFO                                User session information

CURRENT_SCHEMA                      Returns the default schema used in the current schema

CURRENT_SQL                         Returns the SQL that triggered the audit event  64

CURRENT_USER                        Name of the current user

DB_NAME                  Name of the database from the DB_NAME initialization parameter

EXTERNAL_NAME                       External of the database user

HOST                              Name of the host machine from which the client has connected

INSTANCE                                    The identifier number of the current instance

IP_ADDRESS                                  IP address of the machine from which the client has connected

NLS_CALENDAR                        The calendar of the current session

NLS_CURRENCY                        The currency of the current session

NLS_DATE_FORMAT                     The date format for the current session

NLS_DATE_LANGUAGE                   The language used for dates    

NLS_SORT                                    BINARY or the linguistic sort basis

OS_USER                                 The OS username for the user logged in       

 

For example:

sys_context ('USERENV', 'NLS_DATE_FORMAT')                  would return 'RR-MM-DD'

sys_context ('USERENV', 'NLS_SORT')                                 would return 'BINARY'



Variance Function

In Oracle/PLSQL, the variance function returns the variance of a set of numbers.

The syntax for the variance function is:

variance ( expression )

expression is a numeric expression.

For example:

select variance (char_length)
from all_tab_columns;

Thursday, July 14, 2005

Case Statements

In Oracle 9i, you can use the case statement within an SQL statement.  It has the functionality of an IF-THEN-ELSE statement.


The syntax for the case statement is:

CASE expression

WHEN condition_1 THEN result_1

WHEN condition_2 THEN result_2

...

WHEN condition_n THEN result_n

ELSE result END


expression is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype.  Conditions are evaluated in the order listed.  Once a condition is found to be true, the

case statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype.  This is the value returned once a condition is found to be true.

Note:


If no condition is found to be true, then the case statement will return the value in the ELSE clause. If the ELSE clause is omitted and no condition is

found to be true, then the case statement will return NULL.  You can have up to 255 comparisons in a case statement.  Each WHEN ... THEN clause

is considered 2 comparisons.

For Example:

You could use the case statement in an SQL statement as follows:

SELECT TABLE_NAME,

CASE OWNER

WHEN 'SYS' THEN 'THE OWNER IS SYS'

WHEN 'SYSTEM' THEN 'THE OWNER IS SYSTEM'

ELSE 'THE OWNER IS ANOTHER VALUE' END

FROM ALL_TABLES;


The above case statement is equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN

     result := 'The owner is SYS';

ELSIF owner = 'SYSTEM' THEN

    result := 'The owner is SYSTEM'';

ELSE

    result := 'The owner is another value';

END IF;


The case statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional.  You could have omitted it.  Let's take a look at the SQL statement

above with the ELSE clause omitted.

Your SQL statement would look as follows:

SELECT TABLE_NAME,

CASE OWNER

WHEN 'SYS' THEN 'THE OWNER IS SYS'

WHEN 'SYSTEM' THEN 'THE OWNER IS SYSTEM' END

FROM ALL_TABLES;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

Wednesday, July 13, 2005

DECODE Statement

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode ( expression , search , result [, search , result]... [, default] )

  
expression is the value to compare.  

search is the value that is compared against expression.  

result is the value returned, if expression is equal to search.  

default is optional.  If no matches are found, the decode will return default.  If default is omitted, then the decode statement will return null (if no matches are found).  

For Example:  

You could use the decode function in an SQL statement as follows:  

SELECT supplier_name,

           decode (supplier_id, 10000,  'IBM',  

                                                10001,  'Microsoft',  

                                                 10002,  'Hewlett Packard',  

                                                              'Gateway') result  

FROM suppliers;  


The above decode statement is equivalent to the following IF-THEN-ELSE statement:  

IF supplier_id = 10000 THEN

     result := 'IBM';  

ELSIF supplier_id = 10001 THEN

    result := 'Microsoft';  

ELSIF supplier_id = 10002 THEN

    result := 'Hewlett Packard';  

ELSE

    result := 'Gateway';  

END IF;  

  
The decode function will compare each supplier_id value, one by one.  

Tuesday, July 12, 2005

Conversion Function (CAST)

In Oracle/PLSQL, the cast function converts one datatype to another.

The syntax for the cast function is:

cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

For example:

SELECT CAST ( '22-AUG-2003' AS VARCHAR2(30) ) FROM DUAL;

This would convert the date (i.e.: 22-Aug-2003) into a varchar2(30) value.

Coalesce Function

In Oracle/PLSQL, 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.

The syntax for the coalesce function is:

coalesce ( expr1, expr2, ... expr_n )

For Example:

You could use the coalesce function in an SQL statement as follows:

SELECT COALESCE ( ADDRESS1, ADDRESS2, ADDRESS3 ) RESULT FROM SUPPLIERS;

The above coalesce statement is equivalent to the following IF-THEN-ELSE statement:


IF address1 is not null THEN

     result := address1;

ELSIF address2 is not null THEN

    result := address2;

ELSIF address3 is not null THEN

    result := address3;

ELSE

    result := null;

END IF;