Execute a "DESC TABLE" command in Oracle using JavaScript?

Question

I need to find the column info for a number of Oracle views using JavaScript. What I've tried is:

var conObj = new ActiveXObject('ADODB.Connection');     
conObj.Open(conString);     
sql = "DESC MyView";
rs1 = new ActiveXObject("ADODB.Recordset");

var commd = new ActiveXObject('ADODB.Command');

commd.ActiveConnection = conObj; //

commd.CommandText = sql;

commd.CommandType = 1;

rs1 = commd.Execute();

What I get is an error about the sql statement. I know the sql works in SQL Developer.

I can't use the alternative SELECT query as that returns an empty rowset - the view isn't populated when I need to run the query. Only the DESC returns the values.

Any ideas?

Where is this code supposed to run? Where is the database server? — Pointy
– Pointy, Commented Jun 24, 2014 at 17:53

Maheswaran Ravisankar · Accepted Answer · 2014-06-24 18:27:44Z

8

DESC is a SQL*Plus command. SO, you cannot use it via JDBC/ODBC. An alternative can be like this below.

select RPAD(COLUMN_NAME,30)||' '||DATA_TYPE||'('||DATA_LENGTH||')' as descr
FROM all_tab_cols
  WHERE TABLE_NAME = UPPER('YOUR_TABLE') and owner=UPPER('SCHEMA_NAME');

Oracle's Reference

all_tab_cols is a data dictionary table(view) which contains the table/view metadata.

Example:

SQL> create view MyView as select * from dual where 1=2;

View created.

SQL> select RPAD(COLUMN_NAME,30)||' '||DATA_TYPE||'('||DATA_LENGTH||')' as descr
  2  FROM all_tab_cols
  3   WHERE TABLE_NAME ='MYVIEW'  and owner='MYSCHEMA';


DESCR
------------------------------------------------
DUMMY                          VARCHAR2(1)

Data and DESC

SQL> select * from myview;

no rows selected

SQL> desc myview;

 Name       Null?    Type
 -----------------------------
 DUMMY               VARCHAR2(1)

edited Jun 24, 2014 at 18:27

answered Jun 24, 2014 at 17:56

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Frank Perry Over a year ago

I just tried plugging that in and it didn't like the FROM location. I have tried : sql = "select * from user_tab_cols where table_name = 'mytable'" but that comes back empty.

Maheswaran Ravisankar Over a year ago

The alias ,I chose was actually a reserved keyword,and hence it is. Try now please. Also, the table name has to be all CAPS

Patrick Bacon Over a year ago

You could double quote the alias, "desc". Also, data_length is of type NUMBER (Oracle implicitly cast this as a varchar).

Frank Perry Over a year ago

That gets past the problem with the FROM but it returns an empty rowset. I think the problem there is that there aren't any rows in the view. It isn't populated until later. The DESC command does work - I assume it is using something deeper in Oracle. When I run the DESC command I get a text string describing the table but I don't know how I would extract that.

Maheswaran Ravisankar Over a year ago

@Patrick double quotes would work, but I personally dont use just because, while fetching I have to use the same case. (if I dont choose the column index). And type casting wont make an impact here I guess. But still user can include individual columns as well.

|

Varun · Accepted Answer · 2015-10-05 05:53:02Z

0

I agree with Maheswaran's query. Additionally, I tried the below query to get all the information. This lets me know about the different constraints and attributes.

select * FROM all_tab_cols WHERE TABLE_NAME = UPPER('TABLE_NAME') and owner=UPPER('SCHEMA_NAME');

answered Oct 5, 2015 at 5:53

Varun

1232 silver badges8 bronze badges

Collectives™ on Stack Overflow

Execute a "DESC TABLE" command in Oracle using JavaScript?

2 Answers 2

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related