Oracle PL/SQL Cheat Sheet

-- CREATE LIKE TABLE WITH DATA
CREATE TABLE BISKINLER_TBL_NEW AS
    SELECT * FROM BISKINLER_TBL_OLD;


-- DELETE ALL DATA FROM TABLE
TRUNCATE TABLE BISKINLER_TBL_OLD;

-- COPY DATA FROM OLD TABLE -> NEW TABLE
INSERT INTO BISKINLER_TBL_NEW
    SELECT * FROM BISKINLER_TBL_OLD;

-- SIMPLE FUNCTION CALL
SELECT FUNCTION_NAME ('VARHCHAR PARAM', 10) FROM DUAL;


-- SIMPLE PROCEDURE CALL
BEGIN
    PROCEDURE_NAME();
END;


-- CHANGE DATABASE LANGUAGE - PL/SQL
EXECUTE IMMEDIATE ('ALTER SESSION SET NLS_LANGUAGE = "AMERICAN"');

-- CHANGE DATABASE LANGUAGE - SQL
ALTER SESSION SET NLS_LANGUAGE = "AMERICAN";

-- LEARN ORACLE/ PL/SQL.. VERSION
SELECT * FROM v$version;

-- SEARCH IN YOUR SOURCE CODE (PROC., FUNC., PACKAGE, )
-- EX: SEARCH FOR COMMENTS ADDED BY AFBISKINLER
  SELECT *
    FROM user_source C
   WHERE C.TYPE = 'PACKAGE' AND C.TEXT LIKE ('%--%AFBISKINLER%')
ORDER BY C.NAME;

-- 
SELECT TO_DATE ('2123 12 12', 'YYYY MM DD') FROM dual;

TO_DATE('21231212','YYYYMMDD')
------------------------------
30.12.2123                   
1 row selected.

-- 
SELECT TO_CHAR (SYSDATE, 'YYYY MM DD') FROM dual;
TO_CHAR(SYSDATE,'YYYYMMDD')
---------------------------
2012 02 17                 
1 row selected.


---------------------------------------
-- CREATE AND DROP SEQUENCE

-- DROP SEQUENCE
DROP SEQUENCE BISKINLER_SAMPLE_SEQ;

-- CREATE SEQUENCE
CREATE SEQUENCE BISKINLER_SAMPLE_SEQ
    START WITH 1
    MAXVALUE 999999999999999999999999999
    MINVALUE 1
    NOCYCLE
    CACHE 20
    NOORDER;

---------------------------------------
SELECT 'AUTHENTICATED_IDENTITY:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'AUTHENTICATED_IDENTITY')  AS SYSVALUE   FROM DUAL UNION 
SELECT 'CURRENT_USER:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'CURRENT_USER')    FROM DUAL UNION 
SELECT 'DB_NAME:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'DB_NAME')    FROM DUAL UNION 
SELECT 'DB_UNIQUE_NAME:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'DB_UNIQUE_NAME')    FROM DUAL UNION 
SELECT 'HOST:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'HOST')    FROM DUAL UNION 
SELECT 'IDENTIFICATION_TYPE:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'IDENTIFICATION_TYPE')    FROM DUAL UNION 
SELECT 'INSTANCE_NAME:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'INSTANCE_NAME')    FROM DUAL UNION 
SELECT 'IP_ADDRESS:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'IP_ADDRESS')    FROM DUAL UNION 
SELECT 'LANG:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'LANG')    FROM DUAL UNION 
SELECT 'LANGUAGE:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'LANGUAGE')    FROM DUAL UNION 
SELECT 'NETWORK_PROTOCOL:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL')    FROM DUAL UNION 
SELECT 'NLS_DATE_LANGUAGE:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE')    FROM DUAL UNION 
SELECT 'NLS_SORT:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'NLS_SORT')    FROM DUAL UNION 
SELECT 'OS_USER:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'OS_USER')    FROM DUAL UNION 
SELECT 'SERVER_HOST:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'SERVER_HOST')    FROM DUAL UNION 
SELECT 'SERVICE_NAME:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'SERVICE_NAME')    FROM DUAL UNION 
SELECT 'SESSION_USER:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'SESSION_USER')    FROM DUAL UNION 
SELECT 'TERMINAL:' as SYSCOLUMN , SYS_CONTEXT ('USERENV', 'TERMINAL')    FROM DUAL ; 


|-----------------------------------------------------------------|
|SYSCOLUMN                 |   SYSVALUE                           |
|-----------------------------------------------------------------|
|AUTHENTICATED_IDENTITY:   |   ABC                                |
|CURRENT_USER:             |   ABC                                |
|DB_NAME:                  |   ABC                                |
|DB_UNIQUE_NAME:           |   ABC                                |
|HOST:                     |   ABC                                |
|IDENTIFICATION_TYPE:      |   LOCAL                              |
|INSTANCE_NAME:            |   ABC                                |
|IP_ADDRESS:               |   ABC                                |
|LANG:                     |   US                                 |
|LANGUAGE:                 |   AMERICAN_AMERICA.WE8ISO8859P9      |
|NETWORK_PROTOCOL:         |   tcp                                |
|NLS_DATE_LANGUAGE:        |   AMERICAN                           |
|NLS_SORT:                 |   BINARY                             |
|OS_USER:                  |   ABC                                |
|SERVER_HOST:              |   ABC                                |
|SERVICE_NAME:             |   ABC                                |
|SESSION_USER:             |   ABC                                |
|TERMINAL:                 |   ABC                                |
|-----------------------------------------------------------------|
---------------------------------------------------------
DECLARE
   dummy NUMBER;
BEGIN
   SELECT count(*) INTO dummy FROM dual;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;
   WHEN OTHERS THEN
      NULL;
END;


---------------------------------------------------------
SELECT 
    CASE D.dummy 
    WHEN 'Y' THEN 
        'YES' 
    WHEN 'X' THEN 
        'NO' 
    ELSE 
        'OTHER' 
    END CASE
  FROM DUAL d;

---------------------------------------------------------

---------------------------------------------------------

---------------------------------------------------------

---------------------------------------------------------

---------------------------------------------------------

---------------------------------------------------------