ORACLE Describe Table

[cbtguy_2000]

Is it possible to execute the DESC tablename command using PHP to a Oracle
DB or is that a *SQLPlus command?

[Malcolm Dew-Jones]

cbtguy_2000 (cb*********@yahoo.com) wrote:
: Is it possible to execute the DESC tablename command using PHP to a Oracle
: DB or is that a *SQLPlus command?

Not exactly, DESCRIBE is actually a command of sqlplus.

In oracle, look up the columns in a table by SELECTing from the
USER_TAB_COLUMNS table.

Oracle has a number of tables such as that one that have data about
tables/colums/etc.

[Mladen Gogala]

On Thu, 28 Jun 2007 23:02:02 -0700, cbtguy_2000 wrote:

Is it possible to execute the DESC tablename command using PHP to a
Oracle DB or is that a *SQLPlus command?

What SQL*Plus does is to describe a cursor. Describing USER_TAB_COLUMNS
is equivalent to describing a cursor "select * from USER_TAB_COLUMNS".
OCI8 has plenty of functions to do just that:
oci_num_fields
oci_field_name
oci_field_is_null
oci_field_precision
oci_field_scale
oci_field_size
oci_field_type
--
http://www.mladen-gogala.com

[cbtguy_2000]

Thanks for your help.
That info was very helpful.

The following SELECT gives the basic same info as DESCRIBE
SQLDESCRIBE BB_TAX
Name
Null? Type
----------------------------------------------------------------- --------
---------------
IDSTATE
NOT NULL NUMBER(2)
STATE
CHAR(2)
TAXRATE
NUMBER(4,3)

SQLcol column_name format a15
SQLcol data_type format a15
SQLcol nullable format a4
SQLSELECT column_name, data_type,
2 data_length, data_precision,
3 data_scale, nullable
4 FROM user_tab_columns
5 WHERE table_name = 'BB_TAX';

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION
DATA_SCALE NULL
--------------- --------------- -----------
--------------- ---------
----
IDSTATE NUMBER 22
2 0
N
STATE CHAR 2
Y
TAXRATE NUMBER 22
4 3
Y

"Mladen Gogala" <mg*****************@verizon.netwrote in message
news:pa*********************@verizon.net...

On Thu, 28 Jun 2007 23:02:02 -0700, cbtguy_2000 wrote:

>Is it possible to execute the DESC tablename command using PHP to a
Oracle DB or is that a *SQLPlus command?

What SQL*Plus does is to describe a cursor. Describing USER_TAB_COLUMNS
is equivalent to describing a cursor "select * from USER_TAB_COLUMNS".
OCI8 has plenty of functions to do just that:
oci_num_fields
oci_field_name
oci_field_is_null
oci_field_precision
oci_field_scale
oci_field_size
oci_field_type
--
http://www.mladen-gogala.com

[Mladen Gogala]

On Sat, 30 Jun 2007 05:45:00 +0000, cbtguy_2000 wrote:

The following SELECT gives the basic same info as DESCRIBE

It does, but it will not work for views or V$ tables.

--
http://www.mladen-gogala.com

[Andy Hassall]

On Mon, 02 Jul 2007 12:33:08 GMT, Mladen Gogala
<mg*****************@verizon.netwrote:

>On Sat, 30 Jun 2007 05:45:00 +0000, cbtguy_2000 wrote:

>The following SELECT gives the basic same info as DESCRIBE

It does, but it will not work for views or V$ tables.

Um, yes it will. Views' columns appear in USER_TAB_COLUMNS.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Bart the Bear]

On Mon, 02 Jul 2007 19:31:52 +0100, Andy Hassall wrote:

On Mon, 02 Jul 2007 12:33:08 GMT, Mladen Gogala
<mg*****************@verizon.netwrote:

>>On Sat, 30 Jun 2007 05:45:00 +0000, cbtguy_2000 wrote:

>>The following SELECT gives the basic same info as DESCRIBE

It does, but it will not work for views or V$ tables.

Um, yes it will. Views' columns appear in USER_TAB_COLUMNS.

You are right, my mistake.

[Mladen Gogala]

On Mon, 02 Jul 2007 19:31:52 +0100, Andy Hassall wrote:

>>It does, but it will not work for views or V$ tables.

Um, yes it will. Views' columns appear in USER_TAB_COLUMNS.

It does work for views, but not for V$ tables:
SQLselect column_name from dba_tab_columns
2 where table_name='V$PROCESS';

no rows selected

SQLselect column_name from dba_tab_columns
2 where table_name='V$SESSION';

no rows selected

SQLselect column_name from dba_tab_columns
2 where table_name='DBA_USERS';

COLUMN_NAME
-----------------------------------
USERNAME
USER_ID
PASSWORD
ACCOUNT_STATUS
LOCK_DATE
EXPIRY_DATE
DEFAULT_TABLESPACE
TEMPORARY_TABLESPACE
CREATED
PROFILE
INITIAL_RSRC_CONSUMER_GROUP
EXTERNAL_NAME

12 rows selected.

--
http://www.mladen-gogala.com

[Andy Hassall]

On Thu, 05 Jul 2007 23:45:10 GMT, Mladen Gogala
<mg*****************@verizon.netwrote:

>On Mon, 02 Jul 2007 19:31:52 +0100, Andy Hassall wrote:

>>>It does, but it will not work for views or V$ tables.

Um, yes it will. Views' columns appear in USER_TAB_COLUMNS.

It does work for views, but not for V$ tables:
SQLselect column_name from dba_tab_columns
2 where table_name='V$PROCESS';

no rows selected

That's because V$PROCESS is a synonym, not a table or view. Dereference it
first.

SQLselect table_owner, table_name
2 from dba_synonyms
3 where synonym_name = 'V$PROCESS'
4 and owner = 'PUBLIC';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
SYS V_$PROCESS

SQLselect column_name from dba_tab_columns
2 where owner = 'SYS'
3 and table_name = 'V_$PROCESS';

COLUMN_NAME
------------------------------
PGA_USED_MEM
PGA_ALLOC_MEM
PGA_FREEABLE_MEM
PGA_MAX_MEM
ADDR
PID
SPID
USERNAME
SERIAL#
TERMINAL
PROGRAM
TRACEID
BACKGROUND
LATCHWAIT
LATCHSPIN

15 rows selected
--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool