Hi ,
I tried saving the blob field. but am not getting the image but only the text of the jpg is coming in. Can you help what should I need to do for getting the blob images from oracle.
Appreciate if any one can help me in this.



FILEDEF PHOTO DISK /home/webfocus/ibi/apps/ifnw/PHOTO.FTM

TABLE FILE PI_FUND_MNGR_PHOTOGRAPH
PRINT PHOTOGRAPH
IF RECORDLIMIT EQ 1
ON TABLE SAVE AS PHOTO
END
-RUN

This message has been edited. Last edited by: Kerry,

Srinivasan,

You have already opened a topic on this subject.

Please do not abuse the system by opening multiple Topics on the same issue.

All that will happen is that other members will ignore anything you post as being a waste of their time.

You have 2 choices either use a Java call or SQL passthru

In my previous experience WF was not able to retrieve blobs you had to use SQL embeddded within JAVA to retrive the images.

You can store and retrieve blobs in most systems using SQL PASSTHRU via a stored procedure.

Alternativly an external Java call can usually do the same.

What you cannot do is retrieve a blob directly in a table request, Compute or Define.

Thanks JG,
sorry that I have started the same topic again. Thanks for the suggestion. I am able to save the image in the dbserver using the sql passthru.
but the problem is I need to save the image in the WfClient Server rather than on the DB server. has anyone faced a similar issue. please help me on this. Ftp is restricted.

Please suggest.
Thanks,
srinivasan

It's a long time since I did this but here's some old code that I used about 6 years ago.

It was written against Oracle 9.2 but should still be valid.

1st you need a stored porocedure

xx is the database
your_table is the name of the table
ID is the column name for the unique key for the required record
IDNO is the key value for the required record
THEBLOB is the column containing the blob
output_file_name is the name of the stored file e.g. file.jpg

CREATE OR REPLACE
PROCEDURE SP_GETBLOB_FOR_WF
(IDNO IN INTEGER,OUTFILE IN VARCHAR)
IS
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;

BEGIN
-- Get LOB locator
SELECT THEBLOB
INTO l_blob
FROM your_table
WHERE ID = IDNO;

l_blob_len := DBMS_LOB.getlength(l_blob);

-- Open the destination file.
l_file := UTL_FILE.fopen('BLOB_FILE',OUTFILE,'w', 32767);

-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;

-- Close the file.
UTL_FILE.fclose(l_file);

EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;

Call the stored procedure passing the required parameters

SQL SQLORA
EX xx.SP_GETBLOB_FOR_WF 'IDNO','output_file_name';
END
-RUN

This will create the file in the edatemp directory which you can then copy or move to where you want

Thanks JG,

I will try this and update you on the result.

Thanks much on the solution.

Hi.

Although this is an old thread, I now need to make JG's suggestion work.

There is no problem with the stored procedure: it creates the file perfectly on the Oracle server, which is running on a different machine as WebFOCUS.

My question is: does anybody know what needs to be done for the file to magically appear in edatemp, as the last line in JG's thread suggests?

Thanks
Ruhan