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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 | 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; / |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- 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 ; |
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了