Oracle read/write file bfilename instance

Oracle file read/write operations are familiar to everyone. The following describes the Oracle file bfilename instance for reading and writing, hoping to help you learn how to read and write Oracle files.

Create directory allows us to flexibly read and write files in Oracle databases, greatly improving the ease of use and scalability of Oracle.
Its syntax is:
CREATE [or replace] DIRECTORY directory AS 'pathname ';

This case is created as follows:
Create or replace directory exp_dir as '/tmp ';
 
After the directory is created, you can grant the read and write permissions to specific users. The specific syntax is as follows:
Grant read [, WRITE] on directory directory TO username;

For example:
Grant read, write on directory exp_dir to eygle;
 
In this case, the user eygle has the permission to read and write the directory.

Let's look at a simple test:

 
 

  
  
SQL> create or replace directory UTL_FILE_DIR as '/opt/oracle/utl_file';  
  
  
Directory created.SQL> declare    
  
  
    fhandle utl_file.file_type;    
  
  
 begin    
  
  
    fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w');    
  
  
    utl_file.put_line(fhandle , 'eygle test write one');    
  
  
    utl_file.put_line(fhandle , 'eygle test write two');    
  
  
    utl_file.fclose(fhandle);    
  
  
 end;    
  
  
 /  
  
  
PL/SQL procedure successfully completed.  
  
  
SQL> !  
  
  
[oracle@jumper 9.2.0]$ more /opt/oracle/utl_file/example.txt   
  
  
eygle test write one  
  
  
eygle test write two  
  
  
[oracle@jumper 9.2.0]$ 
 
 

Similarly, you can use utl_file to read files:

 
 

  
  
SQL> declare    
  
  
    fhandle   utl_file.file_type;    
  
  
    fp_buffer varchar2(4000);    
  
  
  begin    
  
  
    fhandle := utl_file.fopen ('UTL_FILE_DIR','example.txt', 'R');    
  
  
      
  
  
    utl_file.get_line (fhandle , fp_buffer );    
  
  
    dbms_output.put_line(fp_buffer );    
  
  
    utl_file.get_line (fhandle , fp_buffer );   
  
  
    dbms_output.put_line(fp_buffer );   
  
  
    utl_file.fclose(fhandle);   
  
  
  end;   
  
  
  /  
  
  
eygle test write one  
  
  
eygle test write two  
  
  
PL/SQL procedure successfully completed. 
 
 

You can query dba_directories to view all directories.

 
 

  
  
SQL> select * from dba_directories;  
  
  
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH------------------------------ ------------------------------ ------------------------------  
  
  
SYS                            UTL_FILE_DIR                   /opt/oracle/utl_file  
  
  
SYS                            BDUMP_DIR                      /opt/oracle/admin/conner/bdump  
  
  
SYS                            EXP_DIR                        /opt/oracle/utl_file 
 
 

You can use drop directory to delete these paths.

 
 

  
  
SQL> drop directory exp_dir; Directory droppedSQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH too many SYS UTL_FILE_DIR/opt/oracle/utl_fileSYS BDUMP_DIR/opt/oracle/admin/conner/bdump
  
  

  
  
Create or replace directory USER_DIR as 'E: \ PLSQL \ 310 \';
  
  
 
  
  
DECLARE
  
  
V_content VARCHAR2 (1800 );
  
  
V_bfile BFILE;
  
  
Amount INT;
  
  
Offset INT: = 1;
  
  

  
  
BEGIN
  
  
V_bfile: = bfilename ('user _ dir', 'test. TXT '); -- note that the User_dir corresponds to the directory already created above.
  
  
Amount: = DBMS_LOB.getlength (v_bfile );
  
  
DBMS_LOB.OPEN (v_bfile );
  
  
DBMS_LOB.READ (v_bfile, amount, offset, v_content );
  
  
DBMS_LOB.close (v_bfile );
  
  
DBMS_OUTPUT.PUT_LINE (v_content );
  
  
END;
  
  

 
 

Oracle creates and deletes user instances

How to create an Oracle instance in unix

Minimum parameter requirements for Oracle instance Creation

Statement syntax for oracle time addition and subtraction

Oracle deadlock handling