BLOB support in SQL*Plus

Oracle 11g SQL*Plus client comes with an improvement - it now supports BLOB column; Now you can verify blob column content using sqlplus though you may not still be able to make a sense out it if the content is of type image or pdf etc.

Here is an example :-

$ sqlplus sipras

SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 14 14:47:30 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SIPRAS@11glab> create table test (id number, pic blob);

Table created.

SIPRAS@11glab> insert into test values (1,'122334');

1 row created.

SIPRAS@11GLAB > select * from test;

ID
----------
PIC
----------------------------------------------------------------------------------------------------
1
122334


1 row selected.

- An example using image file being loaded into blob column

SIPRAS@11glab> create or replace directory blobdir as '/tmp';

Directory created.

SIPRAS@11glab>insert into test values(2, utl_raw.cast_to_raw('testing blob'));

SIPRAS@11GLAB > select * from test;

ID
----------
PIC
----------------------------------------------------------------------------------------------------
1
122334

2
74657374696E6720626C6F62


2 rows selected.

-- anonymous block to load the content of an image file to the blob

SIPRAS@11glab>DECLARE
vblob    BLOB;
vbfile   BFILE   := BFILENAME ('BLOBDIR', '10gocplogo.jpg');
vamt     INTEGER;
VSIZE    INTEGER;
BEGIN
SELECT     pic
     INTO vblob
     FROM TEST
    WHERE ID = 1
FOR UPDATE;

DBMS_LOB.fileopen (vbfile);
VSIZE := DBMS_LOB.getlength (vbfile);
DBMS_OUTPUT.put_line ('Size of input file: ' || VSIZE);
DBMS_LOB.loadfromfile (vblob, vbfile, VSIZE);
DBMS_OUTPUT.put_line ('After loadfromfile');
VSIZE := DBMS_LOB.getlength (vblob);
DBMS_OUTPUT.put_line ('Size of blob: ' || VSIZE);
EXCEPTION
WHEN OTHERS
THEN
  DBMS_OUTPUT.put_line ('An error occurred');
  DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
END;
/

Size of input file: 15054
After loadfromfile
Size of blob: 15054

PL/SQL procedure successfully completed. 

SQL> select * from test;

ID
----------
PIC
----------------------------------------------------------------------------------------------------
1
FFD8FFE000104A46494600010201012C012C0000FFED002C50686F746F73686F7020332E30003842494D03ED000000000010
012C000000010001012C000000010001FFEE000E41646F62650064C00000

2
74657374696E6720626C6F62

2 rows selected.

I know that you cannot interpret the values since it was an image file but still just wanted to show that you will not get "SP2-0678 Column or attribute type can not be displayed by SQL*Plus" error any more.

However, if you try the same thing from 10g client it won't work and you will get the SP2-0678 error :-

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 14 13:21:11 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SIPRAS@11glab> truncate table test;
Table truncated.

SIPRAS@11glab> insert into test values (1,'122334');

1 row created.

SIPRAS@11glab> select * from test;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus