Duke -- Thanks for the question regarding "How to UNION ALL xxx_VIEWS and xxx_SOURCE ?", version 10.2.0
Submitted on 3-Mar-2008 10:42 Central time zone
Last updated 3-Mar-2008 14:25
You Asked
I often build a view to prototype/develop ETL transformation rules, then "wrap" the view in a PL/SQL package.
Occasionally, I need to search the ETL code. However, xxx_VIEW.TEXT is LONG, while xxx_SOURCE is VARCHAR2(4000).
I know I can run thru the view TEXT, if less than 32k in length with PL/SQL like this:
begin
for rec IN ( select * from user_views ) loop
dbms_output.put_line(rec.view_name||' = '||substr(rec.text,1,25));
end loop;
end;
But can I build a a view that:
1) unions both view and PL/SQL source,
2) splits view text by line
and is described like xxx_SOURCE:
DESC xxx_SOURCE;
Name Type
---- ------------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
I'm willing to assume every view text-line is less than 4000 in length!
Thanks!
and we said...
here is an approach - warning, I just cobbled this together to demonstrate the idea, it
has not been thoroughly tested, please review it and add more robust error handling (eg: add some :) )
basically, using dbms_sql we can piecewise fetch the long, parse it around chr(10) (newlines) and pipe it out, that can be joined back to user_views (no join needed, it does the right thing)
ops$tkyte%ORA10GR2> create or replace type myVCtype as table of varchar2(4000)
2 /
Type created.
ops$tkyte%ORA10GR2> create or replace function getViewText( p_vname in varchar2 )
2 return myVCType
3 authid current_user
4 PIPELINED
5 as
6 l_cursor integer default dbms_sql.open_cursor;
7 l_n number;
8 l_long_val long;
9 l_long_len number;
10 l_buflen number := 10000;
11 l_curpos number := 0;
12 l_leftover long;
13 n number;
14 begin
15 dbms_sql.parse( l_cursor,
16 'select text from user_views where view_name = :x',
17 dbms_sql.native );
18 dbms_sql.bind_variable( l_cursor, ':x', p_vname );
19
20 dbms_sql.define_column_long(l_cursor, 1);
21 l_n := dbms_sql.execute(l_cursor);
22
23 if (dbms_sql.fetch_rows(l_cursor)>0)
24 then
25 loop
26 dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos,
l_long_val, l_long_len );
27 exit when (nvl(l_long_len,0) = 0);
28 if (l_long_len < l_buflen and substr( l_long_val, l_long_len, 1 ) !=
chr(10) )
29 then
30 l_long_val := l_long_val || chr(10);
31 end if;
32 l_long_val := l_leftover || l_long_val;
33 loop
34 n := instr( l_long_val, chr(10) );
35 exit when (nvl(n,0) = 0);
36 pipe row( substr( l_long_val, 1, n-1 ) );
37 l_long_val := substr( l_long_val, n+1 );
38 end loop;
39 l_leftover := l_long_val;
40 l_curpos := l_curpos + l_long_len;
41 end loop;
42 end if;
43 dbms_sql.close_cursor(l_cursor);
44 return ;
45 end;
46 /
Function created.
ops$tkyte%ORA10GR2> column column_value format a60
ops$tkyte%ORA10GR2> create or replace view v
2 as
3 select
4 *
5 from
6 dual;
View created.
ops$tkyte%ORA10GR2> declare
2 l_text long := 'create or replace view v2 as select 1 c1';
3 begin
4 for i in 2..500
5 loop
6 l_text := l_text || ', ' || chr(10) || '''' || to_char( i, rpad('0',40,'0')
) || ''' c' || i;
7 end loop;
8 execute immediate l_text || ' from dual';
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select view_name, column_value
2 from user_views, table( getViewText(view_name) )
3 /
VIEW_NAME COLUMN_VALUE
------------------------------
------------------------------------------------------------
V select
V "DUMMY"
V from
V dual
V2 select 1 c1,
V2 ' 0000000000000000000000000000000000000002' c2,
V2 ' 0000000000000000000000000000000000000003' c3,
V2 ' 0000000000000000000000000000000000000004' c4,
V2 ' 0000000000000000000000000000000000000005' c5,
...
V2 ' 0000000000000000000000000000000000000498' c498,
V2 ' 0000000000000000000000000000000000000499' c499,
V2 ' 0000000000000000000000000000000000000500' c500 from dual
504 rows selected.
Types, pipelined functions, DBMS_SQL, etc...
March 3, 2008 - 4pm Central time zone
Reviewer: Duke Ganote from HOTSOS 2008, Day 1
Mostly stuff I've hardly ever even glanced at in Oracle!!!
Thank you.
SYS.DBMS_METADATA_UTIL.LONG2CLOB
March 6, 2008 - 9am Central time zone
Reviewer: Andras Gabor from Debrecen, Hungary
Hi,
DDL so far...
April 15, 2008 - 2pm Central time zone
Reviewer: Duke Ganote from Amelia, Ohio USA
So far, I added line#, used ALL_VIEW, and force-split the text if the line is more than 4000 in
length. I was lazy and didn't bind the schema parameter yet.
CREATE OR REPLACE
TYPE view_line AS OBJECT
( TEXT VARCHAR2(4000)
, LINE NUMBER
)
/
CREATE OR REPLACE
TYPE view_source AS TABLE OF view_line
/
CREATE OR REPLACE FUNCTION Get_View_Line
( p_vname IN VARCHAR2
, p_schema IN VARCHAR2 DEFAULT USER )
RETURN view_source AUTHID CURRENT_USER PIPELINED
AS
-- *******************************************************
-- Purpose: Shred LONG VIEW TEXT into separate lines
-- ( like xxx_SOURCE )
--
-- http://tinyurl.com/5b2a8s
--
-- *******************************************************
l_cursor INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
l_n NUMBER;
l_long_val LONG;
l_long_len NUMBER;
l_buflen NUMBER := 4000;
l_curpos NUMBER := 0;
l_leftover LONG;
n NUMBER;
l_line NUMBER := 0;
select_txt VARCHAR2(100) := 'select text from all_views where ';
BEGIN
select_txt := select_txt||'owner = '''||p_schema||''' AND view_name = :x';
DBMS_SQL.PARSE (l_cursor
, select_txt
, DBMS_SQL.native
);
DBMS_SQL.BIND_VARIABLE (l_cursor, ':x', p_vname);
DBMS_SQL.DEFINE_COLUMN_LONG (l_cursor, 1);
l_n := DBMS_SQL.EXECUTE (l_cursor);
IF (DBMS_SQL.FETCH_ROWS (l_cursor) > 0)
THEN
LOOP
DBMS_SQL.COLUMN_VALUE_LONG (l_cursor
, 1
, l_buflen
, l_curpos
, l_long_val
, l_long_len
);
EXIT WHEN (NVL (l_long_len, 0) = 0);
IF ( l_long_len < l_buflen
AND SUBSTR (l_long_val, l_long_len, 1) != CHR (10)
)
THEN
l_long_val := l_long_val || CHR (10);
END IF;
l_long_val := l_leftover || l_long_val;
LOOP
n := LEAST( INSTR(l_long_val, CHR (10))
, 4000); -- up to 4000 chars
EXIT WHEN (NVL (n, 0) = 0);
l_line := l_line+1;
PIPE ROW ( view_line
( SUBSTR (l_long_val, 1, n - 1)
, l_line)
);
l_long_val := SUBSTR (l_long_val, n + 1);
END LOOP;
l_leftover := l_long_val;
l_curpos := l_curpos + l_long_len;
END LOOP;
END IF;
DBMS_SQL.CLOSE_CURSOR (l_cursor);
RETURN;
END Get_View_Line;
/
CREATE OR REPLACE VIEW ALL_VIEW_SOURCE
(OWNER, NAME, TYPE, LINE, TEXT)
AS
SELECT
-- ********************************************************
-- Purpose: Provide VIEW lines like xxx_SOURCE
--
-- http://tinyurl.com/5b2a8s
-- ********************************************************
av.owner
, av.view_name AS "NAME"
, 'VIEW' AS "TYPE"
, l."LINE"
, l."TEXT"
FROM ALL_VIEWS av
, TABLE (Get_View_Line (av.view_name, av.owner)) l
/
oops. Need to have user_mviews.query also
November 6, 2008 - 3pm Central time zone
Reviewer: Duke Ganote from Amelia, OH USA
Also QUERY text (LONG data_type) in USER_MVIEWS should be unioned.