Home

Welcome to Cursor2LOB!

Requirements

Cursor2LOB is designed to work with Oracle Database 11g Release 1 and up in any edition.
To successfully install Cursor2LOB your installation schema has to be granted the following system privilege:

• CREATE PROCEDURE

Additionally the EXECUTE object privilege on the following package is required:

• SYS.DBMS_LOB
• SYS.DBMS_TYPES
• SYS.UTL_FILE

By default, this privilege is already granted to PUBLIC so it may not be necessary to grant it explicitly.

Components

Cursor2LOB2 consists only of a single package cursor2lob.

To create the CSV data you use the function/procedure to_clob, to_blob or to_file. These routines write the data to a CLOB, BLOB or UTL_FILE object respectively. If if the provided LOB already contains something then the data gets appended.

The routines to_clob, to_blob and to_file are overloaded to make it easy to use them in SQL as well as PL/SQL.

Examples

The following examples work on the well-known SCOTT schema with the table DEPT and EMP.

Let's create a simple CLOB containing the data from the EMP table in standard CSV format:

select cursor2lob.to_clob(
         cursor(select * from emp e), /* Cursor */
         chr(10),                     /* Row Separator */
         ';',                         /* Column Separator */
         '"',                         /* Delimiter */
         1                            /* 1 -> First row includes column names */
       ) emp_csv
from   dual

The result is as follows:

EMP_CSV

EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO↵ 7369;SMITH;CLERK;7902;17.12. 1980;800;;20↵ 7499;ALLEN;SALESMAN;7698;20.02. 1981;1600;300;30↵ 7521;WARD;SALESMAN;7698;22.02. 1981;1250;500;30↵ 7566;JONES;MANAGER;7839;02.04. 1981;2975;;20↵ 7654;MARTIN;SALESMAN;7698;28.09. 1981;1250;1400;30↵ 7698;BLAKE;MANAGER;7839;01.05. 1981;2850;;30↵ 7782;CLARK;MANAGER;7839;09.06. 1981;2450;;10↵ 7788;SCOTT;ANALYST;7566;19.04. 1987;3000;;20↵ 7839;KING;PRESIDENT;;17.11. 1981;5000;;10↵ 7844;TURNER;SALESMAN;7698;08.09. 1981;1500;0;30↵ 7876;ADAMS;CLERK;7788;23.05. 1987;1100;;20↵ 7900;JAMES;CLERK;7698;03.12. 1981;950;;30↵ 7902;FORD;ANALYST;7566;03.12. 1981;3000;;20↵ 7934;MILLER;CLERK;7782;23.01. 1982;1300;;10↵

The query's result consists only one row with one column containing a CLOB.

Now let's create a CSV CLOB for each department:

select d.deptno, d.dname,
       cursor2lob.to_clob(
         cursor(select e.empno, e.ename, e.sal
                from   emp e
                where  e.deptno = d.deptno), /* Cursor */
         chr(10),                     /* Row Separator */
         ';',                         /* Column Separator */
         '"',                         /* Delimiter */
         0                            /* 0 -> No column names */
       ) emp_csv
from   dept d

The result is as follows:

EMP_CSV	DEPTNO	DNAME
10	ACCOUNTING	7782;CLARK;2450↵ 7839;KING;5000↵ 7934;MILLER;1300↵
20	RESEARCH	7369;SMITH;800↵ 7566;JONES;2975↵ 7788;SCOTT;3000↵ 7876;ADAMS;1100↵ 7902;FORD;3000↵
30	SALES	7499;ALLEN;1600↵ 7521;WARD;1250↵ 7654;MARTIN;1250↵ 7698;BLAKE;2850↵ 7844;TURNER;1500↵ 7900;JAMES;950↵
40	OPERATIONS	(null)

Generating the same data as a fixed-column format works this way:

select d.deptno, d.dname,
       cursor2lob.to_clob(
         cursor(select rpad(e.empno, 8, ' '),
                       rpad(e.ename, 10, ' '),
                       rpad(e.sal, 8, ' ')
                from   emp e
                where  e.deptno = d.deptno), /* Cursor */
         chr(10),                     /* Row Separator */
         null                         /* Column Separator */
       ) emp_csv
from   dept d

The result is as follows:

EMP_CSV	DEPTNO	DNAME
10	ACCOUNTING	7782••••CLARK•••••2450••••↵ 7839••••KING••••••5000••••↵ 7934••••MILLER••••1300••••↵
20	RESEARCH	7369••••SMITH•••••800•••••↵ 7566••••JONES•••••2975••••↵ 7788••••SCOTT•••••3000••••↵ 7876••••ADAMS•••••1100••••↵ 7902••••FORD••••••3000••••↵
30	SALES	7499••••ALLEN•••••1600••••↵ 7521••••WARD••••••1250••••↵ 7654••••MARTIN••••1250••••↵ 7698••••BLAKE•••••2850••••↵ 7844••••TURNER••••1500••••↵ 7900••••JAMES•••••950•••••↵
40	OPERATIONS	(null)

What about PL/SQL? The calls are nearly the same - the only difference is the creation of the cursor since the cursor() function is a SQL-only function. The first example in PL/SQL would look like this:

declare
  l_cursor sys_refcursor;
  l_clob  clob;
begin
  open l_cursor for select * from emp e;
  l_clob := cursor2lob.to_clob(
                  l_cursor, /* Cursor */
                  chr(10),  /* Row Separator */
                  ';',      /* Column Separator */
                  '"',      /* Delimiter */
                  1         /* 1 -> First row includes column names */
                );
end;

Limitations

Cursor2LOB fetches the data as VARCHAR2s or as CLOBs. If a column type is not of these two then an implicit conversion to VARCHAR2 is attempted using the default NLS and format mask settings of your session. If you want to get a specific format for your NUMBERs or DATEs etc. then you'll have to use the TO_CHAR function and provide a format mask.

It's not possible to fetch object types, nested tables or BLOBs as such because there is no reasonable implicit conversion of these types to a VARCHAR2. If you want to get those into your LOB, you'll have to convert them yourself to a VARCHAR2 or CLOB explicitly.

Project Members:

• Michael (admin)