I'm trying to encode my blob data to base64 so I can pass an image to XML BI Publisher
I initially tried this:
select UTL_ENCODE.base64_encode(dbms_lob.substr(EPHOTO, 4000,1))) as
string1 FROM EMPL_PHOTO WHERE emplid='1234';
But I get the error:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long ORA-06512: at line 1
So, I was going to try to split the string into 3 parts, then combine the string in PeopleCode. So I tried the sql below, but the 3rd parameter of the dms_lob.substr function isn't picking up the characters by position (because it has to do with the bytes), so I am unsure of what to do or how to do it correctly.
SELECT utl_raw.cast_to_varchar2(
UTL_ENCODE.base64_encode(dbms_lob.substr(EPHOTO, 1000,1))) as
string1,
utl_raw.cast_to_varchar2(
UTL_ENCODE.base64_encode(dbms_lob.substr(EPHOTO, 1000,1001))) as
string2,
utl_raw.cast_to_varchar2(
UTL_ENCODE.base64_encode(dbms_lob.substr(EPHOTO, 1000,2001))) as
string3
FROM EMPL_PHOTO WHERE emplid='1234';
The first select (string1) returns 1/4 of the photo, but string2 and string3 do not work because they are at the wrong position or something...
Thanks for any help!
I had to go with a SQL only solution because we are missing java libraries in our code base.
Note: This query will only work if the photos are less than or equal to 7275 bytes. If some employee's have photos bigger than this, the resize employee photo script in People Soft will need to be ran before this query runs.
I used Jim Marion's solution here:
SELECT
CASE
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 1455 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 1)))
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 1455 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(EMPLOYEE_PHOTO))
END AS C1,
CASE
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 2910 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 1456)))
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 2910 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 1455 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) - 1455, 1456)))
END AS C2,
CASE
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 4365 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 2911)))
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 4365 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 2910 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) - 2910, 2911)))
END AS C3,
CASE
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 5820 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 4366)))
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 5820 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 4365 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) - 4365, 4366)))
END AS C4,
CASE
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 7275 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 5821)))
WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 7275 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 5820 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) - 5820, 5821)))
END AS C5
FROM PS_EMPL_PHOTO
WHERE EMPLID = 'KUL704';