如何使用PLSQL读取数据库中的BLOB对象

使用PL/SQL从数据库中读取BLOB对象:

1.首先，确认现有对象

SQL>col fdesc for a30

SQL>select fid,fname,fdesc from eygle_blob

FID FNAMEFDESC

------------------------ ------------------------------

1 ShaoLin.jpg少林寺-康熙手书

2 DaoYing.jpg 倒映

2.创建存储Directory

SQL>connect / as sysdba

Connected.

SQL>create or replace directory BLOBDIR as 'D:oradataPic'

Directory created.

SQL>

SQL>grant read,write on directory BLOBDIR to eygle

Grant succeeded.

SQL>

3.创建存储过程

SQL>connect eygle/eygle

Connected.

SQL>

SQL>CREATE OR REPLACE PROCEDURE eygle_dump_blob (piname varchar2,poname varchar2) IS

2l_file UTL_FILE.FILE_TYPE

3l_bufferRAW(32767)

4l_amountBINARY_INTEGER := 32767

5l_pos INTEGER := 1

6l_blob BLOB

7l_blob_len INTEGER

8 BEGIN

9SELECT FPIC

10INTO l_blob

11FROM eygle_blob

12WHERE FNAME = piname

13

14l_blob_len := DBMS_LOB.GETLENGTH(l_blob)

15l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767)

16

17WHILE l_pos <l_blob_len LOOP

18 DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer)

19 UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE)

20 l_pos := l_pos + l_amount

21END LOOP

22

23UTL_FILE.FCLOSE(l_file)

24

25 EXCEPTION

26WHEN OTHERS THEN

27 IF UTL_FILE.IS_OPEN(l_file) THEN

28UTL_FILE.FCLOSE(l_file)

29 END IF

30 RAISE

31 END

32 /

Procedure created.

4.最后取出数据

SQL>host ls -l d:oradataPic

total 7618

-rwxrwxrwa 1 gqgai None2131553 Apr 19 10:12 DaoYing.jpg

-rwxrwxrwa 1 gqgai None1768198 Apr 19 10:12 ShaoLin.jpg

SQL>exec eygle_dump_blob('ShaoLin.jpg','01.jpg')

PL/SQL procedure successfully completed.

SQL>host ls -l d:oradataPic

total 11072

-rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg

-rwxrwxrwa 1 gqgai None2131553 Apr 19 10:12 DaoYing.jpg

-rwxrwxrwa 1 gqgai None1768198 Apr 19 10:12 ShaoLin.jpg

SQL>

SQL>exec eygle_dump_blob('DaoYing.jpg','02.jpg')

PL/SQL procedure successfully completed.

SQL>host ls -l d:oradataPic

total 15236

-rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg

-rwxrwxrwa 1 Administrators SYSTEM 2131553 Apr 26 07:19 02.jpg

-rwxrwxrwa 1 gqgai None2131553 Apr 19 10:12 DaoYing.jpg

-rwxrwxrwa

在OracleQueryBean类中增加一个函数，来进行读取，具体代码如下：

/**

* 根据图片在数据库中的ID进行读取

* @param strID 图片字段ID

* @param w 需要缩到的宽度

* @param h 需要缩到高度

* @return

*/

public byte[] GetImgByteById(String strID, int w, int h){

//System.out.println("Get img data which id is " + nID)

if(myConnection == null)

this.getConnection()

byte[] data = null

try {

Statement stmt = myConnection.createStatement()

ResultSet myResultSet = stmt.executeQuery("select " + this.strIDName + " from " + this.strTabName + " where " + this.strIDName + "=" + strID)

StringBuffer myStringBuffer = new StringBuffer()

if (myResultSet.next()) {

java.sql.Blob blob = myResultSet.getBlob(this.strImgName)

InputStream inStream = blob.getBinaryStream()

try {

long nLen = blob.length()

int nSize = (int) nLen

//System.out.println("img data size is :" + nSize)

data = new byte[nSize]

inStream.read(data)

inStream.close()

} catch (IOException e) {

System.out.println("获取图片数据失败,原因:" + e.getMessage())

}

data = ChangeImgSize(data, w, h)

}

System.out.println(myStringBuffer.toString())

myConnection.commit()

myConnection.close()

} catch (SQLException ex) {

System.out.println(ex.getMessage())

}

return data

}

import cx_Oracle

con = cx_Oracle.connect(‘username’, ‘password’, ‘dsn’)

blob_sql = "select column_name from table where clause"

cursor = con.cursor()

cursor.execute(blob_sql)

result = cursor.fetchall()

file = open('file_name', "wb")

file.write(result[0][0].read()) #可以print查看result的内容，根据实际情况read

file.close()

---