Byte My Data

A personal collection of advice and solutions I've used.

About the author

Something about the author

How to find all tables with a specific column name in MS SQL

Simple and effective:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE 'INVOICE%'

Permalink | Comments (3) | Post RSSRSS comment feed

Creating stored procedures in Oracle/PLSQL

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


Categories: Databases
Permalink | Comments (1) | Post RSSRSS comment feed

GETDATE() equivelant in Oracle

Use SYSDATE in Oracle:

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

Categories: Databases
Permalink | Comments (1) | Post RSSRSS comment feed

Get the date parts of a date in Oracle

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

Permalink | Comments (0) | Post RSSRSS comment feed

MS SQL TOP equivelant in Oracle

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)


Categories: Databases
Permalink | Comments (0) | Post RSSRSS comment feed

Create a table in Oracle stored procedure or function

Use the following:

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

Permalink | Comments (0) | Post RSSRSS comment feed

Recursive query in Oracle using CONNECT BY clause

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


Permalink | Comments (0) | Post RSSRSS comment feed

sys_connect_by_path does work in Oracle 8i

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 !


Permalink | Comments (0) | Post RSSRSS comment feed

Get the database version in Oracle.

Simple and effective:

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

Categories: Databases
Permalink | Comments (0) | Post RSSRSS comment feed

How to find all tables with a specific column name in Oracle

Simple and effective:

SELECT TABLE_NAME FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME='PARTNO'

Categories: Databases
Permalink | Comments (0) | Post RSSRSS comment feed