import data in oracle forms

chalie001
chalie001 used Ask the Experts™
on
hi how can i import data to database table using oracle form i got the following screen
loaddatai got the following code
begin
	upload_db;
	  exception
  	when others
  	then
  	  message('File upload failed: '||sqlerrm);

end;

PROCEDURE UPLOAD_DB IS
  l_success boolean;
BEGIN
  l_success := webutil_file_transfer.Client_To_DB_with_progress
                                    (clientFile      => :upload.file_name
                                    ,tableName       => 'wu_fileupdown_demo'
                                    ,columnName      => 'BLOB'
                                    ,whereClause     => 'ID = 1'
                                    ,progressTitle   => 'Upload to Database in progress'
                                    ,progressSubTitle=> 'Please wait'
                                    ,asynchronous    => false
                                    ,callbackTrigger => null
                                    );
  if l_success
  then
  --
	-- Force commit to save file in db.                                    
	-- This is not in the original Demo 
	-- which causes the Demo to fail if 
	-- you exit and return later to download.
  	:System.Message_Level := '15';                                   
		commit;
  	:System.Message_Level := '0';	
  	
    message('File uploaded successfully into the Database');
    
-- Added to make demo more user friendly.
-- If file is successfully uploaded, move to "Download" tab.        
    go_item('download.file_name');
    
  else
    message('File upload to Database failed');
  end if;
  
  exception
  	when others
  	then
  	  message('File upload failed: '||sqlerrm);
END;

=========================================
declare
	l_filename varchar2(200);
begin
	l_filename := client_get_file_name(directory_name => :global.user_home
                                    ,file_name      => null
                                    ,file_filter    => null
                                    ,message        => null
                                    ,dialog_type    => null
                                    ,select_file    => null
                                    );
  :upload.file_name := l_filename;
  SYNCHRONIZE;
end;

am using this procedure to import data,where do i call this procedure
Procedure Import_csv_file (I_FILENAME IN VARCHAR2) Is

   — Text File Type

   Infile        Text_Io.File_Type;

   Linebuf       Varchar2 (4000);

   V_Getstring   Varchar2 (100);

 

 

   — Field Values Array

   Type Fieldvalue Is Table Of Varchar2(100)

      Index By Binary_Integer;

 

 

   Fv            Fieldvalue;

   Rec_Count Number := 0;

Begin

   Infile := Text_Io.Fopen (I_FILENAME, ‘R’);

   — Read File

 

 

   Loop



           Rec_Count := Rec_Count + 1;

      Text_Io.Get_Line (Infile, Linebuf);

      Linebuf := Linebuf || ‘,’;

         — read from 1 to number of occurrences of comma or any other delimiter

         — below giving example for 3 occurrences

         For I In 1 .. 3

         Loop

            Fv (I) := D2k_Delimited_String.Getstring (Linebuf, I, False, ‘,’);

         End Loop;

 

 

         Begin



            Insert Into yourtable (col1, col2, col3)

                                    Values ( Fv(1), Fv(2), Fv(3));

 

 

         Exception

            When Others

            Then

               Message (Sqlerrm);

         End;

   End Loop;

 

 

   Text_Io.Fclose (Infile);

Exception

   When No_Data_Found Then

 

   — End Of The Text File Reached…. Then Save…

       commit_form;

—  Message(Sqlerrm);

      Text_Io.Fclose (Infile);

      Message (‘Import Completed.’);

   When Others Then

      Text_Io.Fclose (Infile);

      message(sqlerrm);

End;

Open in new window


i have attach the form
importer.zip
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark GeerlingsDatabase Administrator

Commented:
The code in this procedure only transfers a file to the database server.  It does not import data into a database table.  There is no need to do a "commit" in this procedure, because there is no database activity in this procedure.  Transferring a file to the database server is an activity completely outside of the database, and completely independent of whether a commit is done (or not) in the procedure that transfers the file.

