Data Base Tips/ SQL Commands...

Easily change your Oracle password

2005-08-11T02:36:00.000+05:30

From Oracle 8 on, you can use the command:

SQL> password

in SQL*Plus to easily change your password. By using

SQL> password <username>

you can change the password of another user.
This method has the advantage that the new password is not echoed on the screen.

The VSIZE function

2005-08-09T14:29:00.000+05:30

To determine the exact number of bytes occupied of a table. The function 'VSIZE' reports the exact number of bytes allocated by Oracle for data.

For example, to determine the size of a table called 'fuzzy_pink_slippers' with columns 'col_a', 'col_b', ...'col_n', execute the following SQL statement to find the exact size of the table:

SELECT SUM (VSIZE (col_a)) + SUM (VSIZE (col_b)) + ... +
SUM (VSIZE (col_n))
FROM fuzzy_pink_slippers;

Performace / Set Timing On

2005-08-05T02:18:00.000+05:30

We can measure the performance of different queries by setting TIMING:

Set Timing on

Now, each time we run a query, we will see the time it took Oracle to solve it at the end. This is especially good when we are debating on which way is faster (e.g., a join or a sub query).

Commit / No Commit

2005-08-05T02:16:00.000+05:30

Commit will always tell you commit complete But that does not necessarily mean you got what you wanted. Try typing commit; without doing anything, and you will see that you will get the same message. You need to know when oracle does a commit without your asking. For example, creating a table commits the previous information.

The Following actions generate an implicit commit:

1. Quit or exit

2. Create a Table or View (DDL)

3. Drop table, view, and grant

4. Revoke

5. Connect, Disconnect

6. Alter, Audit, noAudit.

We can conclude that the commands like update which just changes the contents are not commited and commands like alter, drop which manipulates the table structure are autocommited. It is from the perspective of the table manipulations.

Creating a Spool File

2005-08-02T20:05:00.000+05:30

Male sure you save each session. This is especially useful at the beginning of your learning experience. Spool files help you analyze the mistakes you make and allow you to continue at a later time as opposed to start all over again. To Create a spool file, you can type:

SQL> spool c:\My Documents\sessionName.lst

The .lst(list) qualifier is nto required, but is highly recommended.

When you complete your session, it is a good practice to close your spool file :

SQL> Spool off;

SQL Scripts

2005-08-01T19:46:00.000+05:30

You can type each SQL Command at the SQL> prompt and end it with a semiconlon - ; This Works fine. You might however, choose to write your commands and execute them altogether, by running a script.

To run a script, you should follow these steps:

1. Using the tect editor of your choice(e.g., Notepad, TexPad, WordPad), create a file called myCommands.sql. The .sql qualifier is not required, but is highly recomended.

2. Scripts can contain commnets. the convention for writing comments is: /* Your comments here, not sql Commands */. You should comment all you scripts. Imagine you want to figure out what you did 6 months down the road!!! you will be thankful you took the time to write comments.

3. Run the script. For example: @C:\My Documents\scriptname.sql will exdcute the script called scriptname.sql located in the My Documents Directory on your C drive.

Uid function

2005-07-27T13:46:00.000+05:30

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.

SQLERRM Function

2005-07-26T14:05:00.000+05:30

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

2005-07-26T00:18:00.000+05:30

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.

chk out this link to know more...

SQLCODE Function SQLCODE Function

2005-07-25T13:55:00.000+05:30

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;

To_Date Function

2005-07-23T01:36:00.000+05:30

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

2005-07-23T01:34:00.000+05:30

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)

2005-07-23T01:32:00.000+05:30

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...!

2005-07-23T01:31:00.000+05:30

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

sys_context function

2005-07-23T01:30:00.000+05:30

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

2005-07-23T01:29:00.000+05:30

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;

Case Statements

2005-07-14T09:55:00.000+05:30

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.

DECODE Statement

2005-07-13T16:11:00.000+05:30

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.

Conversion Function (CAST)

2005-07-12T12:26:00.000+05:30

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

2005-07-12T12:23:00.000+05:30

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;