|
|
| Re: Data Upload using SQL loader from forms [message #511341 is a reply to message #511312] |
Sat, 11 June 2011 22:44   |
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
|
Member |
|
|
Hi,
You will need a Control file, batch file and Oracle form to do this.
Control File
This will have the details about your table name, its structure with datatype and source file name.
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
)
In my example, Iam creating this control file thru forms. This will be useful if you are going to use the form dynamically. if your requirement is static then easily you can create it outside the forms.
Batch File
This is to run the SQLLDR with control file. This also am creating from form. This also can be created outside form.
sqlldr userid = userid/pw@dc ERRORS=1000000 control = /txt/ars_clock_dump.ctl log = /txt/ars_clock_dump.log
When button Pressed Trigger
Please have the following in the coding part
declare
varchar2(10):= get_application_property(username);
varchar2(10):= get_application_property(password);
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, 'BADFILE '''||:parameter.prm_RRPTPATH||'ars_clock_dump_bad.rrpt''');
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');--Windows
v_bat_file := text_io.FOPEN(v_live_path||'load_data.sh', 'w'); --Unix
text_io.PUT_LINE (v_bat_file, 'sqlldr userid = '|| usid || '/' ||pwd || '@' ||db || ' ERRORS=1000000 control = '|| v_live_path||'ars_clock_dump.ctl log = '||:parameter.prm_RRPTPATH||'ars_clock_dump_log.rrpt');
text_io.FCLOSE (v_bat_file);
--host(v_live_path||'load_data.bat',no_screen); --Windows
host(v_live_path||'load_data.sh',no_screen); --Unix
end;
Pls let me know if you have any problems. Iam also doing this kind for first time. so if you have problems I will try to help you.
|
|
|
|
|
|
|
|
| Re: Data Upload using SQL loader from forms [message #511436 is a reply to message #511424] |
Mon, 13 June 2011 02:02 |
sonia.ali
Messages: 40
Registered: April 2009
Location: Pakistan
|
Member |
|
|
Hi,
Dear Irfan,
Thank you very much to your reply. I am new in oracle so i could not understand how to use sql loader. It's heartily request to you. Please make an example in hr schema.
Then I will easily understand.
Thanks again.
Sonia Ali
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
