ORACLE:LONG_TO_CHAR(): CLOB类型转字符型
-- 创建测试数据表 Create table -- CUX_TEST15
create table CUX.CUX_TEST15
(
test_1 NUMBER not null,
test_name VARCHAR2(30) default 'ROOT' not null,
test_quantity NUMBER default 1 not null,
test_clob CLOB,
creation_date DATE default SYSDATE not null,
created_by NUMBER default -1 not null,
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER
)
tablespace CUX_TS_TX_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column CUX.CUX_TEST15.test_1
is '主键ID';
comment on column CUX.CUX_TEST15.test_name
is '默认为 ROOT用户';
comment on column CUX.CUX_TEST15.test_quantity
is '默认数量为1';
comment on column CUX.CUX_TEST15.test_clob
is 'CLOB数据类型';
comment on column CUX.CUX_TEST15.creation_date
is 'DATE日期类型';
ORA-01445:无法从不带保留关键字的表的联接视图中选择ROWID或采样
-- 例子数据
INSERT INTO CUX.CUX_TEST15(test_1,test_name,test_quantity,test_clob,creation_date,created_by,last_update_date,last_updated_by,last_update_login)
VALUES (1,'ADMIN','100','CLOB类型数据',SYSDATE,24354,SYSDATE,24354,123);
INSERT INTO CUX.CUX_TEST15(test_1,test_name,test_quantity,test_clob,creation_date,created_by,last_update_date,last_updated_by,last_update_login)
VALUES (2,'SYSADMIN','900','CLOB类型数据-SYSADM用户数据',SYSDATE,24354,SYSDATE,24354,234);
SELECT CUX_UTIL_PKG2.long_to_char(CT.ROWID,'CUX','CUX_TEST15','TEST_CLOB' ) AS DATA_CLOB_TO_CHAR
, CT.*
FROM CUX.CUX_TEST15 CT
create or replace package CUX_util_pkg2 is
-- Author : SAM
-- Created : 2022/8/21 10:20:39
-- Purpose : LONG_TO_CHAR
-- -- 将ORACLE LONG类型转为字符串类型
FUNCTION LONG_TO_CHAR(
p_rowid rowid,
p_owner varchar2,
p_table_name varchar2,
p_column varchar2
) RETURN VARCHAR2;
--
-- 获取字段默认值
FUNCTION get_data_default(
p_rowid rowid ,
p_owner varchar2,
p_table_name varchar2,
p_column varchar2,
p_data_default varchar2
) RETURN VARCHAR2 ;
-- 例子
-- SELECT CUX_util_pkg.get_data_default('','CUX','CUX_WIP_TRX_LINES_ALL','LAST_UPDATE_DATE','DATA_DEFAULT') DATA_DEFAULT
-- FROM DUAL;
FUNCTION GET_COMMENT_DDL(object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL
) RETURN CLOB ;
FUNCTION GET_DDL(object_type IN VARCHAR2,
p_table_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT NULL
) RETURN CLOB;
end CUX_util_pkg2;
/
create or replace package body CUX_util_pkg2 is
-- -- 将ORACLE LONG类型转为字符串类型
-- p_rowid: 隐藏的主键
-- p_owner: 用户名（SCHEMA)
-- p_table_name: 表名
-- p_column: clob类型的字段名称，即需要从CLOB类型转为VARCHAR类型的字段名称。
FUNCTION LONG_TO_CHAR(
p_rowid rowid,
p_owner varchar2,
p_table_name varchar2,
p_column varchar2
)
RETURN varchar2 AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--set serveroutput on size 10000000000;--
begin
DBMS_OUTPUT.ENABLE(buffer_size => null);
sql_cur := 'select ' || p_column || ' from ' || p_owner || '.' ||
p_table_name || ' where rowid = ' || chr(39) || p_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 LONG_TO_CHAR;
-- get_data_default(): 获取字段默认值
-- p_rowid: 隐藏的主键
-- p_owner: 用户名（SCHEMA)
-- p_table_name: 表名
-- p_column: clob类型的字段名称，即需要从CLOB类型转为VARCHAR类型的字段名称。
-- p_data_default: 默认值字段
FUNCTION get_data_default(
p_rowid rowid ,
p_owner varchar2,
p_table_name varchar2,
p_column varchar2,
p_data_default varchar2
)
RETURN varchar2 AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--set serveroutput on size 10000000000;--
begin
DBMS_OUTPUT.ENABLE(buffer_size => null);
sql_cur := 'select ' || p_data_default || ' from sys.dba_tab_columns where owner=' || chr(39) || UPPER(p_owner) || chr(39)
|| ' and table_name = ' || chr(39) || UPPER(p_table_name) || chr(39)
|| ' and column_name = ' || chr(39) || UPPER(p_column) || 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 get_data_default;
-- SELECT get_data_default('ROWID',col.owner,col.table_name,col.COLUMN_NAME,'DATA_DEFAULT') FROM DUAL;
FUNCTION GET_COMMENT_DDL(object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL
) RETURN CLOB
AS
V_COMMENTS varchar2(500);
V_TAB_COMMENTS varchar2(500);
L_CLOB CLOB;
CURSOR C_COMMENT IS
SELECT OWNER,TABLE_NAME, COLUMN_NAME, COMMENTS
FROM DBA_COL_COMMENTS
WHERE TABLE_NAME = UPPER(NAME) --'SFY_OE_QUOTATION_HEADERS_ALL'
AND OWNER = NVL(UPPER(SCHEMA),OWNER)
AND COMMENTS IS NOT NULL ;
CURSOR C_TAB_COMMENT IS
SELECT OWNER,TABLE_NAME, TABLE_TYPE, COMMENTS
FROM DBA_TAB_COMMENTS
WHERE TABLE_NAME = UPPER(NAME) --'SFY_OE_QUOTATION_HEADERS_ALL'
AND OWNER = NVL(UPPER(SCHEMA),OWNER)
AND COMMENTS IS NOT NULL ;
BEGIN
L_CLOB := NULL;
FOR R_TB IN C_TAB_COMMENT LOOP
V_TAB_COMMENTS := 'COMMENT ON TABLE '|| R_TB.OWNER||'.'||R_TB.TABLE_NAME||' IS '||chr(39) || R_TB.COMMENTS ||chr(39) ||';' || CHR(10);
L_CLOB := L_CLOB || V_TAB_COMMENTS;
END LOOP;
FOR R_COM IN C_COMMENT LOOP
V_COMMENTS := 'COMMENT ON COLUMN '|| R_COM.OWNER||'.'||R_COM.TABLE_NAME||'.'||R_COM.COLUMN_NAME ||' IS '||chr(39) || R_COM.COMMENTS ||chr(39) ||';' || CHR(10);
L_CLOB := L_CLOB || V_COMMENTS;
END LOOP;
RETURN L_CLOB;
END GET_COMMENT_DDL;
FUNCTION GET_DDL(object_type IN VARCHAR2,
p_table_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT NULL
)
return clob AS
i number;
l_clob clob;
l_clob_comment clob;
v_name varchar2(128);
v_schema varchar2(128);
v_table_header varchar2(500);
v_table_footer varchar2(500);
v_column_desc varchar2(500);
v_col_count number;
-- 表游标
cursor c_table( p_name varchar2, p_schema varchar2) is
select owner, table_name, tablespace_name
from dba_tables
where owner = nvl( p_schema,owner) -- 'CUX'
AND TABLE_NAME =p_table_name -- 'CUX_WIP_TRX_LINES_ALL'
;
-- 字段 游标
cursor c_column(p_name varchar2, p_schema varchar2) is
SELECT COL.COLUMN_NAME || CHR(32)|| COL.DATA_TYPE ||
CASE WHEN INSTR(COL.DATA_TYPE,'CHAR',1)>0 THEN
'('||COL.DATA_LENGTH||') '
END
||
CASE WHEN COL.NULLABLE = 'N' THEN
CASE WHEN col.default_length>0 THEN
' DEFAULT '|| get_data_default('ROWID',col.owner,col.table_name,col.COLUMN_NAME,'DATA_DEFAULT') || ' NOT NULL '
ELSE
' NOT NULL '
END
ELSE
''
END AS column_desc
FROM sys.DBA_TAB_COLUMNS COL
WHERE TABLE_NAME = p_name -- 'SFY_WIP_TRX_LINES_ALL'
and owner = nvl(p_schema , owner)
-- AND COLUMN_NAME = 'LAST_UPDATE_DATE'
ORDER BY COL.COLUMN_ID ;
-- 统计表有多少个字段
cursor c_column2(p_name varchar2, p_schema varchar2) is
SELECT count(COL.COLUMN_NAME) as col_count
FROM sys.DBA_TAB_COLUMNS COL
WHERE TABLE_NAME = p_name -- 'SFY_WIP_TRX_LINES_ALL'
and owner = nvl(p_schema , owner)
-- AND COLUMN_NAME = 'LAST_UPDATE_DATE'
ORDER BY COL.COLUMN_ID ;
begin
-- v_name := 'CUX_WIP_TRX_LINES_ALL';
-- v_schema := 'CUX';
v_name := upper(p_table_name);
v_schema := upper(p_owner);
l_clob := null;
--#region:table
IF UPPER(OBJECT_TYPE) = 'TABLE' THEN
-- 表游标
for r_tab in c_table(v_name, v_schema) loop
v_table_header := 'CREATE TABLE ' || r_tab.owner ||'.'|| r_tab.table_name || ' ( '|| chr(10);
-- v_table_footer := ') TABLESPACE '|| r_tab.tablespace_name ||'; ' ||chr(10)||chr(13);
v_table_footer := ') ; ' ||chr(10)||chr(13);
--#region:column
for r_col2 in c_column2(v_name, v_schema) loop
v_col_count := r_col2.col_count;
end loop;
-- 字段游标
open c_column(v_name, v_schema) ;
loop
fetch c_column into v_column_desc ;
exit when(c_column%notfound);
-- dbms_output.put_line('v_col_count='||v_col_count);
if v_col_count = c_column%rowcount then
l_clob := l_clob || v_column_desc || CHR(10);
else
l_clob := l_clob || v_column_desc || ',' || CHR(10);
end if;
-- dbms_output.put_line('v_col_count-> c_column%rowcount ='||c_column%rowcount);
end loop;
close c_column;
/*
for r_col in c_column(v_name, v_schema) loop
if i = c_column%rowcount - 1 then
l_clob := l_clob || r_col.column_desc || CHR(10);
else
l_clob := l_clob || r_col.column_desc || ',' || CHR(10);
end if;
end loop;
*/
--#endregion:column
l_clob := v_table_header || l_clob || v_table_footer;
end loop;
END IF;
--#endregion:table
--#regeion:commnt
-- 类型为 “注释”COMMENT
IF object_type='COMMENT' THEN
SELECT GET_COMMENT_DDL('COMMENT',V_NAME,V_SCHEMA)
INTO l_clob_comment
FROM DUAL ;
L_CLOB := L_CLOB || l_clob_comment;
END IF;
--#endregeion:commnt
return l_clob;
end GET_DDL;
begin
-- Initialization
-- <Statement>;
NULL;
end CUX_util_pkg2;
/
-- Created on 2023/4/17 by SAM
declare
-- Local variables here
i integer;
v_clob clob;
V_TEXT VARCHAR2(32767);
cursor c1 is
select OBJ.OWNER, OBJ.OBJECT_NAME, OBJ.OBJECT_TYPE
from sys.dba_objects obj
where obj.object_type = 'TABLE'
AND OBJ.OBJECT_NAME LIKE 'CUX%'
ORDER BY OBJ.OWNER , OBJ.OBJECT_NAME ;
begin
-- 打印 表名以“CUX”开头的表文本
--select apps.CUX_util_pkg2.get_dDl('TABLE','CUX_TEST15','CUX')
-- into v_clob
-- from dual;
for r1 in c1 loop
select apps.CUX_util_pkg2.get_dDl('TABLE',r1.object_name, r1.owner)
-- CUX_util_pkg2.GET_COMMENT_DDL('COMMENT',r1.object_name, r1.owner)
into v_clob
from dual;
V_TEXT := substr(V_CLOB,1,32767);
DBMS_OUTPUT.PUT_LINE(V_TEXT);
end loop;
end;
优质生活从拆开始
