SQL Snippets

December 19, 2013 by Michael

Some useful SQL Snippets:

Oracle

Create a date without any formatting hassle (like to_date):

SELECT DATE'2014-01-01' FROM dual;

Extract values as numbers from a date object

SELECT EXTRACT (YEAR FROM DATE'2013-05-06') FROM dual;
SELECT EXTRACT (MONTH FROM DATE'2013-05-06') FROM dual;
SELECT EXTRACT (DAY FROM DATE'2013-05-06') FROM dual;

Conversion of geographical coordinates into different coordinate systems:

SELECT srid,cs_name FROM cs_srs WHERE srid IN (82027, 82032, 8307)
 
-- Von GK4 nach GK3
SELECT sdo_cs.transform(
  SDO_GEOMETRY(2001, 82032, SDO_POINT_TYPE(4465656.81, 5338112.31, NULL), NULL, NULL ), -- Geometry in GK4
  82027                                                                                 -- Zielkoordinatensystem
) FROM dual;
 
-- und zurück
SELECT sdo_cs.transform(
  SDO_GEOMETRY(2001, 82027, SDO_POINT_TYPE(3688714.69, 5341125.2, NULL), NULL, NULL ), -- Geometry in GK3
  82032                                                                                -- Zielkoordinatensystem
) FROM dual;
 
-- und wenn wir schon dabei sind nach WSG84 (google)
SELECT sdo_cs.transform(
  SDO_GEOMETRY(2001, 82027, SDO_POINT_TYPE(3688714.69, 5341125.2, NULL), NULL, NULL ), -- Geometry in GK3
  8307                                                                                -- Zielkoordinatensystem
) FROM dual;

Select the tables that have a foreign key on a given table:

SELECT r.table_name, r.constraint_name
FROM all_constraints s
JOIN all_constraints r ON r.constraint_type = 'R' AND r.r_constraint_name = s.constraint_name
WHERE LOWER(s.table_name) = '<table_name>';

Last update: 2014/01/17

See also:

Git Snippets and Maven Snippets.

No comments yet

Post a Comment

Your email is never published nor shared. Required fields are marked *