--方法1，支持表
--plsql中将long类型隐式转换为varchar2，但是sql不能
CREATE OR REPLACE FUNCTION LONG_TO_CHAR(
in_rowid rowid,
in_owner varchar,
in_table_name varchar,
in_column varchar2)
RETURN varchar AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--set serveroutput on size 10000000000;
begin
DBMS_OUTPUT.ENABLE (buffer_size=>null);
sql_cur := 'select '||in_column||' from '||in_owner||'.'||in_table_name||' where rowid = '||chr(39)||in_rowid||chr(39);
dbms_output.put_line (sql_cur);
execute immediate sql_cur into text_c1;
text_c1 := substr(text_c1, 1, 4000);
RETURN TEXT_C1;
END;
/
--------------------------------------
--test
-- 参数分别为 rowid，用户，表名，字段名
DROP TABLE VI.TEST1;
CREATE TABLE VI.TEST1(ID NUMBER,V_CLOB CLOB,V_LONG LONG DEFAULT 'create or replace view v_t',V_SYSDATE DATE DEFAULT SYSDATE);
INSERT INTO VI.TEST1(ID) SELECT 1 FROM DUAL;
COMMIT;
select LONG_TO_CHAR(ROWID, 'VI', 'TEST1', 'V_LONG') FROM TEST1;
-------------------------------------------------------------------------------------------------------------------------------------------------
--方法2，支持表和视图
--long查询结果转换为varchar2类型
/*来自Thomas Kyte 《Oracle9i/10g/11g编程艺术》 12章节中.由于long的操作限制,那么在操作long之前可以将该类型的结果转换为varchar2，
然后再使用long_help.substr_of，基本意思是将long结果的的前4000字节转换为varchar2类型.如果long的数据超过了4000字节,那么可以将循环调用此函数
*/
create or replace package long_help
authid current_user
as
function substr_of(
p_query in varchar2,
p_from in number,
p_for in number,
p_name1 in varchar2 default NULL,
p_bind1 in varchar2 default NULL,
p_name2 in varchar2 default NULL,
p_bind2 in varchar2 default NULL,
p_name3 in varchar2 default NULL,
p_bind3 in varchar2 default NULL,
p_name4 in varchar2 default NULL,
p_bind4 in varchar2 default NULL
)
return varchar2;
end;
/
create or replace package body long_help
as
g_cursor number := dbms_sql.open_cursor;
g_query varchar2(32765);
procedure bind_variable(
p_name in varchar2,
p_value in varchar2
)
is
begin
if ( p_name is not null) then
dbms_sql.bind_variable( g_cursor, p_name, p_value );
end if;
end;
function substr_of(
p_query in varchar2,
p_from in number,
p_for in number,
p_name1 in varchar2 default NULL,
p_bind1 in varchar2 default NULL,
p_name2 in varchar2 default NULL,
p_bind2 in varchar2 default NULL,
p_name3 in varchar2 default NULL,
p_bind3 in varchar2 default NULL,
p_name4 in varchar2 default NULL,
p_bind4 in varchar2 default NULL)
return varchar2 as
l_buffer varchar2(4000);
l_buffer_len number;
begin
if ( nvl(p_from,0) <= 0 ) then
raise_application_error(-20002, 'From must be >= 1 (positive numbers)');
end if;
if ( nvl(p_for,0) not between 1 and 4000 ) then
raise_application_error (-20003, 'For must be between 1 and 4000' );
end if;
if ( p_query <> g_query or g_query is NULL ) then
if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%') then
raise_application_error(-20001, 'This must be a select only' );
end if;
dbms_sql.parse( g_cursor, p_query, dbms_sql.native );
g_query := p_query;
end if;
bind_variable( p_name1, p_bind1 );
bind_variable( p_name2, p_bind2 );
bind_variable( p_name3, p_bind3 );
bind_variable( p_name4, p_bind4 );
dbms_sql.define_column_long(g_cursor, 1);
if (dbms_sql.execute_and_fetch(g_cursor)>0) then
dbms_sql.column_value_long(g_cursor, 1, p_for, p_from-1,l_buffer, l_buffer_len );
end if;
return l_buffer;
end substr_of;
end;
/
--使用方法:
--查询DBA_TAB_PARTITIONS中的LONG类型：HIGH_VALUE
SELECT * FROM(
SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,
LONG_HELP.SUBSTR_OF('SELECT HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNER AND TABLE_NAME=:TABLE_NAME AND PARTITION_NAME=:PARTITION_NAME',
1,
4000,
'TABLE_OWNER',
TABLE_OWNER,
'TABLE_NAME',
TABLE_NAME,
'PARTITION_NAME',
PARTITION_NAME) HIGH_VALUE
FROM DBA_TAB_PARTITIONS
);
--查询long类型数据
SELECT LONG_HELP.SUBSTR_OF('SELECT V_LONG FROM TEST1',1,4000) FROM TEST1;
--查询DBA_VIEWS视图的 TEXT时报错：ORA-01006：绑定变量不存在(原因可能是不支持视图，应该在外层写筛选条件)
-------------------------------------------------------------------------------------------------------------------------------------------------
--方法三，支持表
select sys.dbms_metadata_util.long2varchar(100,'scott.t1','name',rowid) from scott.t1;
-------------------------------------------------------------------------------------------------------------------------------------------------
--varchar2转化为long，撰写的procedure案例如下
create table erpmainbom(
location varchar2(4000),
locationplus long,
parentpn varchar2(4000),
pn varchar2(4000),
plant varchar2(4000)
);
--location字段空格之前的字符重新复制给location，从空格开始的之后的字符拼接locationplus（long类型）字段并复制给locationplus
Create or replace PROCEDURE P_UPDATELOCALPLUS(
strMitm in varchar2,
strSubITEM in varchar2,
strPlant in varchar2)
as
lLocationPlus long;
strLocation varchar2(4000);
strSubLoc varchar2(50);
lTempLoct long;
i number;
Begin
Select location,locationplus into strLocation,lLocationPlus from erpmainbom Where parentpn=strMitm and pn=strSubITEM and plant= strPlant;
If length(strLocation)=4000 then
i:=1;
strSubLoc:=substr(strLocation,4000,i);
While InStr(strSubLoc, ' ') = 0 Loop
i := i + 1;
strSubLoc := substr(strLocation,4000-i,i);
End Loop;
--i:=i+1;
strSubLoc := substr(strLocation,4000-i,i+1);
strLocation := substr(strLocation,1, 4000 - Length(strSubLoc));
lTempLoct:=strSubLoc;
End if;
lLocationPlus:=lTempLoct||lLocationPlus;
update erpmainbom set location=strLocation,locationplus=lLocationPlus where parentpn=strMitm and pn=strSubITEM and plant= strPlant;
commit;
EXCEPTION When others then
Rollback;
end;
/