Oracle Row Generator techniques :-

The link has tips on how to generate rows urgently :-

http://orafaq.com/wiki/Oracle_Row_Generator_Techniques

Mutltiply two numbers using Exponential Functions

I have never used Exponential Functions - I found this in OTN Forums & this was one of the best uses of Exponential functions that I ever found :-

1. Pick a query that returns two numbers in two rows :-

SELECT 2 NUM FROM dual
UNION
SELECT 3 NUM FROM dual

This returns 2 & 3.

2. Multiply using the query :-

SELECT
EXP (SUM (LN (num))) product
FROM (SELECT 2 num
FROM DUAL
UNION
SELECT 3 num
FROM DUAL);

This is simple Exponential Maths :- logE(A) + logE(B) = logE(A+B). logE raised to the power of e ( facilitated by the EXP Function ) cancles out logE, leaving simply the product AB.

How to simulate NO_ROWS, 1 Row and many rows using a single Select

I picked these from the OTN Forums - the queries are surprisingly simple :-

SELECT 'SANDEEP' FROM DUAL WHERE 1 = 2; -- NO_ROWS

SELECT 'SANDEEP' FROM DUAL; -- 1 ROW

SELECT 'SANDEEP' FROM DUAL CONNECT BY LEVEL < 11; -- 10 ROWS

How to find the Next Saturday ?

Here's the query :-

SELECT NEXT_DAY(SYSDATE,'SATURDAY') FROM DUAL;

How to print Dates and Days for a Month

Here's the query :-

SELECT TO_DATE ('01/01/2006', 'dd/mm/yyyy') + ROWNUM - 1 MONTH_DATE,
TO_CHAR (TO_DATE ('01/01/2006', 'dd/mm/yyyy') + ROWNUM - 1, 'day') MONTH_DAY
FROM
ALL_OBJECTS
WHERE
ROWNUM <= 31;

The only hitch is the 31 - I guess I'll figure out a workaround for that too :)

Display Month names from JAN to DEC

How to display the names of Months from JAN to DEC ? This is a beautiful query that I found in Forums :-

SELECT
NUM,
to_char( to_date(NUM,'mm'),'MON','nls_date_language=american') MONTH
FROM
(
SELECT level NUM from dual connect by level <= 12);

Name of the Current User

I just noticed in a Function in Sybase that returns the name of the current Database user :-

select suser_name()

------------------------------
test_user

The equivalent of this in Oracle is :-

SELECT USER FROM DUAL;
------------------------------
test_user