2

I am creating a php page for a team of application testers who need to frequently see the content of a oracle database as part of the testing process. The page takes the sql query from a text area and uses oci8 libraries to execute it against the database.

However as the command describe (short desc) is a feature of Oracle SQL*Plus, I am trying to emulate it's functionality inside a sql query. Here's what I've come up with until now-

SELECT column_name "Name",
                CASE WHEN nullable = 'Y' THEN 'NULL'
                WHEN nullable = 'N' THEN 'NOT NULL'
        END AS "Null",
   concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM all_tab_columns
WHERE table_name='{$TABLE}'

This seems to be working for most of the tables, but not for "v$database" or "v$instance". I understand that the references to these system views are not present inside "all_tab_columns" and the users will not be searching for them, but I want the query to work for all tables and views just for the sake of completeness.

So if anyone can suggest a better way, please guide me.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
Anindya Mukherjee
  • 63
  • 5
  • 3
    `v$database` is not a view. It's a (public) synonym pointing to a view. –  Sep 15 '14 at 09:08
  • ... and this does work if you supply the name of the view that synonym points to, `V_$DATABASE`. – Alex Poole Sep 15 '14 at 09:12
  • Thanks for the info. My knowledge about oracle architecture is rather limited, and it's the reason i'm stumbling with this task. Using the actual view name worked, thanks for that. But what if some other table or view name doesn'r work, thats why i'm looking for a little guidance of whether this method will work for all possible usage or there is another and possibly better way. – Anindya Mukherjee Sep 15 '14 at 09:41
  • 2
    About 99.9% of your question is a duplicate of [How can I describe a table in Oracle without using the DESCRIBE command?](http://stackoverflow.com/questions/9855209/how-can-i-describe-a-table-in-oracle-without-using-the-describe-command) but you actually seem to be asking why `V$DATABASE` is not in `ALL_TAB_COLUMNS`. Which question do you want answered? – Ben Sep 15 '14 at 09:42

1 Answers1

0

You can use dbms_sql.describe_columns2 to get the same information as describe does.

It needs quite some effort to get it actually working: you have to parse the statement and get the metadata from it. The nice thing is: it works for virtually any query. Even for views, or queries that contain calculated columns, joins, etc.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325