Thanks for the question, Praveen.

Asked: February 14, 2022 - 11:22 am UTC

Last updated: February 17, 2022 - 2:59 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi Tom,

I'm trying to create the CSV file using DBMS_LOB pkg but i'm getting an below error.
Could you please help us on this?

I'hve added the code in live sql.

Note: we can create the CSV file using UTL pkg but we don;t have access for directories.

Please do the needful.

DECLARE
CURSOR C1 IS
 SELECT OWNER||','||OBJECT_NAME||','||OBJECT_ID||','||OBJECT_TYPE REC_TAB
 FROM ALL_OBJECTS
 WHERE ROWNUM<=10;
L_BLOB BLOB DEFAULT EMPTY_BLOB(); 
TMP_BFILE BFILE:=NULL; 
DEST_OFFSET INTEGER:=1; 
SRC_OFFSET INTEGER:=1; 
L_WARNING INTEGER;
LANG_CONTEXT INTEGER;
BLOB_CSID NUMBER;
OFFSET INTEGER;
BUFFER VARCHAR2(3267);
L_CLOB CLOB DEFAULT EMPTY_CLOB();
BEGIN
 DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE); 
 FOR I IN C1 LOOP
  L_CLOB:=L_CLOB||I.REC_TAB||CHR(13);
 END LOOP;
 
  SYS.DBMS_LOB.CONVERTTOBLOB(
    DEST_LOB => L_BLOB,
    SRC_CLOB => L_CLOB,
    AMOUNT => DBMS_LOB.GETLENGTH(L_CLOB),
    DEST_OFFSET => DEST_OFFSET,
    SRC_OFFSET => SRC_OFFSET,
    BLOB_CSID => BLOB_CSID,
    LANG_CONTEXT => LANG_CONTEXT,
    WARNING => L_WARNING
  );
 
END;

Regards,
Praveen

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/m2k33xuy1jdrx4nk7w4cwp8kh

and Chris said...

There are several issues here:

- Pass an initial value for the LANG_CONTENT & BLOB_CSID (e.g. DBMS_LOB.default_lang_ctx & DBMS_LOB.default_csid)
- Initialize the BLOB using createtemporary as with the CLOB

But there's a much bigger issue:

You can't use BFILES to write to the file system!

They're pointers to files; you can read the files but not create them. You'll have to use some other method to write the data.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Create CSV file without using UTL FILE pkg

Praveen, February 15, 2022 - 4:43 pm UTC

Could you please suggest the way to create the file?

February 17, 2022 - 2:59 am UTC

You can use the scheduler

https://connor-mcdonald.com/2018/06/20/the-death-of-utl_file/

Create CSV file without using UTL FILE pkg

Rajeshwaran Jeyabal, February 16, 2022 - 2:13 pm UTC

two possible ways from SQL*Plus and SQLCL utility.

from SQL*Plus 12.2 and above

demo@XEPDB1> set markup CSV on quote off
demo@XEPDB1> select * from dept;

DEPTNO,DNAME,LOC
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

or from SQLCL we can use either of these ways.

demo@XEPDB1> select /*csv*/ * from dept;
"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

demo@XEPDB1> set sqlformat csv
demo@XEPDB1> select * from dept;
"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

February 17, 2022 - 2:58 am UTC

good input

Use etl tool

A reader, February 27, 2022 - 3:07 am UTC

Use PENTAHO ETL tool, which is open source...give just connections for dumping data to target system....Any large volume of data can easily done by etl tools....no need to write PLSQL code for confusing...

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Create CSV file without using UTL FILE pkg

Create CSV file without using UTL FILE pkg

Use etl tool

More to Explore

PL/SQL demos

PL/SQL docs

How to crate the CSV file using DBMS_LOB Package

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Create CSV file without using UTL FILE pkg

Create CSV file without using UTL FILE pkg

Use etl tool

More to Explore

PL/SQL demos

PL/SQL docs