Home » SQL & PL/SQL » SQL & PL/SQL » Read a .csv file which has more than 45000 Characters
Read a .csv file which has more than 45000 Characters [message #605200] Wed, 08 January 2014 00:14 Go to next message
sivakarthick
Messages: 3
Registered: January 2014
Location: chennai
Junior Member
Hi all,



Iam a receiving a .CSV file which contains n number of rows ,each row has a data seperated with delimiter.

These values based on delimiter it is seperated and certain validations are handled.

Iam using UTL_FILE.GET_LINE operator to read the value and store it in a variable which is declared as l_text VARCHAR2(30000).

when we receive a line which contains huge characters say 45000 then it throws an error(Unable to read the file).

Only on rare cases we used to receive these kind of files with huge size.

Kindly help to solve this issues.


Database details.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


Hereby i have tried with long Raw data type but still same error unable to read the file.pls find the sample code below,


declare

l_text LONG RAW;

l_importTypeFile UTL_FILE.FILE_TYPE ;


begin

PKG_COMMON.PROC_FOPEN(g_Directory, P_FILENAME, 'RB',l_importTypeFile, p_error_cd, p_error_msg) ;

UTL_FILE.GET_RAW(l_ImportTypeFile,l_Text);

End;



Thanks in Advance

Siva
Re: Read a .csv file which has more than 45000 Characters [message #605210 is a reply to message #605200] Wed, 08 January 2014 01:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
You need to set the linesize when you open the file, else it is defaulted to 1024. If the limit is crossed, you will get this error. Maxlinesize allowed is 32767.

I would go with External Tables. It gives more flexibility and the ability to use sql for any data manipulation.
Re: Read a .csv file which has more than 45000 Characters [message #605213 is a reply to message #605210] Wed, 08 January 2014 02:16 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I agree: an external table seems more feasible. You could also look at DBMS_LOB.LOADCLOBFROMFILE

MHE
Re: Read a .csv file which has more than 45000 Characters [message #605222 is a reply to message #605213] Wed, 08 January 2014 03:05 Go to previous messageGo to next message
sivakarthick
Messages: 3
Registered: January 2014
Location: chennai
Junior Member
Actually it's an maintenance application were we not required external table.
And also we receive these kind of data in rare cases.
Re: Read a .csv file which has more than 45000 Characters [message #605223 is a reply to message #605210] Wed, 08 January 2014 03:06 Go to previous messageGo to next message
sivakarthick
Messages: 3
Registered: January 2014
Location: chennai
Junior Member
i have set the line size(32767) also but still getting the same error.

[Updated on: Wed, 08 January 2014 03:06]

Report message to a moderator

Re: Read a .csv file which has more than 45000 Characters [message #605240 is a reply to message #605223] Wed, 08 January 2014 04:21 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
What is the exact error code? i.e ORA-xxxxx

Also, the limit for a VARCHAR2 is 4000.

HTH
-g
Re: Read a .csv file which has more than 45000 Characters [message #605244 is a reply to message #605240] Wed, 08 January 2014 04:29 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
gazzag wrote on Wed, 08 January 2014 11:21
What is the exact error code? i.e ORA-xxxxx

Also, the limit for a VARCHAR2 is 4000.

HTH
-g

In PL/SQL the Varchar2 limit is 32,767 bytes.
In Oracle 12c, the Varchar2 limit is 32,767 bytes everywhere, even in SQL.

MHE
Re: Read a .csv file which has more than 45000 Characters [message #605245 is a reply to message #605244] Wed, 08 January 2014 04:31 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Maaher wrote on Wed, 08 January 2014 10:29

In Oracle 12c, the Varchar2 limit is 32,767 bytes everywhere, even in SQL.


Of course. Thank you Maaher.

Mind you, the thought of developers having VARCHAR2(32767) in SQL to play with fills me with dread Smile
Previous Topic: can a string be used with IN within a query?
Next Topic: sql code error meaning
Goto Forum:
  


Current Time: Fri Mar 29 08:39:13 CDT 2024