How can i retrieve blob columns from a procedure

[rite2sekhar]

Hi Friends
1)I am having a table X containaing images in a column BLOB How can I retrieve (display) the images thru a procedure

2)what is a temp table
3)Difference between temp table and Cursor

Please help me

regds
Sekhar

[sanereddy]

Please find the answers for your questions. Let me know if you need any.

Question1:

For the following examples, assume you have this table:

create table emp (
ename VARCHAR2(50),
salary NUMBER,
job VARCHAR2(50),
department INTEGER,
photo_id ORDSYS.ORDIMGB,
large_photo ORDSYS.ORDIMGF);


The following is an example of querying the row that has Image BFILE data. You must create a table alias (E in this example) when you refer to a type in a SELECT statement.

SELECT ename, E.photo_id.width
FROM emp E
WHERE ename = \'John Doe\' and
E.photo_id.width > 32 and
E.photo_id.fileFormat=\'GIFF\';


The following is an example of querying the row that has Image LOB data:

SELECT ename, E.large_photo.compressionFormat
FROM emp E
WHERE ename = \'John Doe\' and
E.large_photo.width > 32 and
E.large_photo.fileFormat=\'GIFF\' and
E.large_photo.compressionFormat=\'GIFLZW\';

2.8 Copying an Image from a BFILE to a BLOB Type
To copy the data from an Image BFILE type to an Image BLOB type, you would use the ORDImgF.copyContent method. For example, the following program copies image data from an Image BFILE type to an Image BLOB type:

DECLARE
BLOBImage ORDSYS.ORDIMGB;
BFILEImage ORDSYS.ORDIMGF;
BEGIN
SELECT photo_id,large_photo
INTO BLOBImage,FILEImage
FROM emp where ename = \'John Doe\' for UPDATE;

-- Copy the BFILE image to the BLOB image
BFILEImage.copyContent(BLOBImage.content);

-- Set the BLOB image properties
BLOBImage.setProperties;

-- continue processing

-- update the row
UPDATE emp
SET photo_id = BLOBImage
where ename = \'John Doe\';

END

2.9 Copying an Image from a BLOB to a BLOB Type
To copy the data between two Image BLOB types, use the ORDImgB.copyContent method. For example, the following program copies image data from an Image BLOB type to another Image BLOB type:

DECLARE
Image_1 ORDSYS.ORDIMGB;
Image_2 ORDSYS.ORDIMGB;
BEGIN
SELECT photo_id
INTO Image_1
FROM emp where ename = \'John Doe\';

SELECT photo_id
INTO Image_2
FROM emp where ename = \'Also John Doe\' for UPDATE;

-- copy the data from Image_1 to Image_2
Image_1.copyContent(Image_2.content);

-- set the image properties for Image_2
Image_2.setProperties;

-- continue processing

UPDATE emp
SET photo_id = Image_2
WHERE ename = \'Also John Doe\';

END

2.10 Converting an Image\'s Format
To convert the image data into a different format, use the Process method. For example, the following program converts the image data to the TIFF file format:

DECLARE
Image ORDSYS.ORDIMGB;
BEGIN
SELECT photo_id
INTO Image
FROM emp
WHERE ename = \'John Doe\' for UPDATE;

-- convert the image to TIFF in place
Image.process(\'fileFormat=TIFF\');

END

2.11 Copying and Converting in One Step
To make a copy of the image and convert it into one step, use the processCopy method. For example, the following program converts the image data to the TIFF image file format, but leaves the original image intact:

DECLARE
Image_1 ORDSYS.ORDIMGB;
Image_2 ORDSYS.ORDIMGB;
BEGIN
SELECT photo_id
INTO Image_1
FROM emp
WHERE ename = \'John Doe\' for UPDATE;

-- convert the image to tiff and store the result in Image_2
Image_2 := Image_1;
Image_1.processCopy(\'fileFormat=TIFF\',Image_2.content);

-- continue processing

END

Changes made by these methods can be rolled back. This technique may be useful for a temporary format conversion


Question 2: what is a temp table

The data in a temporary table is private for the session that created it and can be session-specific or transaction-specific. If the data is to deleted at the end of the transaction the table should be defined as follows:

Micellaneous Features
If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.
Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
Views can be created against temporary tables and combinations of temporary and permanent tables.
Tempoarary tables can have triggers associated with them.
Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
There are a number of restrictions related to temporary tables but these are version specific.

3. Difference between temp table and Cursor

A Cursor is a handle ( a name or pointer) for the memory associated with a specific statement. Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

Temporary segments are created by ORACLE when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

Hope this helps.
Vamsee

[sanereddy]

Post moved from \"Certification\" to Oracle.