Oracle SQL CheatSheet

CREATE USER "APPDEV" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT UNLIMITED TABLESPACE TO "APPDEV";
GRANT "CONNECT" TO "APPDEV";
GRANT "RESOURCE" TO "APPDEV";

SQLLDR username/password CONTROL=filename DATA=filename

LOAD DATA
CHARACTERSET UTF8
INFILE *
REPLACE INTO TABLE LOADER_TEST
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS (
USR_ID         INTEGER EXTERNAL,
USR_NAME       CHAR(50),
USR_LNK_NAME   CHAR(50),
USR_LNK_ORDER  INTEGER EXTERNAL
)

EXP scott/tiger@instance FILE=geo_entities.dmp TABLES=geo_entities
 
IMP sysman/password FILE=/path/TO/geo_entities.dmp FROMUSE=scott TOUSER=appdev IGNORE=Y INDEXES=N TABLES=geo_entities

ALTER TABLE TABLE_NAME ADD (column_name  NUMBER);

ALTER TABLE geo_entities MODIFY meta_name VARCHAR(255)

ALTER TABLE TABLE_NAME DROP COLUMN column_name;

ALTER TABLE TABLE_NAME ADD CONSTRAINT table_name_col_pk PRIMARY KEY(column_name);

SELECT * FROM TABLE_NAME WHERE ROWNUM <= 100

CREATE SEQUENCE seq_table_name_pk INCREMENT BY 1 START WITH 10000;
 
INSERT INTO TABLE_NAME (pid, en_name) 
SELECT seq_table_name_pk.NEXTVAL, en_name FROM other_table

SELECT concat('hello', 'rupert') FROM DUAL;
SELECT 'hello' || 'rupert' FROM DUAL;

UPDATE poi_temp pt
SET geom = (SELECT geom FROM poi_app WHERE poi_id = pt.poi_id)
WHERE EXISTS (SELECT 1 FROM poi_app WHERE poi_id = pt.poi_id)
 
UPDATE poi_app pa
SET (long_900913, lat_900913) = (SELECT pg.long_900913, pg.lat_900913 FROM poi_app_900913 pg WHERE pa.poi_id = pg.poi_id)
WHERE EXISTS (SELECT 1 FROM poi_app_900913 pg WHERE pa.poi_id = pg.poi_id)

SDO_GEOMETRY{
    SDO_GTYPE, - GeometryType: D00T
    D: Dimension (2: 2d, 3: 3d, 4:4d)
        T:GeometryType(
        0 - unknown
        1 - point
        2 - line
        3 - polygon
        4 - collection
        5 - multipoint
        6 - multiline
        7 - multipolygon
    )
    SDO_SRID,
    SDO_POINT, - NULL for line, polygon, etc.
    SDO_ELEM_INFO,
    SDO_ORDINATES
}

THE_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 3, 1), SDO_ORDINATE_ARRAY(116
.281578, 39.854501, 116.281491, 39.853828, 116.281236, 39.853181, 116.280821, ....39
.855174, 116.281578, 39.854501))
 
THE_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
 
SQL> SELECT g.the_geom.sdo_gtype FROM geo_entities g WHERE rownum <= 1;
 
THE_GEOM.SDO_GTYPE
------------------
3

INSERT INTO USER_SDO_GEOM_METADATA VALUES
(
    'table_name', -- TABLE_NAME
    'geom', -- COLUMN_NAME
    SDO_DIM_ARRAY -- DIMINFO attribute for storing dimension bounds, tolerance
    (
        SDO_DIM_ELEMENT
        (
            'LONGITUDE', -- DIMENSION NAME for first dimension
            -180, -- SDO_LB for the dimension
            180, -- SDO_UB for the dimension
            0.5 -- Tolerance of 0.5 meters
        ),
        SDO_DIM_ELEMENT
        (
            'LATITUDE', -- DIMENSION NAME for second dimension
            -90, -- SDO_LB for the dimension
            90, -- SDO_UB for the dimension
            0.5 -- Tolerance of 0.5 meters
        )
    ),
    4326 -- SRID value for specifying a geodetic coordinate system
);
 
CREATE INDEX idx_poi_app_geom ON poi_app(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
 
CREATE INDEX idx_poi_app_geom ON poi_app(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('LAYER_GTYPE=POINT')

SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(GEOM, 0.000000005) FROM TABLE_NAME

SELECT pc.postcode, au.authority_name, au.state, au.authority_type
FROM econ_authorities_valid_temp au, econ_postcodes pc
WHERE pc.POSTCODE = 3128
	  AND SDO_ANYINTERACT(
	  				  pc.GEOM,
	  				  au.GEOM
	  ) = 'TRUE

SELECT SDO_UTIL.GETVERTICES( GEOM ) FROM ECON_AUTHORITIES WHERE AUTHORITYID = 90009