Illegal use of LONG datatype Oracle

Question

here is my main question here

I have a problem converting a table column (long raw) to a base row of 64, this column contains some of the employee images.

This is the request, the field I'm trying to convert is f.fot_empl :

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, f.fot_empl 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 FOEMP f -- employee picture ON e.cod_empl = F.COD_EMPL WHERE C.IND_ACTI = 'A'; 

What I tried:

The accepted answer of this message without any results, I continue to get the error "Invalid use of LONG datatype". Workaround for ORA-00997: illegal use of a LONG data type

I am trying to implement the following function with no results:

 CREATE OR REPLACE FUNCTION to_base64 ( vcodem IN FOEMP.COD_EMPR%TYPE, vcodempl IN FOEMP.COD_EMPL%TYPE) RETURN VARCHAR2 IS V_VAR FOEMP.FOT_EMPL%TYPE; V_result VARCHAR2 (4000); BEGIN DBMS_OUTPUT.put_line ('Start'); SELECT UTL_RAW.cast_to_varchar2 ( UTL_ENCODE.base64_encode ( UTL_RAW.cast_to_raw (DBMS_LOB.SUBSTR (f.FOT_EMPL, 4000)))) INTO V_result FROM FOEMP f WHERE COD_EMPL = vcodempl AND COD_EMPR = vcodem; DBMS_OUTPUT.put_line ('End'); DBMS_OUTPUT.put_line ('Result: ' || V_result); END to_base64; / 

Function is invalid due to ORA-00997 in:

  SELECT UTL_RAW.cast_to_varchar2 ( UTL_ENCODE.base64_encode ( UTL_RAW.cast_to_raw (DBMS_LOB.SUBSTR (f.FOT_EMPL, 4000)))) INTO V_result FROM FOEMP f WHERE COD_EMPL = vcodempl AND COD_EMPR = vcodem; 

Thank you very much in advance.

Answer 1

Decision

• Given the many problems that I had with long cheese, I decided to create a table that looks 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;

• I took the function given by @tbone, and I added, and if the condition is for example:

  CREATE OR REPLACE FUNCTION base64enc (p_blob IN BLOB) RETURN CLOB AS 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;

• 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';

Many thanks.

Answer 2

You can convert the LONG RAW value to BLOB in a PL / SQL block, and then base64-encode, which:

 CREATE OR REPLACE FUNCTION to_base64 ( vcodem IN FOEMP.COD_EMPR%TYPE, vcodempl IN FOEMP.COD_EMPL%TYPE) RETURN VARCHAR2 IS 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, it is highly recommended that you convert LONG RAW columns to BOB (BOB) columns ; still saving data as LONG or LONG raw seems pretty outdated now.

Answer 3

SQL binds no more than 4000 for varchar2 and 2000 for char. raw is a binary char, so I would code in chunks a little less than 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 will pass your (converted) blob and save the CLOB.

An example could 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 clob as 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;