ALTERNATIVE QUOTE (q) Operator

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and choose your own quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [], {}, (), or <>.
EX:   select q'<Oracle's quote operator>' from dual;
         select q'#Oracle's quote operator#' from dual;
         select q'(Oracle's quote operator)' from dual;
         select dname ||q'{ it's assigned manager id :}'|| mgr from emp,dept where emp.deptno=dept.deptno

DBMS_RANDOM

This package is very useful to generate random number as well as  random date and text values.

NUMBER:


EXAMPLE: 


SELECT DBMS_RANDOM.VALUE FROM DUAL;


SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;

TEXT:


EXAMPLE:

SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;


Q : Generate 100 random strings of 15 characters by using DUAL table.

 SELECT DBMS_RANDOM.STRING('X',15) FROM DUAL CONNECT BY ROWNUM<101;


DATE:



SELECT TO_CHAR(TO_DATE('01/01/12','mm/dd/yy'),'J')
FROM DUAL;


NOTE :Take the low_value from the above query and use it in the below query to generate random dates.



SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2455928,2455928+364)),'J')
 FROM DUAL;