CODEWITHSUNDEEP
javascriptsqloracle-database
Frank Perry
Frank Perry

Reputation: 15

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

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?

Upvotes: 1

Views: 18803

Answers (2)

Varun
Varun

Reputation: 123

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');

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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)

Upvotes: 8

Related Questions