UTL_FILE Problem

[ekobudy]

Hi All,

I work on Oracle an the OS is AIX, what i want to do is.. Read and write a file from client using UTL_FILE.
Does any body could explain about it?

Thanks,

ekow

 

[SantaMufasa]

Unfortunately, any file that you can access from the UTL_FILE package must be visible to the server upon which Oracle runs. If you can do an "ls" or a "cat" against the file, then you should be able to access it with UTL_FILE; otherwise, no, you cannont access it.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:23 (20Jun04) UTC (aka "GMT" and "Zulu"), 13:23 (20Jun04) Mountain Time)

 

[carp]

The file also needs to reside in a subdirectory that is included in the list identified by the UTL_FILE_DIR parameter.

 

[SantaMufasa]

Well said, Carp (as usual)!

 

[ekobudy]

thanks for the response guys...

the problem is, i can't access to the AIX server...

does any body have any solution,....

what i wanna do is ..just loading the flatfile to Oracle Server without SQL*Loader.

Regards,


Ekow

 

[SantaMufasa]

Ekobudy,

Sure you can do it. I load flat files from my client PC to our AIX server running Oracle all the time. I just don't do it with UTL_FILE.

What is the format of the flat file (.csv, et cetera) and what is the structure of your target table?

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:32 (22Jun04) UTC (aka "GMT" and "Zulu"), 22:32 (21Jun04) Mountain Time)

 

[SantaMufasa]

Ekobudy,

I have some followup questions:

1) Are you the one that creates the flat file, or does someone else provide the file to you?
2) Can you specify the format of the flat file? For example, can you specify the fields as tab-delimited?
3) How many rows is the file? How many bytes?
4) How often will you process flat files of this style?

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:41 (22Jun04) UTC (aka "GMT" and "Zulu"), 22:41 (21Jun04) Mountain Time)

 

[ekobudy]

the flatfile will be loaded every 30 secs,
its size vary between 200KB until 8MB...

do you have any sugesstion ?


Ekow

 

[SantaMufasa]

Ekow,

What process generates the flat file? Do you have any control over the creating process? Can you specify the output format of the flat file data?

Depending upon the answers to these questions, my suggestion to you is, "Cause the creating process to generate SQL INSERT commands that your PC client can execute.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:21 (22Jun04) UTC (aka "GMT" and "Zulu"), 23:21 (21Jun04) Mountain Time)

 

[ekobudy]

ok mustafa,
here is the sample of FLATFILE :

CODEBRID 982991002
CODENAME Avian
CODEPRICE 8000
CODESTOCK 312
CODEBRID 992995621
CODENAME Phone Book
CODEPRICE 20
CODESTOCK 120

The table structure :
ID Number(20) primary key,
NAME Varchar2(40),
PRICE$ Number(18,2),
STOCK Number(10)

so, i have to parse the flatfile first... before inserting to database...

Thanks..

 

[SantaMufasa]

Ekow,

I see two very viable solutions for you:
1) modify the program that generates your output to produce syntactically correct INSERT statements to read:

INSERT INTO <table_name> VALUES (982991002,'Avian',8000,312);
INSERT INTO <table_name> VALUES (992995621,'Phone Book',20120);

2) if you cannot modify the originating output program, then write a program (PL/SQL UTL_FILE, for example) that produces the same INSERT code as above.

Then you run the resulting INSERT code from your PC while connected to the schema that owns the target table.

Any questions?

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:49 (22Jun04) UTC (aka "GMT" and "Zulu"), 23:49 (21Jun04) Mountain Time)

 

[SantaMufasa]

Don't you just hate it when you hit the [Submit Post] button then you see an error? In the code above, the second INSERT statement is missing a comma and should read:

INSERT INTO <table_name> VALUES (992995621,'Phone Book',20,120);

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:52 (22Jun04) UTC (aka "GMT" and "Zulu"), 23:52 (21Jun04) Mountain Time)

 

[ekobudy]

Mufasa,
I cannot change the generated file output from users,
and i cannot use the UTL_FILE because the DB server wont allow me to put something at their server..

so i have to parse the flatfile from client and send insert command.

i have done this using oracle forms TEXT_IO package,
but, later... if the transaction going bigger and bigger, i have to consider not using GUI for faster process..

 

[sem]

Try to use Perl. It's very convenient for parsing and allows Oracle interaction.

Regards, Dima

http://seminihin.narod.ru

 

[ekobudy]

do you have any sample dima? interaction between perl an oracle?

 

[sem]

http://www.orafaq.com/faqperl.htm

Regards, Dima

http://seminihin.narod.ru

 

[ekobudy]

thx sem...
Discussion Closed.