How to generate an even/odd random number in Oracle SQL?

Question Level: Beginner/Intermediate

Question: Generate a random even (or odd) number in the range 1..100.

Odd/Even random number generation is fairly straightforward process if you understand the math behind a simple SQL expression:

SELECT 2*(ROUND(dbms_random.value(1,100)/2,0))   even_random,
       2*(ROUND(dbms_random.value(1,100)/2,0))+1 odd_random
FROM dual

Using the same approach we can generate a random number that is a multiple of 5 (or any other number):

SELECT 5*(ROUND(dbms_random.value(1,100)/5,0))   multiple5_random
FROM dual

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Replace clause for changing one’s password.

Starting with Oracle 11g, if you get the following error when changing your own password, it means that you have to include REPLACE clause in your ALTER USER command:

ALTER USER testuser IDENTIFIED BY testpassword;

Oracle: “ORA-28221: REPLACE not specified” 

Syntax with REPLACE clause:

ALTER USER testuser IDENTIFIED BY testpassword REPLACE oldpassword;

User altered.

If your password contains special characters, you may need to put it in quotations:

ALTER USER testuser IDENTIFIED BY "testp@ssw@rd" REPLACE oldpassword;

User altered.

As per Oracle documentation,

You can omit the REPLACE clause if you are setting your own password or you have the ALTER USER system privilege and you are changing another user’s password. However, unless you have the ALTER USER system privilege, you must always specify the REPLACE clause if a password complexity verification function has been enabled, either by running the UTLPWDMG.SQL script or by specifying such a function in the PASSWORD_VERIFY_FUNCTION parameter of a profile that has been assigned to the user.

Source: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4003.htm#SQLRF01103

An interview question that checks knowledge of Oracle SQL character functions.

Level: Beginner

Question: Find all employee names beginning with ‘S’ not using LIKE operator.

Method #1: INSTR function

SELECT ename
FROM emp
WHERE INSTR(ename, 'S')=1
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #2: Range Test

SELECT ename
FROM emp
WHERE ename>='S' AND ename<'T'
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #3: SUBSTR function

SELECT ename
FROM emp
WHERE SUBSTR(ename, 1, 1) = 'S'
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #4: ASCII function

SELECT ename
FROM emp
WHERE ASCII(ename)=ASCII('S')
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #5: SUBSTR functions

SELECT ename
FROM emp
WHERE 'S' || SUBSTR(ename, 2) = ename
/

--Result:

ENAME
-------
SMITH
SCOTT

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to DELETE all records from all tables in a schema?

This is rather an exercise than a practical task. Anyway, the approach presented below might be helpful in some practical situations.

Problem Level: Beginner/Intermediate

Step 1: Disable all foreign key constraints.

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='ENABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' DISABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

Step 2: DELETE from all tables

DECLARE
  CURSOR c IS
  SELECT table_name
  FROM user_tables;
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'DELETE FROM ' || v.table_name;
  END LOOP;
END;
/

Step 3: COMMIT or ROLLBACK

ROLLBACK; --you can commit here instead if needed

STEP 4: Enable all FOREIGN KEY Constraints

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='DISABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' ENABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to find all packaged procedures that have argument(s) of a specific data type?

This task is quite trivial if you know the right data dictionary to use.
Let’s say we need to see all procedures that has DATE arguments:

SELECT package_name, object_name AS procedure_name, argument_name, data_type
FROM user_arguments a
WHERE data_type='DATE'
  AND package_name IS NOT NULL
ORDER BY 1,2,3

How to Simulate a Global Constant in Oracle PL/SQL?

Oracle PL/SQL does not support global constants, i.e. constants defined on the schema level.
You can define one in a package, which is typically used when you need to refer to a constant. However, such constant definitions look bulky as you always need to reference package name as a prefix to the constant name.

Deterministic functions allow you to mimic the constant use without sacrificing the performance.

Let’s see an example where we will define a “CONSTANT” that carried the date formatting string (for TO_DATE conversions):

CREATE OR REPLACE FUNCTION date_format 
RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
    RETURN 'yyyy-mm-dd hh24:mi:ss';
END date_format;
/

Now, you can use this function as if it were a globally defined constant:

SET SERVEROUTPUT ON

DECLARE
   v_date DATE;
BEGIN
   v_date:=TO_DATE('2015-11-14', date_format);
   DBMS_OUTPUT.PUT_LINE(v_date);
END;
/
14-NOV-15

PL/SQL procedure successfully completed.

According to Oracle documentation, DETERMINISTIC is a “hint that helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result.”

Since our function does not have any parameters, the optimizer will always return the previous (i.e. CONSTANT) result.

Alternatively, you can use RESULT_CACHE option which was first introduced in Oracle 11g:

CREATE OR REPLACE FUNCTION date_format 
RETURN VARCHAR2 RESULT_CACHE
AS
BEGIN
 RETURN 'yyyy-mm-dd hh24:mi:ss';
END date_format;
/

The advantage of this method is the cached information can be reused by any session and dependencies are managed automatically.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

A function that converts a specially formatted character string to Oracle’s TIMESTAMP data type.

Problem: Convert a character string formatted ‘YYYY-MM-DD HH24:MI:SS.FF’ to TIMESTAMP. The string has to be validated before converting VARCHAR2 variable to TIMESTAMP.

The following function does the trick:

CREATE OR REPLACE FUNCTION TO_TS(p_ts_text VARCHAR2) RETURN TIMESTAMP
AS
BEGIN
    IF REGEXP_LIKE(p_ts_text, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}') THEN
        RETURN TO_TIMESTAMP(p_ts_text, 'YYYY-MM-DD HH24:MI:SS.FF');
    END IF;
    
    RAISE_APPLICATION_ERROR(-20012,'Invalid timestamp format is used');
END;

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to mimic MINUS ALL and INTERSECT ALL in Oracle

Some databases, such as PostgreSQL and Teradata, natively support “ALL” keyword with SET operators MINUS (EXCEPT) and INTERSECT. As of current version, Oracle does not support these features; however, a little trick can help emulating the missing functionality.

Let’s see an example. We want to see job titles from department #20 that don’t exist in department #30.

SELECT job
FROM emp
WHERE deptno=20
MINUS
SELECT job
FROM emp
WHERE deptno=30;

--Result:

JOB
---------
ANALYST

If Oracle supported MINUS ALL the result of the following SQL would be quite different:

SELECT job
FROM emp
WHERE deptno=20
MINUS ALL
SELECT job
FROM emp
WHERE deptno=30;

--Result (possible):
JOB
---------
ANALYST
ANALYST
CLERK

The following trick with Analytical function ROW_NUMBER helps with Oracle SQL solution:

SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=20
MINUS
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=30;

JOB               RN
--------- ----------
ANALYST            1
ANALYST            2
CLERK              2

If we ignore (or hide by selecting from the above result) RN column, we would get exactly what we need:

WITH x AS (
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=20
MINUS
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=30
)
SELECT job
FROM x;

JOB
---------
ANALYST
ANALYST
CLERK

The same trick works fine with mimicking INTERSECT ALL statement.
The rule is simple:

SELECT col1, col2, col3, ROW_NUMBER(PARTITION BY col1, col2, col3 ORDER BY 1) rn
FROM 
WHERE ...
INTERSECT
SELECT col1, col2, col3, ROW_NUMBER(PARTITION BY col1, col2, col3 ORDER BY 1) rn
FROM 
WHERE ...

Just make sure to list all selected columns in PARTITION BY clause.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.