E9.2 Oracle SQL F00165 coming with "0" at each character

[Felipe Vidal]

I created the following DB function, which I expect will retrieve the text from table F00165.


(Usage in F00165):


The problem is that it retrieves the text in the following BLOB format:


This is an issue because when I convert this into CLOB, those zeroes are converted into "invisible" characters:


I read that this is related to the Character Set F00165 is configured with, but how can I then translate F00165 into a usable string without needing to modify such string after applying the CLOB conversion to it? Today trying to use the resulting text generates completion errors.

 

[franck_blettner]

Hello Felipe,
If you need only to extract text from F00165, a query like below can give you the answer without PL/SQL. Only 2000 characters are extracted, but the query can be adapted with concat if you need more. Also this query will remove all carriage return but you can remove this part of the query if you need.

SELECT GDTXKY,
SUBSTR(REGEXP_REPLACE(REGEXP_REPLACE(
REPLACE(REPLACE(
utl_raw.cast_to_varchar2(utl_raw.convert (nvl( replace(
dbms_lob.substr(GDTXFT,2000,1) ,
'00',''),'20'), 'al32utf8' , 'we8mswin1252' ))
, CHR(10), NULL), CHR(13), NULL),
'<[^>]*>',
''
), '<img[^>]*',
NULL),1,255)
AS TEXT
FROM PRODDTA.F00165

Regards,
Franck

 

[Felipe Vidal]

Thanks, @franck_blettner ,
I did the following &, as of now, it's working.

--AMSSHIPSVC
CREATE OR REPLACE FUNCTION AMS_DBF5842006(INVAR_BLOB IN BLOB) RETURN CLOB IS
    CLOBOUT CLOB; BLOBOUT BLOB;
    CLOBDLT CLOB; LDESTOFF NUMBER := 1; LSOUROFF NUMBER := 1; LLANGOFF NUMBER := DBMS_LOB.DEFAULT_LANG_CTX; LWARNING NUMBER; VAR_CHSET VARCHAR(12);
BEGIN
    VAR_CHSET := 'WE8MSWIN1252';
    SELECT REPLACE(DBMS_LOB.SUBSTR(INVAR_BLOB, 4000, 1), '00') INTO BLOBOUT FROM DUAL;
    DBMS_LOB.CREATETEMPORARY(CLOBOUT, TRUE);
    DBMS_LOB.CONVERTTOCLOB(CLOBOUT, BLOBOUT, DBMS_LOB.LOBMAXSIZE, LDESTOFF, LSOUROFF, NLS_CHARSET_ID(VAR_CHSET), LLANGOFF, LWARNING);
    RETURN CLOBOUT;
END;
/

--AMSSHIPSVC
CREATE OR REPLACE FUNCTION AMS_DBF5842005(INVAR_MEDOBJ IN VARCHAR) RETURN CLOB IS
    OUT_JSON CLOB; VAR_SYS VARCHAR(10) := 'GT4016A';
BEGIN
    SELECT AMS_DBF5842006(GDTXFT) INTO OUT_JSON
        FROM PRODDTA.F00165 WHERE GDOBNM = RPAD(VAR_SYS, 10, ' ') AND REGEXP_SUBSTR(GDTXKY, INVAR_MEDOBJ, 1, 1) IS NOT NULL;
    RETURN OUT_JSON;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
/

What helped doing it was your suggestion on DBMS_LOB.SUBSTR & REPLACE against its output.