Data Upload using SQL loader from forms [message #510992] |
Wed, 08 June 2011 23:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
Hi,
Iam working with a form thru which I need to port nearly 7 laks of data into table.
Earlier I had created a form thru which I read the data from the text file and inserting into the table. This was taking lot of time and as well after an hour or so, after porting 50k rows the program got terminated and shows an error like Network Inturpted.
So I have decided to use some other option and found that I can use either SQL Loader or external Tables. I had choosed SQL loader option and created a form along with a control file and batch file based on some forum posting.
Control File
LOAD DATA
INFILE 'D:\Sethu\Pay\Clock Dump\CLK_050611\clock_dump.txt'
INTO TABLE ARS_CLOCK_DUMP
(TDATE POSITION(01:08) DATE 'YYYYMMDD',
VER POSITION(09:10) CHAR,
EMPNO POSITION(11:15) CHAR,
TTIME POSITION(16:19) CHAR,
BRADD POSITION(21:22) CHAR
)
Batch file being called from the form
sqlldr userid=ears/ears@td1
ERRORS=500000
control=D:\Sethu\Pay\Modified\Forms\ars_clock_dump.ctl
log=D:\Sethu\Pay\Modified\Forms\ars_clock_dump.log
and the above batch files is called from the form using
host('D:\Sethu\Pay\Modified\Forms\load_data.bat',no_screen);
With above all the form works perfectly in local system which is the development evironment and also client PC. And I was able to port those 7 laks rows in 3 miniutes.
Now the real problem, If I need to move this to live application server, I had to move three files [ FMB, CTL and BAT ]. I have some problems in moving the other two files to the application server [ waiting for approval from bozz ]. And more over, I had to hard code the user id and password in the BAT file, i think which may not be a best practice and also not safe.
So I have decieded to do all from forms and found same sort of script. I took it and modified to my needs.
Now the problem is, it is not working. It is not even raising error thru exception.
Pls help me to solve this.
Thank you
Code from form's when button pressed which is not working
declare
usid varchar2(10):= get_application_property(username);
pwd varchar2(10):= get_application_property(password);
db varchar2(10):= get_application_property(connect_string);
msqlldr varchar2(300);
ctrl_filename varchar (300) := 'D:\Sethu\Pay\Modified\Forms\ars_clock_dump.ctl';
data_filename varchar2(300) := 'D:\oracle\product\10.2.0\db_1\BIN\abc.dat';
fname varchar2(1000);
begin
msqlldr := ' C:\oracle\ora92\bin\sqlldr.exe '||
' userid = '|| usid || '/' ||pwd || '@' ||db||
' control = '|| ctrl_filename ||
' log = D:\Sethu\Pay\Modified\Forms\ars_clock_dump.log';
host(msqlldr,no_screen);
Exception when others then
message(sqlerrm||dbms_error_text);
message(' ');
end;
|
|
|
|
Re: Data Upload using SQL loader from forms [message #511026 is a reply to message #510995] |
Thu, 09 June 2011 03:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
Hi,
I have solved the problem in my local system which is in windows plat form. The following are the codes I have created in when button pressed trigger.
declare
usid varchar2(10):= get_application_property(username);
pwd varchar2(10):= get_application_property(password);
db varchar2(10):= get_application_property(connect_string);
begin
v_ctl_file := text_io.FOPEN(v_live_path||'ars_clock_dump.ctl', 'w');
text_io.PUT_LINE (v_ctl_file, 'LOAD DATA');
text_io.PUT_LINE (v_ctl_file, 'INFILE '''||v_live_path||'clock_dump.txt''');
text_io.PUT_LINE (v_ctl_file, 'INTO TABLE ARS_CLOCK_DUMP');
text_io.PUT_LINE (v_ctl_file, '(TDATE POSITION(01:08) DATE ''YYYYMMDD'',');
text_io.PUT_LINE (v_ctl_file, 'VER POSITION(09:10) CHAR,');
text_io.PUT_LINE (v_ctl_file, 'EMPNO POSITION(11:15) CHAR,');
text_io.PUT_LINE (v_ctl_file, 'TTIME POSITION(16:19) CHAR,');
text_io.PUT_LINE (v_ctl_file, 'BRADD POSITION(21:22) CHAR');
text_io.PUT_LINE (v_ctl_file, ')');
text_io.FCLOSE (v_ctl_file);
v_bat_file := text_io.FOPEN(v_live_path||'load_data.bat', 'w');
text_io.PUT_LINE (v_bat_file, 'sqlldr userid = '|| usid || '/' ||pwd || '@' ||db || ' ERRORS=1000000 control = '|| v_live_path||'ars_clock_dump.ctl log = '|| v_live_path||'ars_clock_dump.log');
text_io.FCLOSE (v_bat_file);
host(v_live_path||'load_data.bat',no_screen);
Exception when others then
message(sqlerrm||dbms_error_text);
message(' ');
end;
now the problem is when i move this form to my application server which is in unix platform, the form is not working.
Request some help.
|
|
|
|
Re: Data Upload using SQL loader from forms [message #511040 is a reply to message #511037] |
Thu, 09 June 2011 03:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
Sorry about that. I forgot abt when others. since it had worked I just ignored it. i will remove it now.
It is not working means, data is not getting ported and also form is not displaying any message. non of the file iam trying to create is not created [CTL, BAt, LOG, or BAD ].
|
|
|
|
Re: Data Upload using SQL loader from forms [message #511047 is a reply to message #511045] |
Thu, 09 June 2011 03:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
Sorry. Just now i found it.
CTL file and LOG file has got created. But for saftety purpose, after finishing the job, i have replaced the content of both files with some text. So i do not what was the content created.
Am changing the code, so that the content of both the files will not get replaced. After analizing the content let me buzz here.
and for your infor the BAD and LOG file has not created.
Thank you.
|
|
|
|
|
Re: Data Upload using SQL loader from forms [message #511158 is a reply to message #511049] |
Thu, 09 June 2011 22:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
Hi the following are the file contents for control file and batch file. Everything seems to be correct. But sqlldr.exe is not starting as you said.
Control File
LOAD DATA
INFILE '/apps/alpay/nonconfi/txt/clock_dump.txt'
INTO TABLE ARS_CLOCK_DUMP
(TDATE POSITION(01:08) DATE 'YYYYMMDD',
VER POSITION(09:10) CHAR,
EMPNO POSITION(11:15) CHAR,
TTIME POSITION(16:19) CHAR,
BRADD POSITION(21:22) CHAR
)
Batch File
sqlldr userid = userid/pw@dc ERRORS=1000000 control = /apps/alpay/nonconfi/txt/ars_clock_dump.ctl log = /apps/alpay/nonconfi/txt/ars_clock_dump.log
Only difference between the windows and unix are resides in the path.
Is there any problem in it? is that the right syntax? is there any limitation on number of records mentioned in errors?
[Updated on: Thu, 09 June 2011 22:19] Report message to a moderator
|
|
|
|
|
|
|
Re: Data Upload using SQL loader from forms [message #511357 is a reply to message #511352] |
Sun, 12 June 2011 05:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
Hi,
It seems there were some problme in rights related to load_data.sh file.
I spoke to admin regarding that and he executed it from back end and it has worked.
Now I have executed the same from front end and it is in process.
Actually, when i tried in my test server and form from the local it took nearly 3 mints. But when the admin tried it it had took more than 1 hrs and 45 mints. it looks like it is taking same time when i run from application. I have enclsoed the summary for your reference.
When I ran it locally from windows plat form system
Table ARS_CLOCK_DUMP:
460351 Rows successfully loaded.
82794 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 2048 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 543145
Total logical records rejected: 82794
Total logical records discarded: 0
Run began on Sun Jun 12 10:13:11 2011
Run ended on Sun Jun 12 10:16:35 2011
Elapsed time was: 00:03:24.61CPU time was: 00:00:12.65
When my admin ran the sh file from server's unix prompt directly
Table ARS_CLOCK_DUMP:
460351 Rows successfully loaded.
82794 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 65536 bytes(2048 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 543145
Total logical records rejected: 82794
Total logical records discarded: 0
Run began on Sun Jun 12 10:32:47 2011
Run ended on Sun Jun 12 11:46:35 2011
Elapsed time was: 01:13:48.99CPU time was: 00:17:10.15 (Ma
I really dont know why it has took such a long time. Even now when am excuting this from application it is still executing, I think its still processing.
|
|
|
|
Re: Data Upload using SQL loader from forms [message #511519 is a reply to message #511357] |
Mon, 13 June 2011 12:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
sethumurugan wrote on Sun, 12 June 2011 06:23Hi,
It seems there were some problme in rights related to load_data.sh file.
There is no environment whatsoever when you run a shell script from within oracle. You need to specify PATH and all your ORACLE environmental variable. I am guessing it is not running at all.
|
|
|
|
|
|
|
|
|
|
Re: Data Upload using SQL loader from forms [message #511806 is a reply to message #511755] |
Wed, 15 June 2011 03:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Lets start again. How long does it take when you run it from forms?
How long does it take when you run it on the server for the same DB?
Probably you will need to trace the sessions to see where the time is being spent.
It could be a network issue.
If you have DBA's it might be an idea to ask them.
|
|
|
Re: Data Upload using SQL loader from forms [message #511814 is a reply to message #511806] |
Wed, 15 June 2011 04:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
cookiemonster wrote on Wed, 15 June 2011 14:26Lets start again. How long does it take when you run it from forms?
How long does it take when you run it on the server for the same DB?
Let me give you step by step opperations and its results.
When I tried from local system [ windows 7 ] thru forms connecting test DB [ Since I can connect to live server without application ] it took 3 mints approx. But used Batch file to run SQLLDR.
Then I have deployed the form in live application server [Unix Box] which is connected with live DB with SH file to run SQLLDR, it did not work.
Then we found that we need to provide some rights to SH file.
DBA provided those rights to that SH file and Executed SH file directly from Unix Prompt connecting live server out side the forms.
It took nearly 1 hours and 45 minuite.
Then again I tried to do the same ie. run SQLLDR outside the forms from my system connecting test server and it took the same 3 mints.
In addition to this, I ran the application in live environment and ran the form to import data, i took lot of time and I could not see the result since I had to go home.
I did not do that excercise again knowing that it will take time.
Then for cross verification, I ran in local system
cookiemonster wrote on Wed, 15 June 2011 14:26
It could be a network issue.
I dont know. DBA ran it direcly on the DB server [ I think so ]
cookiemonster wrote on Wed, 15 June 2011 14:26
If you have DBA's it might be an idea to ask them.
Am trying. But could not. Waiting for another guy to come up with solution. I cant wait for him. So am tring myself with help of forums.
Thank you.
|
|
|
Re: Data Upload using SQL loader from forms [message #511821 is a reply to message #511814] |
Wed, 15 June 2011 04:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
To be blunt - I don't see any way that running the same sqlloader script, for the same ammount of data, on the DB server,
can be slower than running it remotely. Never mind 24 times slower.
So that leads to one of two conclusions:
1) The two runs didn't load the same amount of data.
2) The DBA didn't run it on the DB you think he did.
You are just going to have to talk to the DBA because what you are reporting makes no sense.
|
|
|
|
Re: Data Upload using SQL loader from forms [message #511824 is a reply to message #511357] |
Wed, 15 June 2011 04:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
sethumurugan wrote on Sun, 12 June 2011 15:53
When I ran it locally from windows plat form system
Table ARS_CLOCK_DUMP:
460351 Rows successfully loaded.
82794 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 2048 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 543145
Total logical records rejected: 82794
Total logical records discarded: 0
Run began on Sun Jun 12 10:13:11 2011
Run ended on Sun Jun 12 10:16:35 2011
Elapsed time was: 00:03:24.61CPU time was: 00:00:12.65
When my admin ran the sh file from server's unix prompt directly
Table ARS_CLOCK_DUMP:
460351 Rows successfully loaded.
82794 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 65536 bytes(2048 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 543145
Total logical records rejected: 82794
Total logical records discarded: 0
Run began on Sun Jun 12 10:32:47 2011
Run ended on Sun Jun 12 11:46:35 2011
Elapsed time was: 01:13:48.99CPU time was: 00:17:10.15 (Ma
Please go thru the summary i have posted. it is same amount of data. Both data got stored in the table. DB has ran it in the DB which I wanted.
|
|
|
Re: Data Upload using SQL loader from forms [message #511826 is a reply to message #511824] |
Wed, 15 June 2011 04:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Let's start again, again.
How long does it take to run the sqlloader script from your form, against the DB the DBA ran the script on?
3 mins?
Or longer?
How many rows in the table you are trying to load on the test and prod DB's?
Are there indexes on the tables?
Are there triggers on the tables?
|
|
|
Re: Data Upload using SQL loader from forms [message #511829 is a reply to message #511826] |
Wed, 15 June 2011 05:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sethumurugan
Messages: 61 Registered: June 2010 Location: Chennai
|
Member |
|
|
cookiemonster wrote on Wed, 15 June 2011 15:19How long does it take to run the sqlloader script from your form, against the DB the DBA ran the script on?
3 mins?
Or longer?
longer
cookiemonster wrote on Wed, 15 June 2011 15:19
How many rows in the table you are trying to load on the test and prod DB's?
table will be empty in both cases. I will truncate the table before starting SQLLDR
cookiemonster wrote on Wed, 15 June 2011 15:19
Are there indexes on the tables?
Are there triggers on the tables?
No triggers, no constraints, no indexes
|
|
|
|
|