Oracle directly reads text file data

Technorati label: Oracle

Oracle uses utl_file package
Create or replace procedure loadfiledata (p_path varchar2, p_filename varchar2) is
V_filehandle utl_file.file_type; -- defines a file handle.
V_text varchar2 (100); -- stores text
V_name test. name % type;
V_id test. autoid % type;
V_firstlocation number;
V_secondlocation number;
V_totalinserted number;
Begin
If (p_path is null or p_filename is null) then
Goto to_end;
End if;
V_totalinserted: = 0;
/* Open specified file */
V_filehandle: = utl_file.fopen (p_path, p_filename, 'R ');
Loop
Begin
Utl_file.get_line (v_filehandle, v_text );
Exception
When no_data_found then
Exit;
End;
V_firstlocation: = instr (v_text, ',', 1, 1 );
V_id: = substr (v_text, 1, v_firstlocation-1 );
V_name: = substr (v_text, v_firstlocation + 1 );
/* Insert database operation */
Insert into test
Values (v_id, v_name );
Commit;
End loop;
<To_end>;
NULL;
End loadfiledata;
==================== Create a table
3. Test Environment
First, create a target table to store the data in the file:
Create Table Test (
Autoid varchar2 (10 );
Name varchar2 (20 ));
======================================
Declare
V_path varchar2 (200 );
V_filename varchar2 (200 );
Begin
V_path: = 'f :\';
V_filename: Invalid 'address information .txt ';
Loadfiledata (v_path, v_filename );
End;
/
/* Because the Oracle database has a security management problem on the directory created by the package, not all file directories can be accessed by the utl_file package,
To update the directory settings, set the utl_file_dir field in init. ora to *, so that the utl_file package can access all the directory files. The utl_file_dir setting is correct */
===== Error! ======
ORA-06510: PL/SQL: User-defined exception events that cannot be processed
ORA-06512: In "SYS. utl_file", Line 98
ORA-06512: In "SYS. utl_file", line 157
ORA-06512: In "system. loadfiledata2", line 15
ORA-06512: In line 1
============================
Create a target table to store the data in the file! No! Help me! In the following example, you can write data to a text file!
==============================
Declare
File_handle utl_file.file_type;
Begin
File_handle: = utl_file.fopen ('C: \ temp', 'sss.txt ', 'A ');
Utl_file.put_line (file_handle, 'Write information ');
Utl_file.fclose (file_handle );
End;
==========
Add utl_file_dir = c: \ Temp to init. ora.
Just start again.