| how to upload TEXT FILE in a table? [message #86355] |
Thu, 23 September 2004 06:52  |
odeveloper
Messages: 34
Registered: September 2004
|
Member |
|
|
hi gurus!
i m facing a problem, i have different users and want to give them Developer 6i's form interface to upload their delimited TEXT FILES from their machines in a table, table is only one and all users are uploading their files.
once again, these files are on their own machines.
hope to receive a solution.
Regards.
|
|
|
|
| Re: how to upload TEXT FILE in a table? [message #86362 is a reply to message #86355] |
Fri, 24 September 2004 05:23  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Make a Form which uses TEXT_IO utility to read the file then write it to the Tables.
e.g. you may use the following code.Write it in a Procedure and call it from File Upload button created on your Form.:
/***
** Name : File_Upload
** Parameter : None
** Usage : Upload Text file into Database
** Called As : File_Upload
** Called from : When-Button-Pressed
***/
PROCEDURE File_Upload(P_File_Name Varchar2) IS
L_File TEXT_IO.File_Type;
L_Line_Buf Varchar2(2000);
L_Line_Count Number:=0;
L_Table_Rec Als.Als_Cse_Violations%Rowtype;
L_Error_Text Varchar2(100);
L_Commit_Point Number:=0;
L_Count_Rec Number:=0;
Begin
Set_Application_Property(Cursor_Style,'BUSY');
Begin
L_File := TEXT_IO.Fopen(P_File_Name, 'R');
Exception
When Others Then
Message('Error while opening file '||P_File_Name|| '.', No_Acknowledge);
Raise Form_Trigger_Failure;
End;
Loop
L_Line_Count := L_Line_Count + 1;
L_Count_Rec := L_Count_Rec + 1;
Begin
Text_Io.Get_Line(L_File, L_Line_Buf);
Exception
When No_Data_Found Then
Exit;
When Others Then
Message('Error while reading Line Number '||L_Line_Count||' from file '||P_File_Name|| '.', No_Acknowledge);
Raise Form_Trigger_Failure;
End;
Begin
L_Error_Text := 'Data Error while reading Last Name for Line '||L_Line_Count||'.';
L_Table_Rec.Last_Nm := Substr(L_Line_Buf , 1 , 20);
L_Error_Text := 'Data Error while reading First Name for Line '||L_Line_Count||'.';
L_Table_Rec.First_Nm := Substr(L_Line_Buf , 21 , 20);
L_Error_Text := 'Data Error while reading Middle Name for Line '||L_Line_Count||'.';
L_Table_Rec.Middle_Nm := Substr(L_Line_Buf , 41 , 17);
L_Error_Text := 'Data Error while reading DOB for Line '||L_Line_Count||'.';
If Check_DOB(Substr(L_Line_Buf , 61 , 10)) Then
L_Table_Rec.Dob := TO_DATE(Substr(L_Line_Buf , 61 , 10),'MM/DD/YYYY');
End If;
Exception
When Others Then
Message('E: '||L_Error_Text||' '||SQLERRM, No_Acknowledge);
Raise Form_Trigger_Failure;
End;
Begin
Insert Into MY_TABLE
(
Last_Nm ,
First_Nm ,
Middle_Nm ,
Dob ,
)
Values (
L_Table_Rec.Last_Nm ,
L_Table_Rec.First_Nm ,
L_Table_Rec.Middle_Nm ,
L_Table_Rec.Dob );
Exception
When Others Then
Message('Error while inserting record into Table MY_TABLE. '||SQLERRM, No_Acknowledge);
Raise Form_Trigger_Failure;
End;
FORMS_DDL('COMMIT');
End Loop;
If Form_Success Then
Message('File successfully uploaded.',NO_ACKNOWLEDGE);
FORMS_DDL('COMMIT');
Set_Application_Property(Cursor_Style,'DEFAULT');
Else
Message('File could not be uploaded successfully, please check the structure of file.',NO_ACKNOWLEDGE);
Raise Form_Trigger_Failure;
End If;
Text_IO.Fclose(L_File);
/***
** Exception Handling
***/
Exception
When Form_Trigger_Failure Then
Set_Application_Property(Cursor_Style, 'DEFAULT');
Text_IO.Fclose(L_File);
Raise Form_Trigger_Failure;
When Others Then
Set_Application_Property(Cursor_Style, 'DEFAULT');
Text_IO.Fclose(L_File);
Template.Show_Error('T');
End;
HTH
Regards
Himanshu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
