| FILE CONVERSION [message #299407] |
Mon, 11 February 2008 22:44  |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
Hi,
I have converted a flat file into an external table using following
create table t1(
f1 Varchar2(30),
f2 Varchar2(20),
f3 Varchar2(20)
)
organization external (
type oracle_loader
default directory dir1
access parameters (
records delimited by newline
fields terminated by ' '
missing field values are null
)
location ('1.txt')
)
reject limit unlimited;
I successsfully got the table.
But there is a problem,in the file (1.txt) fisrt field is separated by space and rest are separated by '|'.
How to use both the conditions?
Also
1Can anyone guide me to link for utl_file package for I/O.
2.How to know the memory taken by a table and during running of PL/SQL procedure
regards
[Updated on: Mon, 11 February 2008 22:49] Report message to a moderator |
|
|
|
|
|
| Re: FILE CONVERSION [message #299481 is a reply to message #299423] |
Tue, 12 February 2008 03:42  |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
| Michel Cadot wrote on Tue, 12 February 2008 00:39 | | Quote: | But there is a problem,in the file (1.txt) fisrt field is separated by space and rest are separated by '|'.
How to use both the conditions?
|
No but you can separate them when you query the table, this is the power of external table.
1. You don't need it, it will be far less performant
2. Precise your question
Regards
Michel
|
Thanks alot Michel,
I will do more self study on External tables..Can u provide me any link.
regarding your answer to query1..
did you mean utl_file is not so good in performance ?
|
|
|
|
|
|
| Re: FILE CONVERSION [message #299495 is a reply to message #299423] |
Tue, 12 February 2008 04:01 |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
| Michel Cadot wrote on Tue, 12 February 2008 00:39 | | Quote: | But there is a problem,in the file (1.txt) fisrt field is separated by space and rest are separated by '|'.
How to use both the conditions?
|
No but you can separate them when you query the table, this is the power of external table.
1. You don't need it, it will be far less performant
2. Precise your question
Regards
Michel
|
Michel ,
Thanks for the external table link..
regarding second point --- Lets suppose my table has 20 rows and 3 columns.How to know the space occupied by it?
Fields in file are
f1 abc|bb|mmm|dd
Now for file to table conversion using my code..
I can do only one thing ie create table with fields separated by '|' or fields separated by space..
If I choose fields separated by '|' then the first column will contain the first and second filed of the flat file ..
Can anyone of the fllowing work.
1.Substring whole column one and separate the two entries in two new columns.
or please suggest .
Thanks alot
|
|
|
|
|
|
| Re: FILE CONVERSION [message #299505 is a reply to message #299501] |
Tue, 12 February 2008 04:51 |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
| Michel Cadot wrote on Tue, 12 February 2008 04:36 | | Quote: | my table has 20 rows and 3 columns.How to know the space occupied by it?
|
Occupied where? Which kind of table, the external one or a standard one (forget IOT)?
| Quote: | 1.Substring whole column one and separate the two entries in two new columns.
|
INSTR, SUBSTR
Regards
Michel
|
Michel,
Actually I was using updat with replace on external table which was throwing error .
So I made a dummy standard table using
create table DUMMY as select * from ext_table..
So I shall be thankful if u tell ,how to know the space occupied for both external and standard table.
2.I will use subs/inst for my table conversion problem and revert
[Updated on: Tue, 12 February 2008 04:52] Report message to a moderator |
|
|
|
|
|
| Re: FILE CONVERSION [message #299567 is a reply to message #299545] |
Tue, 12 February 2008 07:52 |
ashish2345
Messages: 50
Registered: September 2007
|
Member |
|
|
| Michel Cadot wrote on Tue, 12 February 2008 06:50 | External table does no space.
For your target table, add the fields length, multiply by the number of rows, divide by (1-pctfree), add 10-20% to be safe.
Regards
Michel
|
How to know the pctfree? Kindly guide
|
|
|
|
| Re: FILE CONVERSION [message #299579 is a reply to message #299567] |
Tue, 12 February 2008 08:17 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
PCTFREE is given at table creation time (or modify afterwards).
You find it in user_tables.pct_free.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
