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 md = new ActiveXObject('ADODB.Command');

md.ActiveConnection = conObj; //

md.CommandText = sql;

md.CommandType = 1;

rs1 = md.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?

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 md = new ActiveXObject('ADODB.Command');

md.ActiveConnection = conObj; //

md.CommandText = sql;

md.CommandType = 1;

rs1 = md.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?

• javascript
• sql
• oracle-database

• Where is this code supposed to run? Where is the database server? – Pointy Commented Jun 24, 2014 at 17:53
• DESC is a SQL*Plus mand. SO, you cannot use it. – Maheswaran Ravisankar Commented Jun 24, 2014 at 17:53

2 Answers 2

DESC is a SQL*Plus mand. 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)

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