3

I'm trying to get the description of a table in oracle because I need to know what foreign keys does it have and which tables they refer to. I'm using desc but all I get is "ORA-00900: invalid SQL statement" Any idea why? Thanks.

1
  • Can you show us the command you use for DESC ? Also, you will not be able to track all keys with this command, but at least you should be able to do without getting an error.
    – tvCa
    Dec 29, 2014 at 10:32

3 Answers 3

6

I suspect that you did not execute this command with sqlplus. Not every sql program can handle the desc command.

You can either use sqlplus or get the table definition with the following SQL command:

SELECT dbms_metadata.get_ddl (object_type, object_name, owner)
FROM   all_objects
WHERE  owner = '<owner>'
  AND  object_name LIKE '<whatever_table>%';

This will give you the complete table definition with all column definitions.

1
  • This only shows a small (cropped) version of table description.
    – bitifet
    Apr 22, 2020 at 8:52
0

The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.

you action should be Correct the syntax or install the Procedural Option.

0

first of all, the DESC statement causes ORA-00900 when used in a programming environment, like PHP or Python. o0x258's answer above returns only the CREATE statement of the given table. if you are interested in the fields themselves, you should try something like:

select sys.all_tab_columns.*, sys.all_indexes.uniqueness
from sys.all_tab_columns
left join sys.all_ind_columns
  on sys.all_ind_columns.index_owner = sys.all_tab_columns.owner
  and sys.all_ind_columns.table_name = sys.all_tab_columns.table_name
  and sys.all_ind_columns.column_name = sys.all_tab_columns.column_name
left join sys.all_indexes
  on sys.all_indexes.owner = sys.all_tab_columns.owner
 and sys.all_indexes.table_name = sys.all_tab_columns.table_name
  and sys.all_indexes.index_name = sys.all_ind_columns.index_name
  and sys.all_indexes.index_type = 'NORMAL'
  and sys.all_indexes.status = 'VALID'
where sys.all_tab_columns.owner = '### OWNER ###'
  and sys.all_tab_columns.table_name = '### TABLE ###'
order by sys.all_tab_columns.column_id

hope that helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.