I've got a question how to import a .csv file into a Oracle Forms application.
We are using Oracle Forms 11g on a Oracle 12c Database.
Now we want to import a .csv file with the Forms applicationso our customers can import this file and write the data into the database.
My plan is to create an application where the user can import a .csv file with a filechooser. The data from the .csv will be read and an output shows the user the data in this application. Then the user should be able to save it into the database through a button.
I've tried several searches but haven't found the right solution for this kind of problem. The only solutions I've found were a direct import of a .csv file into a database but not through Oracle Forms
Is it even possible to load .csv files in Oracle Forms?
If anyone has a good solution or anything else that might be helpfull i would be thankfull for that.
I've found a suitable solution for me now. Maybe I could do it better but this helps me so far.
I've found the following Blog: http://tfathy.blogspot.de/2009/03/reading-from-file.html
The Code works 100% by copy&paste and helps me from now on to complete the task.
Maybe it might help anyone else too.
Here is the solutioncode:
Reading From File
March 19, 2009
--------------------------------------------------
Declare
vfilename varchar2(500);
in_file Client_Text_IO.File_Type;
linebuf VARCHAR2(1800);
BEGIN
vfilename := client_get_file_name('c:/temp/', File_Filter=>'Comma Dialimeted Files (*.csv)|*.csv|');
in_file := client_Text_IO.Fopen(vfilename, 'r');
GO_BLOCK('Emp');
FIRST_RECORD;
LOOP
Client_Text_IO.Get_Line(in_file, linebuf);
p_output_line(linebuf);
Client_Text_IO.New_Line;
Next_record;
END LOOP;
FIRST_RECORD;
EXCEPTION
WHEN no_data_found THEN
Client_Text_IO.Put_Line('Closing the file...');
Client_Text_IO.Fclose(in_file);
END;
-------------------------------------------------------
PROCEDURE p_output_line(p_line varchar2) IS
vLINE VARCHAR2(4000);
vVALUE VARCHAR2(1000);
vCOMMA_COUNT NUMBER;
vREPORT_DATE DATE;
BEGIN
vLINE := p_line;
vCOMMA_COUNT := LENGTH(vLINE)- LENGTH(REPLACE(vLINE,',','')); -- COUNT THE NUMBER OF COMMAS
FOR I IN 1.. vCOMMA_COUNT+1 LOOP
vVALUE := SUBSTR(vLINE,1,INSTR(vLINE,',')-1); -- IF vLINE = 123,ABC,9877 THEN VVALUE WILL BE 123
IF vVALUE IS NULL THEN
vVALUE := vLINE;
END IF;
vLINE := SUBSTR(vLINE,INSTR(vLINE,',')+1) ; -- CHANGE 123,ABC,9877 TO BE ABC,9877
IF I = 1 THEN
:DATA.BMK_NAME := vVALUE;
ELSIF I = 2 THEN
vREPORT_DATE := last_day(to_date(vVALUE,'dd-mm-yyyy'));
:DATA.REPORT_DATE := vREPORT_DATE;
ELSIF I = 3 THEN
:DATA.BMK_RETURN := to_number(vVALUE);
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
MESSAGE('Please Check the data type is appropriate on you excel file');
MESSAGE('Please Check the data type is appropriate on you excel file');
END;
-----------------------------------------------------------------------
-- notes
1- you must install webutil version 106 or later
2- make sure that you attached and compiled the webutill.pll scucessfuly
You can just use webutil to show a filechooser to select the file and upload it to the application server. If you use a shared directory between the application server and the db server you can use an external table to show the input of the file to the user. And then after the button you just insert the data from the external table in another table.
