[hussaintal]

We are using client_text_io utility (webutil.pll) to read a text file and insert specific fields into table in the database
Problem: firstly the read and insert performance is too fast, when the number of records increase the performance become slowly, finally the application hang.
When we are using another utility (UTL_FILE) in a stored procedure on the database directly, the operation works probably.
But we must use (webutil.pll)

We are using Oracle Application Server to deploy our application which
Contains forms and reports.
Where we use Oracle9i Enterprise Edition release 9.2.0.1.0 under HP UX,
Application Server release 9.0.4.0.0 under RH Linux, reports and Forms
Generated by Dev9i

Please reply to me as soon as possible

[debasisdas]

try to disable any index on the tabl;e during data loading process and enable the same alter on during data fetch process.

[hussaintal]

Thanks for your reply
we are using this method and do not work probably
I think problem raised from application not from sql insert into statement
Can you tell me how can I trace the application and find where it hang.

[debasisdas]

how i will know what are you doing in your application ?

[hussaintal]

the application trying to read a text file (line by line) located on PC and insert the records into a table in the database
the application use client_text_io utility (WEBUTIL) to read the file

[hussaintal]

if you ask about the code
we are using the following code:
DELETE FROM tableName;
in_file := client_text_io.fopen(:file_path,'r');
loop
V_LINE_COUNT := V_LINE_COUNT + 1;
begin
client_text_io.get_line(in_file,linebuf);
exception
when no_data_found then
IF V_LINE_COUNT = 1 THEN
MESSAGE('empty file');
RAISE FORM_TRIGGER_FAILURE;
END IF;
exit;
end;
V_var1 := nvl(substr(linebuf,1 ,10 ),0);
V_var2 := nvl(substr(linebuf,12 ,2 ),0);
V_var3 := substr(linebuf,15 ,10 );
V_var3 :=trim(V_var3);
IF V_var3 = '' THEN
V_var4 :='';
else
V_var4 := TO_NUMBER(V_var3) ;
end if;
V_var5 := substr(linebuf,26 ,70 );

INSERT INTO tableName VALUES (V_var1,V_var2,V_var4,V_var5);


client_text_io.new_line;
<<end_loop>>
null;
end loop;

[hussaintal]

thanks for your replying
finally we find a solution and it is so simple
set a parameter in java initiator on the client
go to the java initiator in control panel and fill the following
-mx256m -Dcache.size=20000000
in the java runtime parameter field
thats mean the JVM on the client can use 256 mega from the PC RAM