CREATE OR REPLACE FUNCTION BLOB_TO_HEXSTR (blob_in IN BLOB, start_index in integer) RETURN VARCHAR2 IS v_varchar VARCHAR2(4000); v_start PLS_INTEGER := start_index * 2000 + 1; v_buffer PLS_INTEGER := 2000;
BEGIN –select userenv(‘LANGUAGE’) into g_nls_db_char from dual;
if DBMS_LOB.GETLENGTH(blob_in) is null then return empty_clob(); end if;
DBMS_OUTPUT.put_line(‘TEST:’ || CEIL(DBMS_LOB.GETLENGTH(blob_in)));
–DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
v_varchar := DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start);
–DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
–DBMS_OUTPUT.put_line(v_varchar);
RETURN v_varchar; end BLOB_TO_HEXSTR;
方法使用:SELECT BLOB_TO_HEXSTR(EMPLOYEE_PHOTO, 0) FROM TABLE_NAME WHERE XDF_EP_PERIOD_ID = ‘%%’ AND EMPLID = ‘%%’