Re: A Pro*C question..

From: Alvin Law <alaw_at_oracle.com>
Date: 4 Jan 95 00:58:01
Message-ID: <ALAW.95Jan4005801_at_ap226sun.oracle.com>

In article <tssmithD18v4s.J8H_at_netcom.com> tssmith_at_netcom.com (Tim Smith) writes:

> vasudevy_at_eng.auburn.edu (Vasudev V. Yendapally) writes:
> > I'm trying to "describe" a given table and get the
> >table attributes into some variable names. I'm able to
> >execute any statements that involve "SELECT, INSERT, UPDATE,
> >DELETE " but when it comes to "describe tablename" no
> >action is performed and I simply exit the program.
> >Can someone explain me how I can get these variables to
> >be stored and can be displayed (using Pro*c ofcourse!)?
>
> DESCRIBE is not a standard SQL DML command, like SELECT, INSERT, etc.
> To describe a table into a descriptor that contains fields for
> column name, column datatype, column size, etc. you have
> to use dynamic SQL. Read up on dynamic SQL method 4 in the Pro*C
> documentation.

You can avoid using that beast (Method 4) by using this SQL statement:

select column_name "Name"

,      decode(nullable, 'Y', NULL, 'N', 'NOT NULL') "Null?"
,      data_type || '(' || to_number(data_length) || ')' "Type"

from all_tab_columns
where table_name = :table
order by column_id;

This is not perfect but can get you as close as the DESCRIBE output.

--
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
| Alvin Law .. Project Leader, Applications Division ... Oracle Corporation |
|  Email: alaw_at_us.oracle.com ..... Voice: 415.506.8317 . Fax: 415.506.7299  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel

Received on Wed Jan 04 1995 - 00:58:01 CET