Thanks for the question, Duke.

Asked: March 03, 2008 - 10:42 am UTC

Last updated: June 27, 2018 - 9:47 am UTC

Version: 10.2.0

Viewed 1000+ times

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 Tom 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.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Types, pipelined functions, DBMS_SQL, etc...

Duke Ganote, March 03, 2008 - 4:36 pm UTC

Mostly stuff I've hardly ever even glanced at in Oracle!!!

Thank you.

SYS.DBMS_METADATA_UTIL.LONG2CLOB

Andras Gabor, March 06, 2008 - 9:58 am UTC

Hi,

Sometimes I use SYS.DBMS_METADATA_UTIL.LONG2CLOB to convert LONGs to CLOB (or VARCHAR2, see other subprograms of the package), that way I can handle them (not search or anything, but handle them nicely, without the 32K limit). You can also use TO_CLOB for varhcars. This way you can even UNION ALL them.

However the SYS.DBMS_METADATA_UTIL.LONG2CLOB needs ROWIDs and table name, which you can't get directly from user_views, but from the underlying objects.

Another approach I implement is using java stored proc/functions to handle longs and convert them to CLOBs or VARCHAR2 (substrings of longs usually).
This is not efficient, this is last effort. (I can use java stored proc to copy long values for instance, I cannot do that using INSERT..SELECT nor CTAS).

Oracle officially says LONG is bad, get rid of it, use CLOB in your code. XXX_VIEWS has TEXT as LONG. Nice. I wish it went away totally. If they insist on bacward compatibility for code: LONG could be a synonym for CLOB (like VARCHAR for VARCHAR2, etc.).

Regards,
Andras

DDL so far...

Duke Ganote, April 15, 2008 - 2:42 pm UTC

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

Duke Ganote, November 06, 2008 - 3:17 pm UTC

Also QUERY text (LONG data_type) in USER_MVIEWS should be unioned.

TEXT_VC in 12c and above.

Rajeshwaran, Jeyabal, June 27, 2018 - 9:27 am UTC

For 12c and above we can make use of the new column TEXT_VC (of varchar2 datatype) added in USER_VIEWS data dictionary.
however for view metadata more than 4000 characters, we need to rely on the above option.

demo@ORA12C> desc user_views
 Name                                Null?    Type
 ----------------------------------- -------- ------------------
 VIEW_NAME                           NOT NULL VARCHAR2(128)
 TEXT_LENGTH                                  NUMBER
 TEXT                                         LONG
 TEXT_VC                                      VARCHAR2(4000)
 TYPE_TEXT_LENGTH                             NUMBER
 TYPE_TEXT                                    VARCHAR2(4000)
 OID_TEXT_LENGTH                              NUMBER
 OID_TEXT                                     VARCHAR2(4000)
 VIEW_TYPE_OWNER                              VARCHAR2(128)
 VIEW_TYPE                                    VARCHAR2(128)
 SUPERVIEW_NAME                               VARCHAR2(128)
 EDITIONING_VIEW                              VARCHAR2(1)
 READ_ONLY                                    VARCHAR2(1)
 CONTAINER_DATA                               VARCHAR2(1)
 BEQUEATH                                     VARCHAR2(12)
 ORIGIN_CON_ID                                NUMBER
 DEFAULT_COLLATION                            VARCHAR2(100)
 CONTAINERS_DEFAULT                           VARCHAR2(3)
 CONTAINER_MAP                                VARCHAR2(3)
 EXTENDED_DATA_LINK                           VARCHAR2(3)
 EXTENDED_DATA_LINK_MAP                       VARCHAR2(3)

demo@ORA12C>
demo@ORA12C> declare
  2    l_sql clob := q'# create or replace view v as select #'  ;
  3  begin
  4    for i in 1..43
  5    loop
  6      l_sql := l_sql || ''''|| rpad('*',100,'*') ||q'# ' c#'||i||',' ;
  7    end loop;
  8    l_sql := trim(',' from l_sql) ||' from dual ';
  9    execute immediate l_sql ;
 10  end;
 11  /

PL/SQL procedure successfully completed.

demo@ORA12C>   select length(text_vc)
  2  from user_views
  3  where view_name = 'V' ;

LENGTH(TEXT_VC)
---------------
           4000

demo@ORA12C>

June 27, 2018 - 9:47 am UTC

Yep, thanks for sharing.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Types, pipelined functions, DBMS_SQL, etc...

SYS.DBMS_METADATA_UTIL.LONG2CLOB

DDL so far...

oops. Need to have user_mviews.query also

TEXT_VC in 12c and above.

More to Explore

PL/SQL demos

PL/SQL docs

How to UNION ALL xxx_VIEWS and xxx_SOURCE ?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Types, pipelined functions, DBMS_SQL, etc...

SYS.DBMS_METADATA_UTIL.LONG2CLOB

DDL so far...

oops. Need to have user_mviews.query also

TEXT_VC in 12c and above.

More to Explore

PL/SQL demos

PL/SQL docs