Solved

Inserting an Oracle Blob image into a Crystal Report

Posted on 2002-06-16
4
2,514 Views
Last Modified: 2012-06-27
I'm having trouble with Crystal Reports 8.5. I am trying to insert a field into my report which maps
to a blob image column in Oracle.

The source inside the report is simply a database table consisting of a VARCHAR2 id field and a BLOB
field. When I drag the id field into the report it works fine. However when I go to drag the Blob field,
the error "No rowset was returned for this table, query, or procedure".

Oracle version 8.1.7.0.0
Crystal version 8.5
Connecting the two using a "Microsoft OLE DB Provider for Oracle" connection.

In a document from Crystal Decisions, it specifies that from version 8 onwards, Crystal can read Long
Raw and Blob fields.

I have heard that there may be an error on the Oracle end which has been fixed in Release 3 of Oracle
8i. I am trying to find out if this is true.

Any clues?
0
Comment
Question by:doctorkimball
  • 2
4 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 7083052
I can only explain how to do this in Oracle environment. This will give you the way you can construct
the needed procedures. Be aware that BLOBS need special ppackages and program constructs because often they are stored outside the Oracle schema= The following text will orient you how to manipulate LOBs.

This is a solution 9Oracle environment):

SQL> create directory MYDIR as 'C:\MYIMAGE';

Directory created.

SQL> create table special(a number, b varchar2(20), c blob);

Table created.

SQL>
SQL> insert into special values(1,'abcd',empty_blob());

1 row created.

SQL>
SQL> commit;

Commit complete.


create or replace procedure load(id number)
is
dest_lob blob;
src_lob bfile:=bfilename('MYDIR','img.bmp');
amt integer:=sys.dbms_lob.getlength(src_lob);
begin
select c into dest_lob from special
where a=id for update;
dbms_lob.fileopen(src_lob);
dbms_lob.loadfromfile(dest_lob,src_lob,amt);
dbms_lob.fileclose(Src_lob);
commit;
end;
/

SQL> @ blob_insert

Procedure created.

SQL> exec load(1);

PL/SQL procedure successfully completed.

SQL> select a from special;

A




The LONG datatypes are accessible via subprograms from the DBMS_LOB package.
The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs,NCLOBs, BFILEs, and temporary LOBs.




You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs.

For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND




exception:

DECLARE
fil BFILE;
pos INTEGER;
amt BINARY_INTEGER;
buf RAW(40);
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
dbms_lob.open(fil, dbms_lob.lob_readonly);
amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := '';
dbms_lob.read(fil, amt, pos, buf);
dbms_output.put_line('Read F1 past EOF: '||
utl_raw.cast_to_varchar2(buf));
dbms_lob.close(fil);
exception
WHEN no_data_found
THEN
BEGIN
dbms_output.put_line('End of File reached. Closing file');
dbms_lob.fileclose(fil);
-- or dbms_lob.filecloseall if appropriate
END;
END;
/
Statement processed.
End of File reached. Closing file



PURPOSE:
========
How to create LOB objects and differences compared with LONG RAW behavior.
Working with LOB objects in Forms 6.0.


SCOPE & APPLICATION:
====================
This article explains the basic use and advantages of the Oracle 8 LOB
column type recognized in Developer 6.0 and above.


RELATED DOCUMENTS:
==================
Note:66046.1, Note:1038856.6, Bug:804091, BUG:831105


Oracle Developer 6 To Oracle 8 NEW DATA TYPES
=============================================
The new table column TYPE's (constructs):

BLOB - binary data
CLOB - single byte character data, fixed width (8.1 fixed or variable width).
NCLOB - multi-byte character, fixed width (8.1 fixed or variable width)
BFILE - binary file stored to O/S.

Advantages of new column TYPE

1) Differences (Note:66046.1 Hints:  Oracle8 - New Constructs)
2) Multiple LOB's and BFILE columns (one LONG RAW column per table).
3) LOB's support object types.
4) Replication can be used with LOB's.
5) LOB's stored in a way that optimizes space and efficient access and participate in transaction model


of the server
6) BFILE's (external LOB) stored in O.S. files outside of database, so do not participate in transactions
7) LOB maximum size is 4GB, a LONG RAW maximum is 2GB.
 
Example:  
Create the table.
SQL*Plus simple create statement:
 
  Create table mylobdata  
  (refno number,
  myblob1 BLOB,
  myclob1 CLOB,
  mynclob1 NCLOB,
  mybfile1 BFILE);

Manipulate LOB's in SQLPLUS - NO !
1) It is not possible to query back LOB data.
Lets say you need to test/manipulate BLOB without using forms. PL/SQL provides package
called DBMS_LOB for this (see Note:61737.1)

2) Cannot alter type LONG to LOB and visa versa (Note:1038856.6), so do this :-
ALTER TABLE table_name ADD (new_column_name new_datatype);
UPDATE table_name SET new_column_name = old_column_name;

BLOB - IMAGES
=============
BLOB properties :
- Item Type -> Image
- Image format -> GIF, TIF , BMP...etc. Determines NOT incoming image type but storage format in database


