The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

Original Post: http://www.techonthenet.com/oracle/procedures.php

Use SYSDATE in Oracle:

SELECT SYSDATE FROM dual;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

Original Post: http://decipherinfosys.wordpress.com/2007/07/19/extracting-day-month-or-year-from-a-date-field/

Oracle uses an EXTRACT(datetime) function to extract the value of a specified datetime field from a datetime expression. Function is very handy to manipulate specific datetime field value like day, month or year. Following values can be extracted from the column having date datatype. Apart from values mentioned below, timezone related values like timezone_region, timezone_hour and timezone_minute can also be obtained using EXTRACT function if underlying datatype is TIMESTAMP WITH TIME ZONE. * Day, Month, Year, Hour, Minute, Second

SELECT EXTRACT(Year FROM My_Date) as Year

Original Post:

http://bytes.com/topic/oracle/answers/631642-top-10-rows#post2501910

For top row just use the magic column rownum:

SELECT * FROM your_table WHERE rownum <= 10;

If you want bottom row you can order it before (using ORDER BY your_field DESC)

Use the following:

EXECUTE IMMEDIATE 'CREATE TABLE MyTable (id NUMBER)';

This page explains the hierarchical query using the (start with) connect by clause in an Oracle SQL Query.

http://www.oradev.com/connect_by.jsp

SELECT LPAD(' ', 2*level-1)|| SYS_CONNECT_BY_PATH(last_name, '/') "Path" 
FROM employees 
CONNECT BY PRIOR employee_id = manager_id

See also: http://www.orafaq.com/node/2038

I came across this blog post from Laurent Schneider which saved me a lot of headache since I didn't have to go messing with a workaround!

Original Post:
http://laurentschneider.com/wordpress/2005/12/sys_connect_by_path-in-8i-or-the-danger-to-use-undocumented-parameters.html

I have been posting on metalink technical forum about a query that I run against all my test databases but did not work in production.

as it simpliest form

select sys_connect_by_path(dummy,’:') from dual connect by 1=2;

well, there is nothing wrong with this query. I tried it on 8i, 9iR2, 10gR1 10gR2 and it worked fine. In production, it just refused to work on 8i.

Well, I have an other bitset of 8i on my test environment, and an other operating system version. So I supposed it must be related to the old production 64bits AIX4 os.

After reading the docs again, I discovered that SYS_CONNECT_BY_PATH is not documented in 8i. So why did this work on my test system then? Because I have _new_connect_by_enabled = true in my init.ora.

Ok, on the one hand, I found out an (unsupported) way to let my query run in 8i

alter session set “_new_connect_by_enabled”=TRUE;

On the other hand, I have been tricked by my own parameter file, setting one hidden parameter ages ago, forgetting about it, and the query “unhopefully succeeded” in my test environment… revealing the bug only once distributed !

Simple and effective:

SELECT * FROM v$version WHERE BANNER LIKE 'Oracle%'

Simple and effective:

SELECT TABLE_NAME FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME='PARTNO'

Original post from Jimmy Rishe: http://www.infopathdev.com/forums/p/9479/33529.aspx