Assuming this file transfer works as intended, you likely need to execute a separate procedure later to actually import data from the transferred file into the database.

Author

Commented:
this the procedure to import data i what to know where do i call this
Procedure Import_csv_file (I_FILENAME IN VARCHAR2) Is

   — Text File Type

   Infile        Text_Io.File_Type;

   Linebuf       Varchar2 (4000);

   V_Getstring   Varchar2 (100);

 

 

   — Field Values Array

   Type Fieldvalue Is Table Of Varchar2(100)

      Index By Binary_Integer;

 

 

   Fv            Fieldvalue;

   Rec_Count Number := 0;

Begin

   Infile := Text_Io.Fopen (I_FILENAME, ‘R’);

   — Read File

 

 

   Loop



           Rec_Count := Rec_Count + 1;

      Text_Io.Get_Line (Infile, Linebuf);

      Linebuf := Linebuf || ‘,’;

         — read from 1 to number of occurrences of comma or any other delimiter

         — below giving example for 3 occurrences

         For I In 1 .. 3

         Loop

            Fv (I) := D2k_Delimited_String.Getstring (Linebuf, I, False, ‘,’);

         End Loop;

 

 

         Begin



            Insert Into yourtable (col1, col2, col3)

                                    Values ( Fv(1), Fv(2), Fv(3));

 

 

         Exception

            When Others

            Then

               Message (Sqlerrm);

         End;

   End Loop;

 

 

   Text_Io.Fclose (Infile);

Exception

   When No_Data_Found Then

 

   — End Of The Text File Reached…. Then Save…

       commit_form;

—  Message(Sqlerrm);

      Text_Io.Fclose (Infile);

      Message (‘Import Completed.’);

   When Others Then

      Text_Io.Fclose (Infile);

      message(sqlerrm);

End;

Open in new window


you can check my form

or must must is use witht his
PROCEDURE select_file_proc 
			(V_KDIC_PATH in char)
IS
	V_SELECT_FROM			VARCHAR2(100);
	V_COUNTER					NUMBER(5);
	
BEGIN
	SHOW_NOTE_MESSAGE('Please select a file in your local machine'); 
	
  :PARAMETER.P_FILENAME  := CLIENT_GET_FILE_NAME (directory_name => V_KDIC_PATH,
	                               file_name       => NULL,
	                               file_filter     => NULL,
	                               message         => 'Open a file',
	                               dialog_type     => OPEN_FILE);
	                                
  if :PARAMETER.P_FILENAME is null then
  	 exit_form(no_commit); 
	else 
  	 :PARAMETER.P_CONTINUE := 'Y';
	end if; 
END;

Open in new window

Mark GeerlingsDatabase Administrator

Commented:
I can't open the IMPORT_DATA.fmb file that is in the *.zip file that you uploaded, because that IMPORT_DATA.fmb apparently references a *.PLL library other than the one you included in the *.zip file.

You can call that Import_csv_file procedure wherever you want to call it in your form.  And, if you use this Import_csv_file procedure, you don't need the UPLOAD_DB procedure that you described earlier in this question, since that transfers a file to the DB server.  But the Import_csv_file procedure can load data directly into the database from a file on the client PC.  There is no need then to transfer the file to the DB server.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
How will I browser to the file I will send the correct fmb and pull and file to load later
Mark GeerlingsDatabase Administrator

Commented:
Are you asking; "How can you browse to the file on your machine that you want to upload to the database?"  We don't have access to your machine so that is difficult for us to answer.

Or, are you asking: "How can you get Oracle Forms to display a list of files on your machine?"  A procedure something like the select_file_proc that you posted here, or a Java procedure can help with that.

Or you (or any Oracle Forms user) should be able to use Windows Explorer to browse to a file, then you could manually copy the directory and file name into Oracle Forms to upload the file.

Author

Commented:
I have upload procedure where do I call the procedure to import data to database I what to select a file in my client and import data to database table

Author

Commented:
hi i have upload the file with table script and liabrary
importer.zip

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial