Re: Doing a DESC in Pro*C

From: John Strange <jstrange_at_imtn.dsccc.com>
Date: 11 Apr 1999 12:16:28 GMT
Message-ID: <7eq3qs$5h$1@relay1.dsccc.com>

This will bypass trying to run a DESC

/*****************************************************************
*
*       desc_tbl.sql - Describe user's tables with comments.
*
*       usage - sqlplus scott/tiger @desc_tbl.sql
*
*       output - desc_tbl.lis  (180 col)

*

*****************************************************************/
 

column data_type format a15
column column_name format a25
column comments format a70
column table_name format a23  

set linesize 180
set pagesize 6000
set pause off
set term off  

break on table_name skip 1 nodup  

spool desc_tbl.lis  

column osid format a10
column user format a10
select user, lower (substr (global_name, 1, (instr (global_name, '.') -1))) osid from global_name , dual
/
select tc.table_name

  , tc.column_name
  , tc.nullable
  , decode (rtrim (tc.data_type),

'CHAR', DATA_TYPE || '(' || DATA_LENGTH || ')',
'NUMBER', DATA_TYPE || '(' || DATA_PRECISION || '.' || DATA_SCALE || ')',
'DATE', DATA_TYPE,
'VARCHAR2', DATA_TYPE || '(' || DATA_LENGTH || ')',
'LONG', DATA_TYPE, data_type

    ) DATA_TYPE
  , cc.comments
 FROM user_tab_columns tc, user_col_comments cc WHERE cc.table_name = tc.table_name
  and cc.column_name = tc.column_name
order by tc.table_name, column_id
/
spool off
exit ;

/****************** end        desc_tbl.sql ********************************/

Pauil Perkins (paulp_at_despam.penguinpowered.com) wrote: : Hi All,

: Is there a way to perform a "DESC" on a table using Pro*C.  I want to
: enumerate the column names and types for a particular table using Pro*C at
: runtime.

: I've seen that you can do a DESCRIBE on a prepared Dynamic SQL statement,
: but this

: (I think) enumerates the types of the columns contained in the statement - : i.e. requires prior knowledge of the table.

: I'm trying to write a tool that sniffs out the available tables and displays : the available columns along with column types.

: Any ideas?

: Thanks,

: Paul.

--
While Alcatel may claim ownership of all my ideas (on or off the job), Alcatel does not claim any responsibility for them. Warranty expired when u opened this article and I will not be responsible for its contents or use. Received on Sun Apr 11 1999 - 07:16:28 CDT