Oracle数据字典中long字段的查询
-- Oracle数据字典中long字段的查询
-- 在数据字典中保留很多long字段,查询时很不方便。
-- 以下包可以转换为varchar2方便操作,查询数据字典时可以用varchar进行查询
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;
--使用方法:
/*SELECT LONG_HELP.SUBSTR_OF(''||
'SELECT data_default '||
' FROM user_tab_columns '||
' WHERE table_name = '''||t.table_name ||''''||
' AND column_name = '''||t.column_name||''''||
'',1,4000) AS data_default
FROM user_tab_columns t;*/
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;
/
-- 使用(调用)方法:
SELECT LONG_HELP.SUBSTR_OF(''||
'SELECT data_default '||
' FROM user_tab_columns '||
' WHERE table_name = '''||t.table_name ||''''||
' AND column_name = '''||t.column_name||''''||
'',1,4000) AS data_default
,t.*
FROM user_tab_columns t;