Oracle read/write file bfilename [instance]

Source: Internet
Author: User
Oracle read/write file bfilename [instance]
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
2 fhandle utl_file.file_type;
3 begin
4 fhandle: = utl_file.fopen ('utl _ FILE_DIR ', 'example.txt', 'w ');
5 utl_file.put_line (fhandle, 'eygle test write one ');
6 utl_file.put_line (fhandle, 'eygle test write two ');
7 utl_file.fclose (fhandle );
8 end;
9/
PL/SQL procedure successfully completed.
SQL>!
[Oracle @ jumper 9.2.0] $ more/opt/oracle/utl_file/example.txt eygle test write oneeygle test write two [oracle @ jumper 9.2.0] $
 

Similarly, you can use utl_file to read files:
SQL> declare
2 fhandle utl_file.file_type;
3 fp_buffer varchar2 (4000 );
4 begin
5 fhandle: = utl_file.fopen ('utl_file_dir', 'example.txt ', 'R ');
6
7 utl_file.get_line (fhandle, fp_buffer );
8 dbms_output.put_line (fp_buffer );
9 utl_file.get_line (fhandle, fp_buffer );
10 dbms_output.put_line (fp_buffer );
11 utl_file.fclose (fhandle );
12 end;
13/
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_fileSYS
BDUMP_DIR/opt/oracle/admin/conner/bdumpSYS EXP_DIR/opt/oracle/utl_file
 

You can use drop directory to delete these paths.
SQL> drop directory exp_dir;
Directory dropped
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------------------------------------------------------------------
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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.