Illegal use of LONG datatype Oracle

You can convert your LONG RAW value into a BLOB in a PL/SQL block, and then base64-encode that:

CREATE OR REPLACE FUNCTION to_base64 (   vcodem     IN FOEMP.COD_EMPR%TYPE,   vcodempl   IN FOEMP.COD_EMPL%TYPE)   RETURN VARCHAR2IS  l_blob BLOB;  l_long_raw LONG RAW;BEGIN  SELECT fot_empl INTO l_long_raw    FROM foemp   WHERE COD_EMPL = vcodempl AND COD_EMPR = vcodem;  l_blob := TO_BLOB(l_long_raw);  RETURN UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (l_blob));END;/

Of course, strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns; still storing data as LONG or LONG raw seems rather antiquated now.

SQL binds at most 4000 for varchar2 and 2000 for char. raw is a binary char, so I would encode in chunks of just under 2k, something like:

create or replace FUNCTION base64enc(p_blob IN BLOB) RETURN CLOB    AS      l_clob CLOB;      l_step PLS_INTEGER := 1998;    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 base64enc;

Now that you have the base64enc function, you'd pass in your (converted) blob and store CLOBs.

An example might be:

create table t1 ( id int primary key, x long raw )insert into t1 values( 1, rpad( 'a', 2000, 'a' ) )commit;create or replace function convertLR(i_id in int)return clobas  l_blob blob;  l_longraw long raw;begin  select x into l_longraw from t1 where id = i_id;  l_blob := to_blob(l_longraw);  return base64enc(l_blob);end;select convertLR(1) from dual;

Solution

1. given the many problems i had with long raw i decide to create atable like this:

   CREATE TABLE FOTS_EMPL ( cod_empr, cod_empl, foto) AS SELECT F.COD_EMPR, F.COD_EMPL, TO_LOB (FOT_EMPL) FROM FOEMP f;

2. i took the function given by @tbone and i added and if conditionlike this:

   CREATE OR REPLACE FUNCTION base64enc (p_blob IN BLOB) RETURN CLOBAS l_clob CLOB; l_step PLS_INTEGER := 1998;BEGIN IF p_blob IS NOT NULL THEN 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; ELSE RETURN NULL; END IF;END base64enc;

3. this was my final select statement:

   SELECT e.NOM_EMPL First_name, APE_EMPL Last_name, e.NOM_EMPL || ' ' || e.APE_EMPL Full_name, car.NOM_CARG position, COS.NOM_CCOS Area, base64enc(foto) Picture, E.FEC_NACI Birth_date FROM EMPLE e INNER JOIN CONTR c ON E.COD_EMPL = C.COD_EMPL INNER JOIN cargo car ON C.COD_CARG = CAR.COD_CARG INNER JOIN CCOST cos on COS.COD_CCOS = C.COD_CCOS LEFT JOIN FOTS_EMPL F -- new table with blob instead of long raw ON e.cod_empl = F.COD_EMPL AND e.cod_empr = f.cod_empr WHERE C.IND_ACTI = 'A';

Thank you very much.