Home » SQL & PL/SQL » SQL & PL/SQL » FILE CONVERSION
FILE CONVERSION [message #299407] Mon, 11 February 2008 22:44 Go to next message
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 #299423 is a reply to message #299407] Tue, 12 February 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: FILE CONVERSION [message #299481 is a reply to message #299423] Tue, 12 February 2008 03:42 Go to previous messageGo to next message
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 #299490 is a reply to message #299481] Tue, 12 February 2008 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
did you mean utl_file is not so good in performance ?

Comparing to external table and SQL, yes.

http://www.oracle.com/pls/db102/search?remark=quick_search&format=ranked&word=external+table&tab_id=

Regards
Michel


Re: FILE CONVERSION [message #299495 is a reply to message #299423] Tue, 12 February 2008 04:01 Go to previous messageGo to next message
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 #299501 is a reply to message #299495] Tue, 12 February 2008 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: FILE CONVERSION [message #299505 is a reply to message #299501] Tue, 12 February 2008 04:51 Go to previous messageGo to next message
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 #299545 is a reply to message #299505] Tue, 12 February 2008 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: FILE CONVERSION [message #299567 is a reply to message #299545] Tue, 12 February 2008 07:52 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PCTFREE is given at table creation time (or modify afterwards).
You find it in user_tables.pct_free.

Regards
Michel
Previous Topic: Create partitioned table as select
Next Topic: Mutating Table Help
Goto Forum:
  


Current Time: Sat Dec 03 05:38:07 CST 2016

Total time taken to generate the page: 0.09472 seconds