Create db link without editting tnsnames.ora

Often database link is the best way to transport rows, current values of sequences or to know differences between rows in tables in remote databases: developing, test, production, etc

To create db link you need a 'create database link' privilege. As a database developer, i always ask DBA to grant this privilege when he create my developing schemas (with connect, resource and create view privileges).

When i want to access remote database with db link i need to add it's description to a tnsnames.ora file. As a developer i have to ask DBA to do it: i don't have an access to tnsnames.ora in database server. Sometimes it can takes hours.

When i plan to create db link, run some query and drop it, i create it this way:

SQL> CREATE DATABASE LINK xe_myrudw
  2    CONNECT TO hr IDENTIFIED BY hr USING '
  3    (DESCRIPTION =
  4      (ADDRESS = (PROTOCOL = TCP)(HOST = myrudw-ccfz91j.r2.money.ge.com)(PORT = 1521))
  5      (CONNECT_DATA =
  6        (SERVER = DEDICATED)
  7        (SERVICE_NAME = XE)
  8      )
  9    )
 10  ';

Database link created.

SQL> SELECT 1 a FROM DUAL@XE_MYRUDW;

         A
----------
         1

without adding connect string to tnsnames.ora.

Delete all data from schema

I use this

WITH const AS
  (SELECT rconst.table_name,
    rconst.constraint_name
     FROM user_constraints rconst,
    user_constraints pconst
    WHERE rconst.constraint_type = 'R'
  AND rconst.r_constraint_name   = pconst.constraint_name
  AND pconst.constraint_type     = 'P'  
  )
SELECT 'ALTER TABLE '||const.table_name||' DISABLE CONSTRAINT '||const.constraint_name||';' rnme FROM const UNION ALL
SELECT 'TRUNCATE TABLE '||ut.table_name||';' FROM user_tables ut UNION ALL 
SELECT 'ALTER TABLE ' ||const.table_name ||' ENABLE CONSTRAINT ' ||const.constraint_name ||';' rnme FROM const;

to generate script to clean schema.

• All rows from all tables will be removed.
• You can NOT rollback this script even if you replace "truncate" with "delete" (alter statement permorms commit). But you can use flashback to query truncated data.
• You can write a pl\sql block with loop, using this select as a cursor and execute immediate command.

concatenate, group by

A hour ago i read post "String Aggregation Techniques".
I'm working with Oracle 10G, but i have never used COLLECT to concatenate

Look, it is pretty simple, don't require any user-defined aggregate function, types, analytic functions.

SQL> SELECT dept.DEPARTMENT_NAME DEP,
      COLLECT (emp.LAST_NAME) LAST_NAMES
       FROM departments dept,
            employees emp
      WHERE dept.DEPARTMENT_ID = emp.DEPARTMENT_ID
    GROUP BY dept.DEPARTMENT_NAME;

DEP                            LAST_NAMES
------------------------------ -------------------------------------------------

Administration                 SYSTPdOAhPUUuTY2R3kKsEORiAg==('Whalen')
Marketing                      SYSTPdOAhPUUuTY2R3kKsEORiAg==('Hartstein', 'Fay')

Of course if need to write a query for some report in UI or for some data extract process you shoud use another technique, but if you use 10g and need to see or show to youre colleagues same concatenated data you can use this. Without any preparation, creating new objects and so on. Or, if you are 11g R2 user, then you just can use LISTAGG analytic function.

Select from data dictionary

I often use this

SQL> SELECT VIEW_NAME FROM ALL_VIEWS
  2  WHERE VIEW_NAME LIKE '%K-E-Y-W-O-R-D%';

to find from what system or user view i can select samething what i need: objects, privilegies, users, APEX pages or processes.

A COMMIT statements after DDL

You don't need to commit before or after you run ddl. Every ddl statements runs as this pseudocode:

BEGIN
   COMMIT;
   YOURE-DDL-OPERATOR;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;

is date function in Oracle

Sametimes i use this function to know, is a type of variable or field is date or not.
The function is very simple: i just try to convert variable to date, and if i can then variable is date.

SQL> CREATE OR REPLACE
CREATE OR REPLACE
FUNCTION IS_DATE
    (
      a_date IN VARCHAR2)
    RETURN VARCHAR2                               IS
    v_result      VARCHAR2 (1 CHAR) := 'Y';
    not_date    EXCEPTION;
    non_numeric EXCEPTION;
    v_date DATE;
    PRAGMA EXCEPTION_INIT( not_date ,    -1861);
    PRAGMA EXCEPTION_INIT( non_numeric , -1858);
  BEGIN
    BEGIN
       SELECT TO_DATE(a_date) INTO v_date FROM DUAL;
    EXCEPTION
    WHEN not_date THEN
      v_result := 'N';
    WHEN non_numeric THEN
      v_result := 'N';
    END;
    RETURN v_result;
  END IS_DATE;