Oracle PL/SQL/Data Type/blob
Содержание
A PL/SQL block to read an operating system file called BLOB.JPG that contains 1 row of binary data.
<source lang="sql">
SQL> SQL> SQL> create table myTable(
2 c1 number primary key, 3 c2 blob 4 );
Table created. SQL> SQL> SQL> DECLARE
2 v_raw RAW(32767); 3 v_file_id UTL_FILE.FILE_TYPE; 4 BEGIN 5 v_file_id:= 6 UTL_FILE.FOPEN("c:\","blob.jpg","r",32767); 7 UTL_FILE.GET_RAW (v_file_id,v_raw); 8 INSERT INTO myTable VALUES (1,v_raw); 9 UTL_FILE.FCLOSE( v_file_id); 10 END; 11 /
SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
Blob type column
<source lang="sql">
SQL> create table myTable
2 (c1 number primary key, 3 c2 blob);
Table created. SQL> SQL> drop table myTable; Table dropped.
</source>
Initialize blob type data
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 blob_column BLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE initialize_blob(blob_par IN OUT BLOB,id_par IN INTEGER)IS
2 BEGIN 3 SELECT blob_column 4 INTO blob_par 5 FROM myTable 6 WHERE id = id_par; 7 END initialize_blob; 8 /
Procedure created. SQL> SQL> drop table myTable; Table dropped.
</source>
Read blob type data, DBMS_LOB.READ
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 blob_column BLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE initialize_blob(blob_par IN OUT BLOB,
2 id_par IN INTEGER 3 ) IS 4 BEGIN 5 SELECT blob_column 6 INTO blob_par 7 FROM myTable 8 WHERE id = id_par; 9 END initialize_blob; 10 /
Procedure created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE read_blob_example(id_par IN INTEGER) IS
2 blobValue BLOB; 3 binary_buffer_var RAW(25); 4 offset INTEGER := 1; 5 amount_var INTEGER := 25; 6 BEGIN 7 initialize_blob(blobValue, id_par); 8 DBMS_LOB.READ(blobValue, amount_var, offset, binary_buffer_var); 9 DBMS_OUTPUT.PUT_LINE("binary_buffer_var = " || binary_buffer_var); 10 DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var); 11 END read_blob_example; 12 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
Read data in for sql statement
<source lang="sql">
SQL> SQL> SQL> select lpad(" ",2*level) || operation || " " || options
2 || " " || object_name as "Execution Plan" 3 from plan_table 4 where statement_id = "&&1" 5 connect by prior id = parent_id and statement_id = "&1" 6 start with id = 1;
Enter value for 1: old 4: where statement_id = "&&1" new 4: where statement_id = "" old 5: connect by prior id = parent_id and statement_id = "&1" new 5: connect by prior id = parent_id and statement_id = "" no rows selected SQL>
</source>