в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:58Z

1 версия

Новая страница

== BFILE column and directory==



<source lang="sql">
SQL> CREATE TABLE myBFile
2 (id NUMBER PRIMARY KEY,
3 bfile_data BFILE);
Table created.
SQL>
SQL> CREATE DIRECTORY bfile_dir AS "c:\proj";

SQL>
SQL> DECLARE
2 bfileValue BFILE;
3 BEGIN
4 INSERT INTO myBFile VALUES (1,BFILENAME("BFILE_DIR","test.bmp"));
5 SELECT bfile_data
6 INTO bfileValue
7 FROM myBFile
8 WHERE id = 1;
9
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table myBFile;
Table dropped.
SQL></source>

== BFILE type column==



<source lang="sql">
SQL> CREATE TABLE myBFile
2 (id NUMBER PRIMARY KEY,
3 bfile_data BFILE);
Table created.
SQL>
SQL> INSERT INTO myBFile VALUES (1,null);
1 row created.
SQL>
SQL>
SQL> drop table myBFile;
Table dropped.
SQL></source>

== BFILE value and BFILENAME function==



<source lang="sql">
SQL> CREATE TABLE myBFile
2 (id NUMBER PRIMARY KEY,
3 bfile_data BFILE);
Table created.
SQL>
SQL> INSERT INTO myBFile VALUES (1,BFILENAME("BFILE_DIR","test.bmp"));
1 row created.
SQL>
SQL>
SQL> drop table myBFile;
Table dropped.
SQL></source>

== Create a BFILE locator==



<source lang="sql">
SQL>
SQL> CREATE TABLE facebook (
2 name VARCHAR2(80),
3 photo BLOB,
4 directions CLOB,
5 description NCLOB,
6 web_page BFILE);
Table created.
SQL>
SQL> CREATE DIRECTORY bfile_data AS "c:\xxx";
Directory created.
SQL>
SQL>
SQL> DECLARE
2 web_page BFILE;
3 BEGIN
4 DELETE FROM facebook WHERE name="Tannery Falls";
5
6 web_page := BFILENAME("BFILE_DATA","Tannery Falls.htm");
7
8 INSERT INTO facebook (name, web_page) VALUES ("Tannery Falls",web_page);
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table facebook;
Table dropped.
SQL> drop directory bfile_data;
Directory dropped.
SQL></source>

== Creating Tables Containing BFILE Objects==



<source lang="sql">
SQL>
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY,
3 bfile_column BFILE NOT NULL
4 );
Table created.
SQL>
SQL> desc myTable;
Name Null? Type
---------------
ID NOT NULL NUMBER(38)
BFILE_COLUMN NOT NULL BINARY FILE LOB
SQL>
SQL> drop table myTable;
Table dropped.</source>

== Populating a BFILE Column with a Pointer to a File==

You use the Oracle database"s BFILENAME() function to populate the BFILE column with a pointer to your external file.
The BFILENAME() function accepts two parameters:
the database directory object"s name (which was created earlier) and the name of the file.
34. 2. BFILE
34. 2. 1.
<A href="/Tutorial/Oracle/0660__Large-Objects/CreatingTablesContainingBFILEObjects.htm">Creating Tables Containing BFILE Objects</a>
34. 2. 2.
Populating a BFILE Column with a Pointer to a File
34. 2. 3.
<A href="/Tutorial/Oracle/0660__Large-Objects/PopulatingBFILE.htm">Populating BFILE</a>
34. 2. 4.
<A href="/Tutorial/Oracle/0660__Large-Objects/BFILEtypecolumn.htm">BFILE type column</a>
34. 2. 5.
<A href="/Tutorial/Oracle/0660__Large-Objects/BFILEvalueandBFILENAMEfunction.htm">BFILE value and BFILENAME function</a>
34. 2. 6.
<A href="/Tutorial/Oracle/0660__Large-Objects/BFILEcolumnanddirectory.htm">BFILE column and directory</a>
34. 2. 7.
<A href="/Tutorial/Oracle/0660__Large-Objects/CreateaBFILElocator.htm">Create a BFILE locator</a>
34. 2. 8.
<A href="/Tutorial/Oracle/0660__Large-Objects/RetrievetheLOBlocater.htm">Retrieve the LOB locater</a>
34. 2. 9.
<A href="/Tutorial/Oracle/0660__Large-Objects/UseaBFILEtoloadaLOBcolumn.htm">Use a BFILE to load a LOB column</a>

