1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Get column names from multiple views using pl/sql

Discussion in 'SQL PL/SQL' started by Sparamanga, Jan 19, 2017.

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi,

    I was trying to retrieve all the columns names from over 100 different views in an oracle database earlier today. I wasn't quite sure how to do it, so to save time I used query to get all the view names, then simply wrote a query that selected the view_name and column names from all views where view_name in (list of views).

    I was thinking about writing looping through all the view_names in the database and for each iteration storing the column_names in a variable and the outputting this to the screen.

    As each view can have differing number of columns though would I have to store the column names in a record variable?

    Then how would I retrieve the column names for each view name stored in this record variable?

    Am I taking the right approach?

    Thanks,

    Rob.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,566
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Ok thanks Zargon. I'll have a look into collections and will let you know how I get on :)
     
  4. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi Zargon,

    I had a look at collections. Would I need to use an associative array?

    I understand you define your own variable type and declare a variable using that type to be utilised in the array as in..

    Code (SQL):

    DECLARE

    TYPE my_holder IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
    my_variable my_holder;
    But I need to have each view name as the index of the array, and then for each index i need to have multiple values for the different columns names.

    The examples i have been reading so far assign a single value to each index name.

    If i had one index called -rob_view_1 for instance and I wanted to assign the column names rob_col_1, rob_col_2, rob_col_3 to this one index is that possible?

    Or is that what is know as a multi-dimensional array?

    Thanks,

    Rob.
     
  5. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    There is a simple way for me to achieve the results I need..

    SELECT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME
    IN (
    SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER='ROB1'
    )

    But as I have been trying to do it with PL/SQL for the last few hours, I will continue!
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,566
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You should use a varray; look at the third example in the post that uses the dfarray type as it should give you an idea of how to do this.