OraFAQ Forum: SQL & PL/SQL » UTL_FILE READ TAKING TIME

Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE READ TAKING TIME (ORACLE 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UTL_FILE READ TAKING TIME [message #587302]

Fri, 14 June 2013 01:23

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

Hi All,

Need urgent help on below process.

CREATE OR REPLACE PROCEDURE FILE_CHECK AS

V_LOOP_TILL_SUCCESS1 VARCHAR2(1) := 'N';
V_LOOP_TILL_SUCCESS VARCHAR2(1) := 'N';
VINHANDLE UTL_FILE.FILE_TYPE;
MSTATE VARCHAR2(1000);
MCODE NUMBER;
FFDATE DATE;
VNEWLINE VARCHAR2(250);
CF NUMBER :=1;

BEGIN

LOOP
BEGIN
VINHANDLE := UTL_FILE.FOPEN('INPUT_DIR', 'SMS.TXT', 'R');
IF UTL_FILE.IS_OPEN(VINHANDLE) THEN
INSERT INTO TB_FILE VALUES('SMS.TXT',SYSDATE,'Y');
ELSE
INSERT INTO TB_FILE VALUES('SMS.TXT',SYSDATE,'N');
END IF;

SELECT STATUS INTO V_LOOP_TILL_SUCCESS1 FROM TB_FILE;

IF V_LOOP_TILL_SUCCESS1 = 'Y' THEN
V_LOOP_TILL_SUCCESS :='Y';
END IF;

EXIT WHEN V_LOOP_TILL_SUCCESS = 'Y';

END;
END LOOP;

basically this procedure check if file present then execute next block else procedure keep running in loop
exit when file is available on location.

This procedure pick SMS.TXT file from location which is ('INPUT_DIR')
when i am test this proceudre this procedure take very much time at the line of

VINHANDLE := UTL_FILE.FOPEN('INPUT_DIR', 'SMS.TXT', 'R');

Thanks.

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587305 is a reply to message #587302]

Fri, 14 June 2013 01:28

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

file may be available or may not be available if
file is available then process next block else sleep for 10 sec.

----
CREATE OR REPLACE PROCEDURE FILE_CHECK AS

V_LOOP_TILL_SUCCESS1 VARCHAR2(1) := 'N';
V_LOOP_TILL_SUCCESS VARCHAR2(1) := 'N';
VINHANDLE UTL_FILE.FILE_TYPE;
MSTATE VARCHAR2(1000);
MCODE NUMBER;
FFDATE DATE;
VNEWLINE VARCHAR2(250);
CF NUMBER :=1;

BEGIN

LOOP
BEGIN
VINHANDLE := UTL_FILE.FOPEN('INPUT_DIR', 'SMS.TXT', 'R');
IF UTL_FILE.IS_OPEN(VINHANDLE) THEN
INSERT INTO TB_FILE VALUES('SMS.TXT',SYSDATE,'Y');
ELSE
INSERT INTO TB_FILE VALUES('SMS.TXT',SYSDATE,'N');
END IF;

SELECT STATUS INTO V_LOOP_TILL_SUCCESS1 FROM TB_FILE;

IF V_LOOP_TILL_SUCCESS1 = 'Y' THEN
V_LOOP_TILL_SUCCESS :='Y';
END IF;

EXIT WHEN V_LOOP_TILL_SUCCESS = 'Y';

dbms_lock.sleep(10);

END;
END LOOP;

End file_check;

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587306 is a reply to message #587302]

Fri, 14 June 2013 01:28

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This has already been said MANY AND MANY times to you:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

And FEEDBACK to those that help you.
Review ALL your previous topics and write a feedback and thank to people.

Waiting for you before helping you more in any way.

Regards
Michel

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587307 is a reply to message #587305]

Fri, 14 June 2013 01:31

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

thanks for feedback mr Michel.
can you help on this?

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587308 is a reply to message #587302]

Fri, 14 June 2013 01:32

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

From your previous topics:

Michel Cadot wrote on Thu, 26 April 2012 11:23

Michel Cadot wrote on Thu, 26 April 2012 08:57
1/ Do NOT post in UPPER case
2/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
3/ Also always post your Oracle version, with 4 decimals.

If you make no effort to post correctly I don't see any reason to help you.

Regards
Michel

cookiemonster wrote on Fri, 11 May 2012 14:18

You've been asked read and follow How to use [code] tags and make your code easier to read? before. Please do so...

BlackSwan wrote on Mon, 21 May 2012 08:03

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
...

Michel Cadot wrote on Sun, 17 March 2013 20:13

...
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587310 is a reply to message #587307]

Fri, 14 June 2013 01:33

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

umeshchandraseth wrote on Fri, 14 June 2013 07:31

thanks for feedback mr Michel.
can you help on this?

I would almost guarantee that Michel can help on this, however, I can also almost guarantee that he won't until you show that you have at least tried to follow the guidelines (which at this point, you clearly haven't)

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587311 is a reply to message #587307]

Fri, 14 June 2013 01:33

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

umeshchandraseth wrote on Fri, 14 June 2013 08:31

thanks for feedback mr Michel.
can you help on this?

No until you show some respect towards us in your behaviour.

Regards
Michel

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587312 is a reply to message #587307]

Fri, 14 June 2013 01:36

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

sorry sir,

now find formatted code.

CREATE PROCEDURE FILE_CHECK AS

V_LOOP_TILL_SUCCESS1 VARCHAR2(1) := 'N';
V_LOOP_TILL_SUCCESS VARCHAR2(1) := 'N';
VINHANDLE UTL_FILE.FILE_TYPE;
MSTATE VARCHAR2(1000);
MCODE NUMBER;
FFDATE DATE;
VNEWLINE VARCHAR2(250);

BEGIN

LOOP
BEGIN
VINHANDLE := UTL_FILE.FOPEN('INPUT_DIR', 'SMS.TXT', 'R');
IF
UTL_FILE.IS_OPEN(VINHANDLE)
THEN
INSERT INTO TB_FILE VALUES('SMS.TXT',SYSDATE,'Y');
ELSE
INSERT INTO TB_FILE VALUES('SMS.TXT',SYSDATE,'N');
END IF;

SELECT STATUS INTO V_LOOP_TILL_SUCCESS1 FROM TB_FILE;

IF
V_LOOP_TILL_SUCCESS1 = 'Y'
THEN
V_LOOP_TILL_SUCCESS :='Y';
END IF;

EXIT
WHEN V_LOOP_TILL_SUCCESS = 'Y';

DBMS_LOCK.SLEEP(10);

END;

END LOOP;

END FILE_CHECK;

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587313 is a reply to message #587312]

Fri, 14 June 2013 01:39

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Nope, try looking up the section on how to use code tags.

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587314 is a reply to message #587313]

Fri, 14 June 2013 01:47

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

thanks mr pablolee.

now find formatted code.

CREATE PROCEDURE File_check
AS
v_loop_till_success1 VARCHAR2(1) := 'N';
v_loop_till_success VARCHAR2(1) := 'N';
vinhandle utl_file.file_type;
mstate VARCHAR2(1000);
mcode NUMBER;
ffdate DATE;
vnewline VARCHAR2(250);
BEGIN
LOOP
BEGIN
vinhandle := utl_file.Fopen('INPUT_DIR', 'SMS.TXT', 'R');

IF utl_file.Is_open(vinhandle) THEN
INSERT INTO tb_file
VALUES ('SMS.TXT',
SYSDATE,
'Y');
ELSE
INSERT INTO tb_file
VALUES ('SMS.TXT',
SYSDATE,
'N');
END IF;

SELECT status
INTO v_loop_till_success1
FROM tb_file;

IF v_loop_till_success1 = 'Y' THEN
v_loop_till_success := 'Y';
END IF;

EXIT WHEN v_loop_till_success = 'Y';

dbms_lock.Sleep(10);
END;
END LOOP;
END file_check;

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587318 is a reply to message #587314]

Fri, 14 June 2013 02:23

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Nope.
Did you read the provided link?
Even a 7-year old child is able to do it.
If you can't, forget Oracle, forget PL/SQL, start to investigate in some job you'll have not to use what is usually between human being ears but, it seems, not yours.

Regards
Michel

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587320 is a reply to message #587318]

Fri, 14 June 2013 02:25

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

hey Michel.

its mean your child is developer.

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587321 is a reply to message #587320]

Fri, 14 June 2013 02:26

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

don't try to teach me.if you can help then please help.

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587324 is a reply to message #587320]

Fri, 14 June 2013 02:33

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

umeshchandraseth wrote on Fri, 14 June 2013 09:25

hey Michel.

its mean your child is developer.

At least he is able to format code in OraFAQ.

Regards
Michel

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587325 is a reply to message #587321]

Fri, 14 June 2013 02:33

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

umeshchandraseth wrote on Fri, 14 June 2013 09:26

don't try to teach me.if you can help then please help.

Why should I when you are so reluctant to follow the guide, post how we requested and do feedback and thank people that help you.

And if you don't want to learn you are not in the correct forum.

Regards
Michel

[Updated on: Fri, 14 June 2013 02:52]

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587326 is a reply to message #587321]

Fri, 14 June 2013 02:35

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

can you tell me.
where it is mention ' do not feedback nor thank people that help you.'

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587327 is a reply to message #587321]

Fri, 14 June 2013 02:35

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Code tags exist to make code easier to read. Pretty much every internet oracle forum has them and expects posters to use them.
Using them makes your code easier to follow for everyone, and so makes people more willing to read and understand the code and so help you.
So when a moderator of the site (Michel, pabolee and myself) ask you to use code tags it is a good idea to do so.
Because if you're not interested in spending 30 seconds to make your code easier to follow, why should anyone spend time helping you?

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587329 is a reply to message #587326]

Fri, 14 June 2013 02:47

umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi

Member

basically attached code check if file present then execute next block else procedure keep running in loop after first loop it will be sleep for 10 sec .

This procedure pick SMS.TXT file from location which is ('INPUT_DIR')
when i am test this proceudre this procedure take very much time at the line of

VINHANDLE := UTL_FILE.FOPEN('INPUT_DIR', 'SMS.TXT', 'R');

file may be available on location or may not be available.

Umesh

Attachment: Code.txt
(Size: 1.11KB, Downloaded 1226 times)

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587332 is a reply to message #587329]

Fri, 14 June 2013 02:53

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you read the link we provided you you know this is NOT the way to do (attach a file).
You have to post your code INLINE and FORMATTED.

Regards
Michel

Report message to a moderator

Re: UTL_FILE READ TAKING TIME [message #587336 is a reply to message #587329]

Fri, 14 June 2013 03:01

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

I explain to you why we ask you to use code tags - so instead you post the code as a file.
The link to the page that explains code tags (in simple terms, with pictures) is posted multiple times above. Click on it. read what it says, follow what it says.

Report message to a moderator

Previous Topic:	CONVERSIONS
Next Topic:	Need all invalid records

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 30 21:07:57 CDT 2025