!
- Load in from file usinge read_image_file() used as normal to process the image on the file system.

GOTCHAS !
=========
Create all table BLOB columns as outset otherwise you may hit bug:804091. This bug meant
that if you create table with one BLOB column then add another BLOB column at a later date, forms
will save images only to first column.

BLOB - SOUND
============
BLOB properties :
- Item type -> Sound
- sound format -> eg. WAVE
- Load (wav) file from file
- read_sound_file() as usual to process the wav file.

BLOB - OLE
==========
BLOB properties :
- Item Type -> OLE container
- Insert object ...etc. Just the same as with LONG RAW columns.

CLOB - text
===========
CLOB Properties :
- Item Type -> Text Item
Problems :
- Query on CLOB item criteria does not work in form

NCLOB - text
============
NCLOB properties :
Item Type -> Text Item
Problems :
- Cannot insert or query data in form, BUG:831105.
NB: To insert from SQLPLUS :-
INSERT INTO <CLOBTABLE> VALUES
(N'mydata');

BFILE
=====
- The files are stored on the server as BFILE  (binary file) column type.
- The directory alias and file name is held in the BFILE column.
Forms 6 can display the image (say) directly, using the directory alias and filename held in  the BFILE


column.
With Forms 5 you would use dbms_lob() to identify the file and READ_IMAGE_FILE() to display it (Note:66312.1).
Problems:
- You can't load BFILE data directly using Forms 6. So, in SQL Plus do the following :
GRANT CREATE ANY DIRECTORY TO SCOTT;
CREATE OR REPLACE DIRECTORY MYDIR AS 'D:\TEMP2';
CREATE TABLE MY_BFILETABLE
  (REFNO NUMBER, PHOTO BFILE);
INSERT INTO MY_BFILETABLE VALUES (1,BFILENAME('MYDIR','A_PHOTO.BMP'));






How to read image (in rcle environment):

CREATE OR REPLACE PACKAGE BODY myblob AS

FUNCTION get_blob_len
      RETURN NUMBER IS
BEGIN
      RETURN dbms_lob.getlength(my_blob);
END;

PROCEDURE read_blob
              (
              amount          IN OUT NUMBER,
              offset          IN NUMBER,
              buf             IN OUT RAW
              ) IS
BEGIN
      dbms_lob.read(my_blob,amount,offset,buf);
END;

FUNCTION load
              (
              filename IN VARCHAR2
              )
              RETURN INTEGER AS
lobd       BLOB;
fils       BFILE := BFILENAME('MYBLOBS', filename);
amt        INTEGER;
id         INTEGER;
BEGIN
      select myblobs_s.nextval
          into id
          from dual;
      dbms_lob.fileopen(fils, dbms_lob.file_readonly);
      amt:=dbms_lob.getlength(fils);
      insert into myblobs values (id, empty_blob())
              returning theblob into lobd;
      dbms_lob.loadfromfile(lobd, fils, amt);
      commit;
      dbms_lob.fileclose(fils);
     
      return(id);

END;


FUNCTION find
              (
              iFind   IN INTEGER
              )
              RETURN INTEGER is
BEGIN
      select theblob
        into my_blob
        from myblobs
       where id = iFind;
      return (1);
EXCEPTION
      when no_data_found then
              return (-1);
END;

END;
/


Run this as a user who has privelige to create a directory, and access to dbms_lob.
You will be prompted to provide a value for 'blob_dir'.  This is the physical
directory on the server that you will load your sample image files from.

Generally speaking you have to use the DBMS_LOB package:
This package provides general purpose routines for operations
   on Oracle Large OBject (LOBs) datatypes - BLOB, CLOB (read-write)
   and BFILEs (read-only).
   
   Oracle 8.0 SQL supports the definition, creation, deletion, and
   complete updates of LOBs. The main bulk of the LOB operations
   are provided by this package.

