Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Loading external CLOB data

Posted on 2007-10-01
8
Medium Priority
?
1,608 Views
Last Modified: 2013-12-07
Hey,

I need to load a 66,2 MB file with CLOB content into an Oracle table. I've created an external table to load the contents of this file.
I'm using the function utl_file.cast_to_raw to convert the contents of the file to hexadecimal format. I'm receiving an ORA-22835 error stating that the size of the datafile exceeds the 4000 byte limit. Does this mean that I need to split the file into 4000 byte chunks in order to load the CLOB content into the database. Or is there a more efficient way to this?  

Regards
0
Comment
Question by:Mus_Bak
6 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 19989888
I do not believe that UTL_FILE is intended to do this JOB.

Try to use DBMS_LOB package. There are more then enough examples
how to load File in CLOB column.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 672 total points
ID: 19989902
0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 664 total points
ID: 19989967
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:paquicuba
ID: 19992971
Do you mean UTL_RAW.CAST_TO_RAW?
Can you post your external table?
Why are you casting to RAW? Are you trying to encrypt something here?


0
 

Author Comment

by:Mus_Bak
ID: 19993541
Paquicuba,

Basically I'm trying to load the contents of a structured binary file. My goal is to convert these data into decimal format.
Usually I load the contents into a CLOB column (into a standard Oracle table via a PL/SQL procedure or an external table - same thing different approach) whereafter I cast the CLOB to raw using the utl_raw.cast_to_raw function. After this I am able to use a function which converts the data into decimal format. This approach does not work in this case due to the size of the datafile in that it exceeds the maximum allowable input for the utl_raw.cast_to_raw function (which is 2000 bytes). Alternatively I could try to split the datafile using the substr function before applying the utl_raw.cast_to_raw function.
Can anyone suggest a different approach?

Regards
0
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 664 total points
ID: 20007284
Can anyone suggest a different approach?

You have to work with the 4000 bytes limit NOT 2000, see below:

ALEX@DEV > CREATE TABLE CLOB_TBL(
  2  CLOB_COL CLOB
  3  )
  4  ORGANIZATION EXTERNAL
  5  (TYPE ORACLE_LOADER
  6  DEFAULT DIRECTORY CDIR
  7  ACCESS PARAMETERS
  8  (
  9  RECORDS DELIMITED BY NEWLINE
 10  NOBADFILE
 11  NODISCARDFILE
 12  NOLOGFILE
 13  READSIZE 20971520
 14  FIELDS
 15  (
 16  CLOB_COL CHAR(20971520)
 17  )
 18  )
 19  LOCATION ('TEMP.TXT'))
 20  REJECT LIMIT UNLIMITED;

Table created.

Elapsed: 00:00:00.14
ALEX@DEV > SELECT LENGTH(CLOB_COL)/(1024*1024) FROM CLOB_TBL;

LENGTH(CLOB_COL)/(1024*1024)
----------------------------
                  1.63818359

Elapsed: 00:00:00.67
ALEX@DEV > DECLARE
  2  V_CHAR VARCHAR2(4000);
  3  V_BLOB BLOB;
  4  BEGIN
  5  SELECT DBMS_LOB.SUBSTR(CLOB_COL,4000,1) INTO V_CHAR FROM CLOB_TBL;
  6  V_BLOB := UTL_RAW.CAST_TO_RAW(V_CHAR);
  7  DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(V_BLOB));
  8  END;
  9  /
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question