Utility / Math related queries

Utility / Math related queries

Convert number to words
More info: Converting number into words in Oracle


SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;

Output:
one thousand five hundred twenty-six


Find string in package source code
Below query will search for string ‘FOO_SOMETHING’ in all package source. This query comes handy when you want to find a particular procedure or function call from all the source code.

--search a string foo_something in package source code
SELECT *
FROM dba_source
WHERE UPPER (text) LIKE '%FOO_SOMETHING%'
AND owner = 'USER_NAME';


Convert Comma Separated Values into Table
The query can come quite handy when you have comma separated data string that you need to convert into table so that you can use other SQL queries like IN or NOT IN. Here we are converting ‘AA,BB,CC,DD,EE,FF’ string to table containing AA, BB, CC etc. as each row. Once you have this table you can join it with other table to quickly do some useful stuffs.

WITH csv
AS (SELECT 'AA,BB,CC,DD,EE,FF'
AS csvdata
FROM DUAL)
SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;


Find the last record from a table
This ones straight forward. Use this when your table does not have primary key or you cannot be sure if record having max primary key is the latest one.

SELECT *
FROM employees
WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

(OR)

SELECT * FROM employees
MINUS
SELECT *
FROM employees
WHERE ROWNUM < (SELECT COUNT (*) FROM employees);


Row Data Multiplication in Oracle
This query use some tricky math functions to multiply values from each row. Read below article for more details.
More info: Row Data Multiplication In Oracle

WITH tbl
AS (SELECT -2 num FROM DUAL
UNION
SELECT -3 num FROM DUAL
UNION
SELECT -4 num FROM DUAL),
sign_val
AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
FROM tbl
WHERE num < 0)
SELECT EXP (SUM (LN (ABS (num)))) * val
FROM tbl, sign_val
GROUP BY val;


Generating Random Data In Oracle
You might want to generate some random data to quickly insert in table for testing. Below query help you do that. Read this article for more details.
More info: Random Data in Oracle

SELECT LEVEL empl_id,
MOD (ROWNUM, 50000) dept_id,
TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F') gender,
TO_DATE (
ROUND (DBMS_RANDOM.VALUE (1, 28))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1, 12))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
'DD-MM-YYYY')
dob,
DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
FROM DUAL
CONNECT BY LEVEL < 10000;


Random number generator in Oracle
Plain old random number generator in Oracle. This ones generate a random number between 0 and 100. Change the multiplier to number that you want to set limit for.

--generate random number between 0 and 100
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;


Check if table contains any data
This one can be written in multiple ways. You can create count(*) on a table to know number of rows. But this query is more efficient given the fact that we are only interested in knowing if table has any data.

SELECT 1
FROM TABLE_NAME
WHERE ROWNUM = 1;