Error in report query with image

Question

When I execute this report sql, which gets personal data and an image of the user, it gives me the following error:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

SELECT
ID, 
NAME,
ADDRESS,  
DECODE(GENDER,'M','MALE','F','FEMALE') as GENDER, 
DECODE(nvl(dbms_lob.getlength(IMG),0), 0, null,
            '<img style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '||
                 'src="'||apex_util.get_blob_file_src('P2_IMG',ID)||'" height="75" width="75" '||
                 'alt="Product Image" title="Product Image" />')  IMG 
FROM USMAN

Very strange query... Why do you need this decode in SOQL? What is the problem to retrieve all data from DB and only after this do all coding/decoding? — Andrii Muzychuk
@Chiz because OP probably wants to get this info into an orace apex report. The gender decode is not necessarily required as it could be resolved by a list of values defined in the column attributes. The img tag html generation less so, because of how apex_util.get_blob_file_src works. — Tom

Tom Tom · Accepted Answer · 2012-10-08T08:15:29

Probably because of your decode on the blob column, which produces a number, compares a number, and gets a char as a default value. You could try

DECODE(TO_CHAR(nvl(dbms_lob.getlength(IMG),0)), '0', null, '<img ... />')

Error in report query with image

1 Answers