Convert a row into a column

SELECT
SUBSTR('GREAT',a,1) GREAT
FROM
(
SELECT
ROWNUM a
FROM
DUAL
CONNECT BY
LEVEL <= LENGTH('GREAT')
);

Output :-

G
R
E
A
T

Prepend a Number with 0

Here's an interesting Script :-

DROP TABLE AX;

CREATE TABLE AX ( COL NUMBER );

INSERT INTO AX ( SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 10 );

SELECT TO_CHAR(COL,'00') FROM AX;

TO_
---
01
02
03
04
05
06
07
08
09

9 rows selected.

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

Can a Function return more than one value ?

This is a popular interview question that I have been asked ( & I have asked in turn ) from time immemorial.

A Function can return more than one value in PL/SQL.

We can do it in two ways :-

1. Use OUT Parameters in the Function :-

CREATE OR REPLACE FUNCTION FN_BAD ( RETVAL OUT NUMBER )
RETURN INTEGER
AS
BEGIN
RETVAL:= 200;
RETURN 100;
END;
/

SELECT FN_BAD FROM DUAL;

ORA-06553: PLS-306: wrong number or types of arguments in call to 'FN_BAD'

SET SERVEROUTPUT ON;

DECLARE
ln_BAD NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(FN_BAD(ln_BAD));
DBMS_OUTPUT.PUT_LINE(ln_BAD);
END;
/


100
200

PL/SQL procedure successfully completed.

Well, this is bad coding....but it works ! I just tried it for fun :)

2. Use a PipleLined Function. A Piplined Function needs to have an assosicated Collection Type.

How to append '0' to single Digit Numbers

DROP TABLE AX;

CREATE TABLE AX ( COL NUMBER );

INSERT INTO AX ( SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 11 );

SELECT TO_CHAR(COL,'00') FROM AX;

TO_
---
01
02
03
04
05
06
07
08
09
10

9 rows selected.

SQL> SELECT TO_NUMBER(TO_CHAR(COL,'00')) NUM FROM AX;

NUM
----------
1
2
3
4
5
6
7
8
9

9 rows selected.

A Function that returns a Table

This is an Example of a Pipelined function that returns a Table of Results :-


DROP TYPE TYP_NAME_AND_AGES;

DROP TYPE OBJ_NAME_AND_AGES

DROP FUNCTION FN_NAME_AND_AGES;

DROP TABLE TBL_NAME_AND_AGES;

CREATE TABLE TBL_NAME_AND_AGES
(
NAME VARCHAR2(10),
AGE NUMBER
);

INSERT INTO TBL_NAME_AND_AGES VALUES ('SANDEEP',20);
INSERT INTO TBL_NAME_AND_AGES VALUES ('SCOTT',21);
INSERT INTO TBL_NAME_AND_AGES VALUES ('TIGER',22);

COMMIT;

CREATE OR REPLACE TYPE TYP_NAME_AND_AGES AS OBJECT
(
NAME VARCHAR2(10),
AGE NUMBER
);

CREATE OR REPLACE TYPE OBJ_NAME_AND_AGES AS TABLE OF TYP_NAME_AND_AGES;

CREATE OR REPLACE FUNCTION FN_NAME_AND_AGES
RETURN OBJ_NAME_AND_AGES
PIPELINED
AS
BEGIN

FOR rec IN ( SELECT * FROM TBL_NAME_AND_AGES )
LOOP
PIPE ROW ( TYP_NAME_AND_AGES( rec.NAME, rec.AGE ) );
END LOOP;

END FN_NAME_AND_AGES;

SELECT * FROM TABLE(FN_NAME_AND_AGES);

NAME AGE
---------- ----------
SANDEEP 20
SCOTT 21
TIGER 22