== Populating BFILE==

Oracle accesses files on the server by using a directory.
A directory is just a pointer to an operating system folder.
Assuming that a folder C:\IO exists on your server, and you want to call that folder IO within Oracle:
create directory IO as "C:\IO";
grant read, write on directory IO to public;
Now, when you refer to IO in any commands, you"re referring to the C:\IO folder in the file system.
To create a pointer to the file on the server and place that pointer in the table on an existing record



<source lang="sql">
SQL> --1: Creating a Pointer
SQL>
SQL> create table catalog
2 (id number,
3 name VARCHAR2(2000),
4 manual_cl CLOB,
5 firstpage_bl BLOB,
6 mastertxt_bf BFILE
7 );
Table created.
SQL>
SQL> create directory IO as "C:\IO";
Directory created.
SQL> --grant read, write on directory IO to public;
SQL>
SQL>
SQL> declare
2 v_bf BFILE;
3 begin
4 v_bf:=BFILENAME ("IO", "text.htm");
5 insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", v_bf);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table catalog;
Table dropped.</source>

== Retrieve the LOB locater==



<source lang="sql">
SQL>
SQL> CREATE TABLE waterfalls (
2 falls_name VARCHAR2(80),
3 falls_photo BLOB,
4 falls_directions CLOB,
5 falls_description NCLOB,
6 falls_web_page BFILE);
Table created.
SQL>
SQL> CREATE DIRECTORY bfile_data AS "c:\xxx";
Directory created.
SQL>
SQL> DECLARE
2 web_page BFILE;
3 html RAW(60);
4 amount BINARY_INTEGER := 60;
5 offset INTEGER := 1;
6 BEGIN
7
8 SELECT falls_web_page
9 INTO web_page
10 FROM waterfalls
11 WHERE falls_name="Tannery Falls";
12
13 DBMS_LOB.OPEN(web_page);
14 DBMS_LOB.READ(web_page, amount, offset, html);
15 DBMS_LOB.CLOSE(web_page);
16
17 DBMS_OUTPUT.PUT_LINE(RAWTOHEX(html));
18
19 DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(html));
20 END;
21 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table waterfalls;
Table dropped.
SQL> drop directory bfile_data;
Directory dropped.
SQL></source>

== Use a BFILE to load a LOB column==



<source lang="sql">
SQL>
SQL> CREATE TABLE facebook (
2 name VARCHAR2(80),
3 photo BLOB,
4 directions CLOB,
5 description NCLOB,
6 web_page BFILE);
Table created.
SQL>
SQL> CREATE DIRECTORY bfile_data AS "c:\xxx";
Directory created.
SQL>
SQL>
SQL> DECLARE
2 myBFile BFILE := BFILENAME("BFILE_DATA","TanneryFalls.directions");
3 directions CLOB;
4 destination_offset INTEGER := 1;
5 source_offset INTEGER := 1;
6 language_context INTEGER := DBMS_LOB.default_lang_ctx;
7 warning_message INTEGER;
8 BEGIN
9
10 DELETE FROM facebook WHERE name="Falls";
11
12 INSERT INTO facebook (name,directions)VALUES ("Falls",EMPTY_CLOB());
13
14 SELECT directions INTO directions FROM facebook WHERE name="Falls";
15
16 DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
17 DBMS_LOB.OPEN(myBFile);
18
19 DBMS_LOB.LOADCLOBFROMFILE(directions, myBFile,
20 DBMS_LOB.LOBMAXSIZE,
21 destination_offset, source_offset,
22 NLS_CHARSET_ID("US7ASCII"),
23 language_context, warning_message);
24
25 IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
26 dbms_output.put_line("Warning! Some characters couldn""t be converted.");
27 END IF;
28
29 DBMS_LOB.CLOSE(directions);
30 DBMS_LOB.CLOSE(myBFile);
31 END;
32 /
DECLARE
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified.
ORA-06512: at "SYS.DBMS_LOB", line 716
ORA-06512: at line 17

SQL>
SQL>
SQL>
SQL> drop table facebook;
Table dropped.
SQL> drop directory bfile_data;
Directory dropped.
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Large Objects/BFILE - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия