I have created the UPLD_FILES table.For this table, I created sequence and trigger.I am using apex version 5.0.1.00.06
CREATE TABLE "UPLD_FILES"
( "ID" NUMBER,
"BLOB_CONTENT" BLOB,
"MIME_TYPE" VARCHAR2(255),
"FILENAME" VARCHAR2(255),
"LAST_UPDATED" DATE,
"CHARACTER_SET" VARCHAR2(128),
PRIMARY KEY ("ID") ENABLE,
CONSTRAINT "UC_FILENAME" UNIQUE ("FILENAME") DISABLE
) ;
CREATE SEQUENCE "SEQ_APEX_UPLD_FILES" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 121 CACHE 20 NOORDER NOCYCLE ;
CREATE OR REPLACE TRIGGER "BI_UPLD_FILES"
BEFORE INSERT
ON UPLD_FILES
FOR EACH ROW
BEGIN
:new.id := SEQ_APEX_UPLD_FILES.NEXTVAL;
END;
/
ALTER TRIGGER "BI_UPLD_FILES" ENABLE;
After inserting the images into table
In PL/SQL Code added the following code
declare
l\_body\_html clob;
l\_attachment\_img blob;
l\_attachment\_mimetype varchar2(100);
l\_encoded\_img clob;
FUNCTION base64encode(
p\_blob IN BLOB)
RETURN CLOB
IS
l_clob CLOB;
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step)
LOOP
l\_clob := l\_clob || UTL\_RAW.cast\_to\_varchar2(UTL\_ENCODE.base64\_encode(DBMS\_LOB.substr(p\_blob, l\_step, i \* l\_step + 1)));
END LOOP;
RETURN l_clob;
END;
begin
--get image from the table
select BLOB\_CONTENT,MIME\_TYPE into l\_attachment\_img,l\_attachment\_mimetype from UPLD\_FILES where id = 75;
--and base64 encode it
l\_encoded\_img := base64encode(l\_attachment\_img);
l\_body\_html := '\<html>\<body>\<img alt="my\_image.jpg" src="data:'|| l\_attachment\_mimetype || ';base64, '|| l\_encoded\_img || '">\</body>\</html>';
dbms_output.put_line('l_body_html = '||l_body_html);
htp.p(l_body_html);
end;
Above code is working correctly working for one value.But below Pl/sql code for multiple is not working.I need multiple images showing using function.
declare
l\_body\_html clob;
l\_attachment\_img blob;
l\_attachment\_mimetype varchar2(100);
l\_encoded\_img clob;
FUNCTION base64encode(
p\_blob IN BLOB)
RETURN CLOB
IS
l_clob CLOB;
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step)
LOOP
l\_clob := l\_clob || UTL\_RAW.cast\_to\_varchar2(UTL\_ENCODE.base64\_encode(DBMS\_LOB.substr(p\_blob, l\_step, i \* l\_step + 1)));
END LOOP;
RETURN l_clob;
END;
begin
FOR apps IN(
select BLOB_CONTENT,MIME_TYPE from UPLD_FILES where id > 74
)
loop
l_encoded_img := base64encode(apps.BLOB_CONTENT);
l_body_html := '<html><body><img alt="my_image.jpg" src="data:'|| apps.MIME_TYPE || ';base64, '|| l_encoded_img || '"></body></html>';
dbms_output.put_line('l_body_html = '||l_body_html);
htp.p(l_body_html);
end loop;
end;
It is showing error.
ORA-06502: PL/SQL: numeric or value error.