OraFAQ Forum: Forms » Data Upload using SQL loader from forms

Home » Developer & Programmer » Forms » Data Upload using SQL loader from forms (10g)

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

Data Upload using SQL loader from forms [message #510992]

Wed, 08 June 2011 23:04

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;