----------------------
RULES AND LIMITATIONS
----------------------
   The following rules apply in the specification of functions and
   procedures in this package.

   LENGTH and OFFSET parameters for routines operating on BLOBs and
   BFILEs are to be specified in terms of bytes.
   LENGTH and OFFSET parameters for routines operating on CLOBs
   are to be specified in terms of characters.
   
   A function/procedure will raise an INVALID_ARGVAL exception if the
   the following restrictions are not followed in specifying values
   for parameters (unless otherwise specified):

   1. Only positive, absolute OFFSETs from the beginning of LOB data
      are allowed. Negative offsets from the tail of the LOB are not
      allowed.
   2. Only positive, non-zero values are allowed for the parameters
      that represent size and positional quantities such as AMOUNT,
      OFFSET, NEWLEN, NTH etc.
   3. The value of OFFSET, AMOUNT, NEWLEN, NTH must not exceed the
      value lobmaxsize (which is (4GB-1) in Oracle 8.0) in any DBMS_LOB
      procedure or function.
   4. For CLOBs consisting of fixed-width multi-byte characters, the
      maximum value for these parameters must not exceed
            (lobmaxsize/character_width_in_bytes) characters
      For example, if the CLOB consists of 2-byte characters such as
      JA16SJISFIXED, then the maximum amount value should not exceed
            4294967295/2 = 2147483647 characters

   PL/SQL language specifications stipulate an upper limit of 32767
   bytes (not characters) for RAW and VARCHAR2 parameters used in
   DBMS_LOB routines.
   
   If the value of AMOUNT+OFFSET exceeds 4GB (i.e. lobmaxsize+1) for
   BLOBs and BFILEs, and (lobmaxsize/character_width_in_bytes)+1 for
   CLOBs in calls to update routines - i.e. APPEND, COPY, TRIM, and
   WRITE routines, access exceptions will be raised. Under these input
   conditions, read routines such as READ, COMPARE, INSTR, SUBSTR, will
   read till End of Lob/File is reached.
   For example, for a READ operation on a BLOB or BFILE, if the user
   specifies offset value of 3GB, and an amount value of 2 GB, READ
   will read only ((4GB-1) - 3GB) bytes.

   Functions with NULL or invalid input values for parameters will
   return a NULL. Procedures with NULL values for destination LOB
   parameters will raise exceptions.  

   Operations involving patterns as parameters, such as COMPARE, INSTR,
   and SUBSTR do not support regular expressions or special matching
   characters (such as % in the LIKE operator in SQL) in the PATTERN
   parameter or substrings.

   The End Of LOB condition is indicated by the READ procedure using
   a NO_DATA_FOUND exception. This exception is raised only upon an
   attempt by the user to read beyond the end of the LOB/FILE. The
   READ buffer for the last read will contain 0 bytes.
     
   For consistent LOB updates, the user is responsible for locking
   the row containing the destination LOB before making a call to
   any of the procedures (mutators) that modify LOB data.
   
   For BFILEs, the routines COMPARE, INSTR, READ, SUBSTR, will raise
   exceptions if the file is not already opened using FILEOPEN.

---------
SECURITY
---------

   Privileges are associated with the the caller of the procedures/
   functions in this package as follows:
   If the caller is an anonymous PL/SQL block, the procedures/functions
   are run with the privilege of the current user.
   If the caller is a stored procedure, the procedures/functions are run
   using the privileges of the owner of the stored procedure.

-------------------------
PROCEDURES AND FUNCTIONS
-------------------------
   Procedures and functions are listed here but do not have separate
   notes as there is plenty of existing documentation on the use of
   the PL/SQL DBMS_LOB routines.

PROCEDURE append(dest_lob IN OUT NOCOPY BLOB, src_lob  IN BLOB);
PROCEDURE close(lob_loc IN OUT NOCOPY BLOB);
FUNCTION  compare(lob_1    IN CLOB CHARACTER SET ANY_CS, ... );
PROCEDURE copy(dest_lob    IN OUT NOCOPY BLOB, ... );
PROCEDURE createtemporary(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE erase(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE fileclose(file_loc IN OUT NOCOPY  BFILE);
PROCEDURE filecloseall;
FUNCTION  fileexists(file_loc IN BFILE)
PROCEDURE filegetname(file_loc  IN  BFILE, ... );
FUNCTION  fileisopen(file_loc IN BFILE)
PROCEDURE fileopen(file_loc  IN OUT NOCOPY  BFILE, ... );
PROCEDURE freetemporary(lob_loc IN OUT NOCOPY  BLOB);
FUNCTION  getchunksize(lob_loc IN BLOB) /* Oracle8i only */
FUNCTION  getlength(lob_loc IN BLOB)
FUNCTION  istemporary(lob_loc IN BLOB)
FUNCTION  isopen(lob_loc in blob)
PROCEDURE loadfromfile(dest_lob    IN OUT NOCOPY  BLOB, ... );
PROCEDURE open(lob_loc   IN OUT NOCOPY BLOB, ... );
FUNCTION  instr(lob_loc IN BLOB, ... );
PROCEDURE read(lob_loc IN            BLOB, ... );
FUNCTION  substr(lob_loc IN BLOB, ... );
PROCEDURE trim(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE write(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE writeappend(lob_loc IN OUT NOCOPY  BLOB, ... );
0
 
LVL 3

Accepted Solution

by:
rkogelhe earned 400 total points
ID: 7083505
I think some versions of the MS OLEDB don't support BLOBs.

Try the Oracle one, but be aware that it does not behave exactly the same.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244661&ID=kb;en-us;Q244661

"Oracle 8.x-specific data types, such as CLOB, BLOB, BFILE, NCHAR, NCLOB, and NVARCHAR2, are not supported."
0
 

Author Comment

by:doctorkimball
ID: 7084671
Thanks rkogelhe,

I guess my option now is to try and insert a LONG RAW field rather than a BLOB. LONG RAW was the precursor to Blobs in Oracle 7 right?
0
 
LVL 3

Expert Comment

by:rkogelhe
ID: 7086708
Sounds like it's either convert to the Oracle OLEDB driver or use long raw. Perhaps you can play with some sort of trigger that converts the long raw to a blob.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now