非法使用LONG数据类型Oracle [英] Illegal use of LONG datatype Oracle
问题描述
这是我的第一个问题
尝试将表列(长原始数据)转换为基数为64的字符串时遇到问题，该列包含一些员工图片.
I have a problem trying to convert a table column (long raw) to a base 64 string, this column contains some of the employees pictures.
这是查询，我要转换的字段是 f.fot_empl :
This is the query, the field that 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';
我尝试过的事情:
此帖子的已接受答案，没有结果，我不断收到非法使用LONG数据类型"错误. ORA-00997的解决方法:非法使用LONG数据类型
The accepted answer of this post with no results, i keep getting "Illegal use of LONG datatype" error. Workaround for ORA-00997: illegal use of LONG datatype
我尝试实现以下功能，但没有结果:
I try 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;
/
由于ORA-00997中的功能，该功能无效:
The 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;
非常感谢.
推荐答案
解决方案
-
鉴于我长期以来遇到的许多问题，我决定创建一个 像这样的表:
given the many problems i had with long raw i decide to create a table 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;
CREATE TABLE FOTS_EMPL ( cod_empr, cod_empl, foto)
AS
SELECT F.COD_EMPR, F.COD_EMPL, TO_LOB (FOT_EMPL)
FROM FOEMP f;
我接受了@tbone给定的功能，并添加了if条件 像这样:
i took the function given by @tbone and i added and if condition like this:
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;
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';
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';
非常感谢您.
这篇关于非法使用LONG数据类型Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
