Oracle Query Tools

รวมโปรแกรมสำหรับ Query Oracle Database

- Oracle SQL Developer (Free)
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

- PL/SQL Developer (Free Trial)
http://www.allroundautomations.com/

- DreamCoder SQL Developer (Free)
http://www.sqldeveloper.net/

-Toad for Oracle
http://toadfororacle.com/index.jspa

- Navicat for Oracle
http://www.navicat.com/


ข้อมูลบางส่วนมาจาก
http://www.narisa.com/forums/index.php?showtopic=28424

ORACLE String Function Reference

แปลง String เป็นตัวใหญ่ทั้งหมด (UPPERCASE)

SELECT UPPER('Super Louis') FROM dual;

แปลง String เป็นตัวเล็กทั้งหมด (LOWERCASE)

SELECT LOWER('Super Louis') FROM dual;

Function สำหรับ Concat String

SELECT 'String1 ' || 'String2 ' || dummy as concatString
FROM dual;

SELECT CONCAT(CONCAT('String1 ','String2 '),dummy) as concatString
FROM dual

More Information: http://www.psoug.org/reference/string_func.html

DateTime Oracle

http://www.dba-oracle.com/t_date_math_manipulation.htm
http://www.psoug.org/reference/date_func.html
http://www.akadia.com/services/ora_date_time.html

ALTER SESSION SET TIME_ZONE = '+7:0';

select sysdate from dual

select add_months( sysdate,2)
from dual



select user_id,
to_char( to_date('01011970','ddmmyyyy') + 1/24/60/60 * view_date, 'dd-mm-yyyy hh24:mi:ss') AS TS2Date,
to_char( to_date('01011970','ddmmyyyy') + 1/24/60/60 * view_date, 'mm-yyyy') AS TS2Date2,
score,
max_score,
pass_score,
result,
course_id
from test_history


select sysdate + 1/24/60/60 + 420/1440
from dual

select sysdate + 1/24/60/60
from dual;

select sysdate + 420/24/60
from dual

select to_date('01011970','ddmmyyyy') + 1/24/60/60
from dual

select TRUNC(SYSDATE+1/24,'HH')
from dual

Change datetime format and language on oracle

SELECT USERENV('language') from dual;

SELECT to_char(sysdate,'dd-mon-yyyy') FROM dual;

ALTER SESSION SET nls_language = 'AMERICAN';

SELECT to_char(sysdate,'dd-mon-yyyy') FROM dual;

ALTER SESSION SET nls_language = 'THAI';

SELECT to_char(sysdate,'dd-mon-yyyy') FROM dual;

http://www.dba-oracle.com/t_nls_date_format_sysdate.htm
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

SQL Server Linked Server

Query from link server

for default instance name

SELECT * FROM [LinkServerName].DatabaseName.dbo.TableName

SELECT * FROM OPENQUERY([LinkServerName], 'SELECT * FROM TableName')

for other instance name

SELECT * FROM [LinkServerName\InstanceName].DatabaseName.dbo.TableName

SELECT * FROM OPENQUERY([LinkServerName\InstanceName], 'SELECT * FROM TableName')

other method

SELECT * FROM LinkServerName..UserName.TableName

Oracle SQL *Plus write text to screen

SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line('Test ' || 'concat');
3 end;
4 /
Test concat

How to use trigger on Oracle

Prepare table

CREATE TABLE t1
(
c1 INTEGER,
c2 VARCHAR(10),
c3 VARCHAR(10)
)
CREATE TABLE t1_audit
(
a1 DATE,
a2 VARCHAR(3),
c1 INTEGER,
c2 VARCHAR(10),
c3 VARCHAR(10)
)
insert into t1 values (1,'111','aaa');
insert into t1 values (2,'222','bbb');
insert into t1 values (3,'333','ccc');
insert into t1 values (4,'444','ddd');

Create trigger

CREATE OR REPLACE TRIGGER t1_trig
AFTER UPDATE ON t1
FOR EACH ROW
BEGIN
IF(:NEW.c2 IS NULL) THEN
INSERT INTO t1_audit VALUES (SYSDATE, 'OLD', :OLD.c1, :OLD.c2, :OLD.c3);
INSERT INTO t1_audit VALUES (SYSDATE, 'NEW', :NEW.c1, :NEW.c2, :NEW.c3);
END IF;
END;

or use this command

CREATE OR REPLACE TRIGGER t1_trig
AFTER UPDATE ON t1
FOR EACH ROW
WHEN (NEW.c2 IS NULL)
BEGIN
INSERT INTO t1_audit VALUES (SYSDATE, 'OLD', :OLD.c1, :OLD.c2, :OLD.c3);
INSERT INTO t1_audit VALUES (SYSDATE, 'NEW', :NEW.c1, :NEW.c2, :NEW.c3);
END;

Test the trigger

UPDATE t1
SET t1.c2 = ''
WHERE t1.c1 = 3;

View result on t1_audit

07/08/2008 14:26:02 OLD 3 333 ccc
07/08/2008 14:26:02 NEW 3 ccc

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-triggers.html
http://www.psoug.org/reference/table_trigger.html
http://www.exforsys.com/tutorials/oracle-9i/oracle-database-triggers.html
http://somkiat.exteen.com/20040715/trigger-short-tutorial

Show row number in SQL Server and Oracle

SQL Server:

USE Northwind
SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID DESC) AS RowID, *
FROM EMPLOYEES ORDER BY FirstName

Show row number in SQL Server 2000, 2005
http://support.microsoft.com/kb/186133


Oracle:

SELECT rownum, column_name FROM user_tables

Show row number in Oracle
http://blog.lishman.com/2008/03/rownum.html

Get current datetime

SQL Server:

SELECT GETDATE()

SELECT {fn NOW()}

SELECT CURRENT_TIMESTAMP

Oracle:

SELECT SYSDATE AS Now FROM dual;

SELECT CURRENT_DATE AS Now FROM dual;

Reference
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
http://www.psoug.org/reference/date_func.html

Command for describe table

SQL Server

Syntax:

SP_HELP <tablename>

SP_COLUMNS <tablename>

Oracle
Describe an Oracle Table, View, Synonym, package or Function.
Note: that because this is a SQL*Plus command you don't need to terminate it with a semicolon.

Syntax:

DESC table

DESC view

DESC synonym

DESC function

DESC package