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;

　　

posted @   samrv  阅读(189)  评论(0)  编辑  收藏  举报
相关博文：
· SQL: PL/SQL打印用户表脚本文本
· ORACLE long类型转字符 LONG_TO_CHAR()
· 《基础语法篇》字段类型
· oracle
· Oracle问题记录
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示