Oracle SQL Developer Describe Command
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Oracle SQL Developer Describe Command

  1. #1
    Join Date
    Dec 2013
    Location
    Buffalo NY
    Posts
    3

    Oracle SQL Developer Describe Command

    Hi Everybody,
    I have a database for a fictional school. It contains tables for faculty, students, course, section, term, enrollment, etc.
    I am trying to write a script that provides all of the information in, and duplicates the formatting of, Oracle's SQL*Plus describe command. The output should add the comments on the rows. the input is: owner (system) and table name (term (or any of my tables)). Output should be: columns for Name, Null?, Type, Comments.

    I enter "system" as owner and Term for table, but the only thing returned is the name of the table, not the other information I need.

    My script looks like this:

    SELECT table_name
    FROM all_tables
    WHERE owner = UPPER('&schemaowner')
    AND table_name LIKE UPPER('%&table%');

    This is the output:

    old:SELECT table_name
    FROM all_tables
    WHERE owner = UPPER('&schemaowner')
    AND table_name LIKE UPPER('%&table%')
    new:SELECT table_name
    FROM all_tables
    WHERE owner = UPPER('system')
    AND table_name LIKE UPPER('%term%')
    TABLE_NAME
    ------------------------------
    TERM


    Thank you

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Have you looked at either dba_tab_columns or user_tab_columns? Also if you are looking at objects in the schema
    that you are logged in as don't use the all_* views, use the user_* views. As in user_tables, user_tab_columns.

    Between user_tables, user_tab_columns and possibly user_cons_columns you should have everything that you need.
    There is also user_ind_columns in case that helps too.
    this space intentionally left blank

  3. #3
    Join Date
    Dec 2013
    Location
    Buffalo NY
    Posts
    3
    thanks for getting back to me Gandolf.
    First of all your suggestions might be good, but I am a bit confused.
    Yes, I, logged in a system. I'm not sure where to put your suggestions. Are you saying user_*views should be in the select statement? I'm not really sure whay my query should look like. I'm rather new at this.
    Thanks for your help.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Which schema owns the objects that you are looking at? If you are logged in as system, then you should
    use the dba_* views. Such as dba_tables, dba_tab_columns, dba_cons_columns and dba_ind_columns.

    From a design perspective, you should not create any objects in sys or system.
    this space intentionally left blank

  5. #5
    Join Date
    Dec 2013
    Location
    Buffalo NY
    Posts
    3
    Well, Gandolf, I guess I just don't get it. It seems like it should be a simple problem, but no matter what i try I get an error, or just the name of the table is returned. as in:
    SELECT table_name
    FROM dba_tables
    WHERE owner = UPPER('&schemaowner')
    AND table_name LIKE UPPER('%&table%');

    Output: term (that was the name of the table I entered at the prompt)

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554

    typo

    if you are looking for the columns and their attributes you should be selecting from dba_tab_columns, filtering by table_name and sorting by table_name if selecting more than one table.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width