oracle

Listing tables, views, procedures, indices, packages, sequences, synonyms, triggers in Oracle

If you want to list tables in MySQL, you simply type the following command:

show tables

Oracle does not provide such a short cut. You have to type the following command:

          
  

Oracle extract function

extract function is used to extract year, month, or date from a date or timezone hour or timezone minute from an interval. Examples:

extract(DAY FROM DATE '2011-01-02') 	-- 02
          
  

Changing case in Oracle

To change the first letter of the word to cap, use the inicap function. Use upper to covert to uppercase and lower to convert to lower case. Examples:

initcap('change case')  -- Change Case
          
  

Oracle NVL2 function

NVL2 function extends the funcitonality of NVL function. NVL function prints the value of the variable unless a null value is encountered; in which case a value is substituted for the null.


Oracle comparing years in dates

Suppose you have two dates and you want to compares the years rather the exact date. Make sure you know the format of the dates. The use the following code:

          
  

Oracle subtring - substr function

The substr function extracts a substring from a string. Syntax:

substr( string, start position, length)

ORA-01858: a non-numeric character was found where a numeric was expected

This error occurs if a wrong value is passed when converting a number or string to date. To fix this problem, find out what date format the date is stored in e.g. yyyymmdd, dd-mm-yy, dd-mon-yy, etc.


Concatenation is Oracle

Peculiarly, Oracle uses the double pipe ( || ) as a concatenation operator. Most programming languages use || for the or operator. Example:

          
  

ORA-00979 - Not a group by expression

All columns of your SELECT must be listed in the GROUP BY expression. Alternately you can use functions MIN() or MAX() which compress results.


Oracle Packages

A package is an encapsulated collection of related PL/SQL types, objects, procedures, and other program objects. Packages have a package specification and a package body.


Oracle NVL function

NVL function can be used to substitute a value when null in encountered. It has the following syntax:

NVL(string,replacement_string)


Listing objects in Oracle

If you want to list tables in MySQL, you simply type the following command:

show tables

Oracle does not provide such a short cut. You have to type the following command:


Oracle set operators: union, intersect, minus

The most commonly used set operators in Oracle are:

- union
- intersect
- minus

Union


Oracle decode function

Decode function has the same functionality as an IF-THEN-ELSE statement. It is present in Oracle 9i and above. It has the syntax

decode(column/expression, value, substitute, default value)


Adding a leading dollar sign ($) in front of a number in Oracle

There are two ways of adding a leading dollar sign to numbers in Oracle.


viagra online generic