|
|
|
|
|
|
|
|
|
|
| Re: Read text file with delimiter besides UTL & SQL* Loader [message #325280 is a reply to message #325214] |
Thu, 05 June 2008 09:21   |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It might be a restriction that was relaxed in 10g, but from the 9 Documentation
| Quote: | Restrictions on External Tables
* No other clauses are permitted in the same CREATE TABLE statement if you specify the external_table_clause.
* An external table cannot be a temporary table.
* You cannot specify constraints on an external table.
* An external table cannot have object type columns, LOB columns, or LONG columns.
|
<pause while I check the 10g docs>
Yup, the restriction has been relaxed a bit in 10g.
From the docs
| Quote: | Restrictions on External Tables
External tables are subject to the following restrictions:
* An external table cannot be a temporary table.
* You cannot specify constraints on an external table.
* An external table cannot have object type, varray, or LONG columns. However, you can populate LOB columns of an external table with varray or LONG data from an internal database table.
|
I don't know how to interpret that last restriction, as External tables are still read only in 10g (as I understand it).
|
|
|
|
|
|
|
|
| Re: Read text file with delimiter besides UTL & SQL* Loader [message #325804 is a reply to message #325462] |
Mon, 09 June 2008 00:02 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
Hi,
Thanks for all the reply. I strongly agree with JRowbottom - As always, try telling us what you are trying to do, rather than how you want to achieve it.
What I'm trying to is -> We are in the midst of implementing a third party recruitment system and we are required to transfer the applicants data back to our Oracle Recruitment System. The third party recruitment system will generate the data in csv/text format. So I have two main tasks here.
One is finding the solution how to read the text/csv files and
Second, study the API in order to load the data into the tables.
We can't always assume that the csv/text files will not over 32kb. So that why I'm looking for better approach.
By the way, I really have no idea on how to "concatenate the results of muliple reads together?"
Pls advise.
Ying
|
|
|
|
| Re: Read text file with delimiter besides UTL & SQL* Loader [message #325840 is a reply to message #325804] |
Mon, 09 June 2008 02:58 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The limit on UTL_FILE is that it can read at most 32767 characters from the file in a single call to UTL_FILE.GET_LINE
If you make another call to UTL_FILE.GET after that, you can read the next line of text in from the file.
Generally, the structure for ussing UTL_FILE is something like:Open file with call to UTL_FILE>FOPEN
Loop
Read Line from File with GET_LINE
Trap the No_Data_Found exception that occurrs if you read past
the end of the file and Exit if this occurrs
Process the line that you've just read
End Loop
Close File with UTL_FILE.FCLOSE
If you need to read all the data from the file, and have it all available at once, use a CLOB datatype, and handle it withDBMS_LOB
|
|
|
|
| Re: Read text file with delimiter besides UTL & SQL* Loader [message #326107 is a reply to message #325158] |
Tue, 10 June 2008 04:01 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
Guess I have missunderstood, if I can understand you correctly, it only limit the get_line to the max of 32767 characters per line, not the entire text of the file, right?
For example:Assume that I have a text.txt with file content as below:
1001,MICHELLE,20,C001
1002,JAMES,23,C001
1003,JENNY,25,C008
And my code is as below:
declare
2 file_handle utl_file.file_type;
3 output_line varchar2(32767);
4 i number;
5 begin
6 i := 0;
7 file_handle := utl_file.fopen('TEST_DIR','test.txt','r');
8 loop
9 begin
10 utl_file.get_line(file_handle,output_line);
11 dbms_output.put_line('Line Number : ' || i || ' and length is : ' || length(output_line));
12 i := i + 1;
13 exception
14 when no_data_found
15 then
16 exit;
17 end;
18 end loop;
19 if utl_file.is_open(file_handle)
20 then
21 utl_file.fclose(file_handle);
22 end if;
23* end;
Then even the file size is over 32KB, then it will not be a problem. Is it correct?
Pls advise.
Ying
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
