REM
REM NAME:
REM   xtable.sql
REM
REM SYNTAX:
REM   xtable.sql
REM
REM FUNCTION:
REM   get xtable brief definition
REM
REM PARAMETER:
REM   see prompt
REM
REM NOTE:
REM   please run as sys user
REM
REM MODIFIED:
REM   rudolf    2004/08/31  create

accept ViewName prompt 'Which internal view you would to look for:'

select view_name,view_definition
  from v$fixed_view_definition where upper(view_definition) like upper('%&ViewName%');


accept ViewName prompt 'Please select one v$ view:'


declare
    type t_tab is table of varchar2(512) index by binary_integer;
    v_tab1  t_tab;
    v_tab2  t_tab;
    v_stmt  varchar2(4000);
    v_vname varchar2(30) := '&ViewName';
    procedure print(p_str varchar2)
    is
        v_str varchar2(4000) := p_str;
        v_linesize number(3) := 100;
    begin
        for i in 0.. floor(length(v_str)/v_linesize) loop
            dbms_output.put_line(substr(v_str,i * v_linesize + 1, v_linesize));
        end loop;
    end;
    function split( p_str varchar2 )
    return t_tab
    is
        v_str varchar2(4000):= p_str;
        v_tempstr varchar2(4000);
        v_char varchar2(4);
        v_cnt number(2) := 0;
        v_tab t_tab;
        v_pos number(4);
        v_tname varchar2(200);
    begin
        -- replace all ',' not in parenthsis with '`' till meeting 'from' clase
        v_str := substr(replace(lower(v_str),'from','`'),7);
        for i in 1..length(v_str)
        loop
            v_char := substr(v_str,i,1) ;
            case
                -- no consideration for '(' enclosed by '"'
                when v_char = '(' then
                    v_cnt := v_cnt + 1;
                when v_char = ')' then
                    v_cnt := v_cnt - 1;
                when v_char = '`' then
                    if v_cnt > 0 then
                        v_char := 'from';
                    else
                        if instr(v_str,'where',i) > 0 then
                            v_tname := substr(v_str,i + 1,instr(v_str,'where',i) - i - 1);
                        else
                            v_tname := substr(v_str,i + 1);
                        end if;
                        exit;
                    end if;
                when v_char = ',' then
                    if v_cnt = 0 then
                        v_char := '`';
                    end if;
                else
                    null;
            end case;
                v_tempstr := v_tempstr||v_char;
        end loop;
        -- splitting string by '`'
        v_cnt := 1;
        loop
            v_pos := instr(v_tempstr,'`');
            if  v_pos = 0 then
                v_tab(v_cnt) := ltrim(rtrim(v_tempstr));
                exit;
            else
                v_tab(v_cnt) := ltrim(rtrim(substr(v_tempstr, 1, v_pos - 1)));
                v_cnt := v_cnt + 1;
                v_tempstr := substr(v_tempstr, v_pos + 1);
            end if;
        end loop;
        dbms_output.put_line('View name:'||v_tname);
        return v_tab;
    end split;
begin
    select VIEW_DEFINITION into v_stmt from v$fixed_view_definition
      where view_name = upper(v_vname);
    v_tab1 := split(v_stmt);
    print('View Definition:'||v_stmt);
    
    dbms_output.put_line('-----');
    
    select VIEW_DEFINITION into v_stmt from v$fixed_view_definition
      where view_name = upper('G'||v_vname);
    v_tab2 := split(v_stmt);
    print('View Definition:'||v_stmt);
    
    dbms_output.put_line('-----');
    dbms_output.put_line('MEANINGFUL NAME              X$ TABLE COLUMN NAME');
    dbms_output.put_line('=====================        ======================');
    for i in v_tab1.first .. v_tab1.last loop
        dbms_output.put_line(rpad(v_tab1(i),30)||v_tab2(i+1));
    end loop;
exception
    when no_data_found then
        dbms_output.put_line('Couldn''t find corresponding view named g'||v_vname||' or '||v_vname);
end;
/