|
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
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
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;
|
