view com numeros de 1 ..999

SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 999

Evitar que o sql plus reconheça & como variável

set define off;

MSDN equivalente para oracle (10g)

http://www.oracle.com/pls/db102/homepage

existe também para outras versões...uma questão de procurar que perdi o link :p

Apagar duplicados

from: http://www.devx.com/tips/Tip/14665

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

alterar o formato predefenido de output de data no oracle

ALTER SESSION SET NLS_DATE_FORMAT = 'MONTH-DD-YYYY';

Caractéres de escape em oracle

How does one escape special characters when writing SQL queries?

Escape quotes

Use two quotes for every one displayed. Examples:

SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
TEXT
--------------------
Franks's Oracle site

SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;
TEXT
----------------
A 'quoted' word.

SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;
TEXT
-------------------------
A ''double quoted'' word.

Escape wildcard characters

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:

SELECT name FROM emp
WHERE id LIKE '%/_%' ESCAPE '/';

SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';

Escape ampersand (&) characters in SQL*Plus

When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:

SET DEFINE ~
SELECT 'Lorel & Hardy' FROM dual;

Other methods:

Define an escape character:

SET ESCAPE '\'
SELECT '\&abc' FROM dual;

Don't scan for substitution variables:

SET SCAN OFF
SELECT '&ABC' x FROM dual;

Use the 10g Quoting mechanism:

Syntax
q'[QUOTE_CHAR]Text[QUOTE_CHAR]'
Make sure that the QUOTE_CHAR doesnt exist in the text.

SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;


Via [link]

CURSOR VS SYS_REFCURSOR

Explicação:
http://forums.oracle.com/forums/thread.jspa?threadID=329109