Oracle tricky queries

How to write a CLOB with a length > 40K out to a file?

There is a database limit which only allows at the most 32760 characters to be written at one time.
Any data greater than that size will have to be written in chunks.

Another database limit (with databases version less than 10g) requires a "new line" to be issued
for each 32K of data written to a file.

The following illustrates two examples that write out 84K of data.

The first example can be used within a 9i or 10g database and illustrates using the "new line" for
each 32K of data.

The samples require that you have created the directory alias named 'ORAFILES_OUTPUT' and reference an existing directory with write privileges. 

Sample1

Main Procedure
create or replace PROCEDURE clob_to_file (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN CLOB) IS
 l_output UTL_FILE.file_type;
 l_amt NUMBER DEFAULT 32000;
 l_offset NUMBER DEFAULT 1;
 l_length NUMBER DEFAULT NVL (DBMS_LOB.getlength (p_clob), 0);
 x varchar2(32760);
BEGIN
 l_output := UTL_FILE.fopen (p_dir, p_file, 'w', 32760);
 WHILE (l_offset < l_length) LOOP
  dbms_lob.read (p_clob, l_amt, l_offset, x);
  UTL_FILE.put (l_output, x);
  UTL_FILE.fflush (l_output);
  UTL_FILE.new_line (l_output);
  l_offset := l_offset + l_amt;
 END LOOP;
 UTL_FILE.fclose (l_output);
END clob_to_file;
/



Anonymous block to create the data and call the above procedure


DECLARE
 v_Clob varchar2(4000);
 t_Clob CLOB;
BEGIN
 dbms_lob.createtemporary(t_clob,true,dbms_lob.session);
 FOR i IN 0 .. 20 LOOP
  select RPAD (i, 4000, i) into v_clob from dual;
  dbms_lob.write(t_clob, 4000, ((4000 * i) + 1), v_clob);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(T_CLOB));
 clob_to_file ('ORAFILES_OUTPUT', 'clob_test9i.txt', t_clob);
END;
/

 Results of Sample1

After executing the above code, navigate to the directory location referenced by the directory alias and check for a file named clob_test9i.txt.
> ls -l clob_test9i.txt
-rw-r--r-- 84003 clob_test9i.txt

The total data is 84000, the output file is 84003. This shows there were 3 writes to the file and
the 3 extra characters are due to the NEW_LINE comma

Sample 2 - Main Procedure

Open a file in binary mode is new to the 10g database version and allows the data to be written in
RAW form and eliminates the need for the "new line" as shown in the previous example.


create or replace PROCEDURE clob_to_file (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN CLOB) IS
 l_output UTL_FILE.file_type;
 l_amt NUMBER DEFAULT 32000;
 l_offset NUMBER DEFAULT 1;
 l_length NUMBER DEFAULT NVL (DBMS_LOB.getlength (p_clob), 0);
 x varchar2(32760);
BEGIN
 l_output := UTL_FILE.fopen (p_dir, p_file, 'wb', 32760);
 WHILE (l_offset < l_length) LOOP
  dbms_lob.read (p_clob, l_amt, l_offset, x);
  UTL_FILE.PUT_raw(l_output, utl_raw.cast_to_raw(x), TRUE);
  UTL_FILE.fflush (l_output);
  l_offset := l_offset + l_amt;
 END LOOP;
 UTL_FILE.fclose (l_output);
END clob_to_file;
/


Anonymous block to create the data and call the above procedure



DECLARE
 v_Clob varchar2(4000);
 t_Clob CLOB;
BEGIN
 dbms_lob.createtemporary(t_clob,true,dbms_lob.session);
 FOR i IN 0 .. 20 LOOP
  select RPAD (i, 4000, i) into v_clob from dual;
  dbms_lob.write(t_clob, 4000, ((4000 * i) + 1), v_clob);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(T_CLOB));
 clob_to_file ('ORAFILES_OUTPUT', 'clob_test10.txt', t_clob);
END;
/
RESULTS
After executing the above code, navigate to the directory location referenced by the directory alias and check for a file named clob_test10.txt.
> ls -l clob_test10.txt
-rw-r--r-- 84000 clob_test10.txt

Since there are no "new line" commands added to the file, the database written is exactly the same
as the data originally created. Essentially, no extra character

Transpose of oracle columns 

How to achieve data of the table in transpose format 

Original table data 

SELECT Column1, Column2, Column3, Column4 FROM Temp;

	Column1	Column2	Column3	Column4
1	1	ABC	PQR	1-Jan-13
2	2	DEF	XYZ	1-Feb-13

Required data # Column to Row Transpose

	Column_Name	Column_Value
1	Column1	1
2	Column2	ABC
3	Column3	PQR
4	Column4	1-Jan-13
5	Column1	1
6	Column2	DEF
7	Column3	XYZ
8	Column4	1-Feb-13

Query # 

SELECT

  xmlpvr.attr COLUMN_NAME,

  xmlpvr.val COLUMN_VALUE

  FROM xmltable ('ROWSET/ROW/*' passing dbms_xmlgen.getxmltype(

  'SELECT

  PVR.CREATION_DATE,

  PVR.ASSIGNMENT_ID,

  PVR.EFFECTIVE_START_DATE,

  PVR.EFFECTIVE_END_DATE,

  PVR.SUPERVISOR_ID 

FROM PER_ALL_PEOPLE_F PVA,

  PER_ALL_ASSIGNMENTS_F PVR

WHERE PVA.PERSON_ID = PVR.PERSON_ID

  AND PVR.ASSIGNMENT_ID = '||:P_ASSIGNMENT_ID ||'

  AND PVA.EFFECTIVE_START_DATE BETWEEN PVR.EFFECTIVE_START_DATE AND PVR.EFFECTIVE_END_DATE

  AND TRUNC(PVR.CREATION_DATE) BETWEEN '''||:p_start_date||''' AND '''||:p_end_date||''''

  ) columns attr VARCHAR2(4000) path 'name()', val VARCHAR2(4000) path 'data(.)' ) xmlpvr;

How to retrieve/assign  system timestamp in Oracle 

DECLARE
a   DATE;
b   TIMESTAMP WITH TIME ZONE := systimestamp;
begin
a := b;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;

Result 

09-JAN-08
09-JAN-08 02.37.02.339176 PM +01:00


How to get 10 sequential dates using a single sql query.

SELECT (SYSDATE) +level-1 each_day FROM DUAL CONNECT BY LEVEL <= 10

0/p:

18-DEC-13

19-DEC-13

20-DEC-13

21-DEC-13

22-DEC-13

23-DEC-13

24-DEC-13

25-DEC-13

26-DEC-13

27-DEC-13

        Alternate query ...

select to_date(trunc(sysdate),'dd-mon-yyyy') + rownum -1

      from all_objects

0/p:

18-DEC-13

19-DEC-13

20-DEC-13

21-DEC-13

22-DEC-13

23-DEC-13

24-DEC-13

25-DEC-13

26-DEC-13

27-DEC-13

28-DEC-13

29-DEC-13

30-DEC-13

Calculate age from HIREDATE to system date of system

SELECT HIREDATE||'        '||SYSDATE||'       ' ||

TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) ||' YEARS '||

TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE))-(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)*12))||

'MONTHS' AS "AGE  "  FROM EMP;