Store values returned by a select statement in an array and display it 2

[pjeypal]

I need to list public views, their owners and column names in each view

I have written the query to display views and owner names but cant figure out how to display column names for each view under the view. I am adding my script below

Could someone help me with this

declare
viewname varchar2(200);
owner_name varchar2(200);
CURSOR T1Cursor is
select view_name,owner from all_views where view_name like 'PUB%';

begin

open T1Cursor;

LOOP
FETCH T1Cursor into viewname,owner_name;
EXIT WHEN T1Cursor%NOTFOUND;
dbms_output.put_line(viewname||' '|| owner_name);

/* NEED to DISPLAY COLUMN NAMES OF EACH VIEW OBTAINED FROM CURSOR*/

/*select column_name from all_tab_columns where table_name=viewname;*/

END LOOP;
CLOSE T1Cursor;
END;

Thanks

 

[SantaMufasa]

Pjeypal,

Here is a simpler set of code that does what you want:

set serveroutput on format wrap
begin
    for v in (select owner, view_name
                from all_views
               order by 1,2) loop
        dbms_output.put_line(v.owner||': '||v.view_name);
        for c in (select column_id, column_name
                    from all_tab_columns
                   where v.owner||'.'||v.view_name = owner||'.'||table_name
                   order by column_id) loop
            dbms_output.put_line('    '||c.column_id||': '||c.column_name);
        end loop;
    end loop;
end;
/

SYS: ALL_ALL_TABLES
    1: OWNER
    2: TABLE_NAME
    3: TABLESPACE_NAME
    ...
    50: COMPRESSION
SYS: ALL_APPLY
    1: APPLY_NAME
    2: QUEUE_NAME
    3: QUEUE_OWNER
    ...
    12: STATUS
...(all other views and their columns)

PL/SQL procedure successfully completed.

The amount of output is/will be horrendously large, but you know that already.

Let us know if this does what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pjeypal]

Thanks a lot Mufasa. It is working fine. Really appreciate
your help

 

[BJCooperIT]

pjeypal,
I know it must have slipped your mind to award a purple star of thanks to Dave for all his help. Let me do it for you...

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[pjeypal]

yeah .I have voted now. He responded so fast . Thanks for reminding me