How to get Oracle table field definition

**Erik Johnson** · Jul 17 '05, 04:03 AM

Re: How to get Oracle table field definition

The simple answer is that "DESCRIBE" is not standard SQL. RDBMS systems often provide an application to do direct database interaction where you can type SQL directly, but also generally expose a number of other convenience commands. We use PostgreSQL here, and so for example I can simply type "\dt" to get a list of all my tables. I can type "\d <table_name>" to get a description of a particular table. Clearly, "\dt" is not standard SQL to describe a table.

What you execute through PHP does not interact with the SQL*Plus application - it talks SQL directly to the database, and so all these extra convenience commands are not directly available (they are no-doubt implemented behind the scenes in terms of SQL, but it may be fairly complex and there may not be any easy way to see what the implementation is - I don't know).

I wanted to get the same sort of information on my system, and so I had a co-worker who is more adept at SQL write the following query for me:

/* Select statement that will give you the metadata for a particular
table, assuming you have/know the table name at the time of execution

To use replace the 'well' below on the 5th line of the statement
with the table name.

This probably won't work on temporary tables (part of a stored procedure or built on the fly). Might work on a view, I haven't tried.

typnam meanings:
bpchar = character
int4 = integer
int8 = longint

attlen meanings:
-1 = variable length
number of bytes used in storage
*/

SELECT a.attnum, a.attname, t.typname, a.attlen
FROM pg_class as c, pg_attribute a, pg_type t
WHERE a.attnum > 0
AND a.attrelid = c.oid
AND c.relname = 'MyTable'
AND a.atttypid = t.oid
ORDER BY a.attnum;

When executed on our system, substituing 'MyTable' with a valid table name, it produces the following output:

=> \i columns_metadat a.txt
attnum | attname | typname | attlen
--------+-------------+---------+--------
1 | well_api_id | bpchar | -1
2 | operatorid | int4 | 4
3 | leaseid | int4 | 4
4 | well_type | bpchar | -1
5 | depth_ft | int8 | 8

'attname' here is a column of column names, typname is the data type for that column, attlen is the size of that column.

So, the good news is that all of the same data is no doubt stored on your system in various meta-data tables. The bad news is that it is probably all under differently named tables with differently named columns, so I don't expect you will be able to run the query above. You will have to dig in and find out what your meta-data tables are named and what their column names are, and figure out how to put together a similar SQL query that will select what you need.

-ej

"Thierry B." <nospam@nospam. com> wrote in message news:c2icrv$487 $1@news-reader5.wanadoo .fr...[color=blue]
> Hi,
>
> from SQL*Plus, i use:
>
> DESCRIBE MyTable
>
> and I get this result (example):
>
> Nom NULL ? Type
> ----------------------------------------- -------- ------------------------
> ----
> FIELD1 NOT NULL NUMBER(38)
> FIELD2 VARCHAR2(30)
>
> From PHP, the same command output:
>
> DESCRIBE MyTable
>
> Execute failed: ORA-00900: invalid SQL statement
>
>
> Any idea?
>
> TB
> --
> Click here to answer / cliquez ci dessous pour me repondre
> http://cerbermail.com/?7O7SOrggJg
>
>[/color]

**Brandon** · Jul 17 '05, 04:14 AM

Re: How to get Oracle table field definition

"Thierry B." <nospam@nospam. com> wrote in message
news:c2icrv$487 $1@news-reader5.wanadoo .fr...[color=blue]
> Hi,
>
> from SQL*Plus, i use:
>
> DESCRIBE MyTable
>
> and I get this result (example):
>
> Nom NULL ? Type
> ----------------------------------------- -------- ----------------------[/color]
--[color=blue]
> ----
> FIELD1 NOT NULL NUMBER(38)
> FIELD2 VARCHAR2(30)
>
> From PHP, the same command output:
>
> DESCRIBE MyTable
>
> Execute failed: ORA-00900: invalid SQL statement
>
>
> Any idea?
>
> TB
> --
> Click here to answer / cliquez ci dessous pour me repondre
> http://cerbermail.com/?7O7SOrggJg
>
>[/color]

Depends on what version you are using so in Oracle9i you can use:

select dbms_metadata.g et_ddl( 'TABLE', 'MyTable', 'OraUserName' ) from dual;

In other versions of Oracle, take a look at the user_tab_column s view and do
something like this:

select column_name,dat a_type from user_tab_column s where table_name =
MyTable;

Note also that you need the 'resource' role granted to you (in addition to
connect role) by the dba in order to get metadata from the data dictionary.

Brandon

**Andy Hassall** · Jul 17 '05, 04:15 AM

Re: How to get Oracle table field definition

On Wed, 17 Mar 2004 03:12:50 GMT, "Brandon" <brandon.lyon@m chsi.com> wrote:
[color=blue]
>"Thierry B." <nospam@nospam. com> wrote in message
>news:c2icrv$48 7$1@news-reader5.wanadoo .fr...[color=green]
>>
>> DESCRIBE MyTable
>>
>> and I get this result (example):
>>
>> Nom NULL ? Type
>> ----------------------------------------- -------- ----------------------[/color]
>--[color=green]
>> ----
>> FIELD1 NOT NULL NUMBER(38)
>> FIELD2 VARCHAR2(30)
>>
>> From PHP, the same command output:
>>
>> DESCRIBE MyTable
>>
>> Execute failed: ORA-00900: invalid SQL statement[/color]
>
>Depends on what version you are using so in Oracle9i you can use:
>
>select dbms_metadata.g et_ddl( 'TABLE', 'MyTable', 'OraUserName' ) from dual;
>
>In other versions of Oracle, take a look at the user_tab_column s view and do
>something like this:
>
>select column_name,dat a_type from user_tab_column s where table_name =
>MyTable;[/color]

With quotes around 'MYTABLE' and the name in uppercase, most likely - unless
it was created as "MyTable".
[color=blue]
>Note also that you need the 'resource' role granted to you (in addition to
>connect role) by the dba in order to get metadata from the data dictionary.[/color]

No you don't. Anyone can access USER_TAB_COLUMN S as SELECT is granted to
PUBLIC and there's a public synonym (unless your DBA is paranoid and has
removed them).

SQL> create user unprivileged identified by unprivileged;

User created.

SQL> grant create session to unprivileged;

Grant succeeded.

SQL> connect unprivileged/unprivileged;
Connected.
SQL> select * from user_tab_column s;

no rows selected

In fact CONNECT and RESOURCE don't have any DML privileges, they only have
system privileges such as CREATE TABLE etc.

--
Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>

How to get Oracle table field definition

How to get Oracle table field definition

Comment

Comment

Comment