Isn't DESCRIBE supposed to work in Oracle Developer Tools for VS Code 21.4.0?

User_8HARS

Just starting to use Oracle Developer Tools for VS Code as of today (05-Apr-2022). When attempting to do a DESCRIBE on a table you receive the following:

SQL> DESCRIBE CUST_MM_CENSUS;

ERROR at line 1: ORA-00904: "USER_GENERATED": invalid identifier

The exact same command works perfectly fine in Oracle SQL Developer.

Christian.Shay -Oracle

Yes it is supposed to work. Maybe there's a bug with one of the column types.

Could you please paste the results of the describe command from SQL Developer here so I can try to create a similar table and reproduce it?

User_8HARS

Having created the table I know is doesn't have anything unique about the table, so its unlikely there's an issue with a column type per se. This is in a Cerner-based Oracle Database which, knowing that Oracle is purchasing them means this might be something that will come up more often as more developers migrate to using true Oracle tools.

DESCRIBE in SQL Developer and SQLPlus provides the following:

Name      Null?  Type      

-------------- -------- -------------- 

CENSUS_DATE  NOT NULL VARCHAR2(10)  

CENSUS_TIME  NOT NULL VARCHAR2(8)   

REPORT_DATE  NOT NULL VARCHAR2(10)  

MRN_NUMBER   NOT NULL VARCHAR2(200)  

ACCOUNT_NUMBER NOT NULL VARCHAR2(200)  

ADMISSION_DATE NOT NULL VARCHAR2(10)  

LOCATION    NOT NULL VARCHAR2(2000) 

ROOM_BED    NOT NULL VARCHAR2(2000) 

PATIENT_NAME  NOT NULL VARCHAR2(100)  

PATIENT_DOB  NOT NULL VARCHAR2(10)  

PATIENT_AGE       CHAR(1)     

DIAGNOSIS        VARCHAR2(255)  

MD           VARCHAR2(100)  

PROCESSED_IND      NUMBER 

SQL for table creation is as follows:

 CREATE TABLE CUST_MM_CENSUS (

    CENSUS_DATE  VARCHAR2( 10 BYTE )  CONSTRAINT CUST_MM_CENSUS_NN01 NOT NULL

,    CENSUS_TIME  VARCHAR2( 8 BYTE )  CONSTRAINT CUST_MM_CENSUS_NN02 NOT NULL

,    REPORT_DATE  VARCHAR2( 10 BYTE )  CONSTRAINT CUST_MM_CENSUS_NN03 NOT NULL

,    MRN_NUMBER   VARCHAR2( 200 BYTE ) CONSTRAINT CUST_MM_CENSUS_NN04 NOT NULL

,    ACCOUNT_NUMBER VARCHAR2( 200 BYTE ) CONSTRAINT CUST_MM_CENSUS_NN05 NOT NULL

,    ADMISSION_DATE VARCHAR2( 10 BYTE )  CONSTRAINT CUST_MM_CENSUS_NN06 NOT NULL

,    LOCATION    VARCHAR2( 2000 BYTE ) CONSTRAINT CUST_MM_CENSUS_NN07 NOT NULL

,    ROOM_BED    VARCHAR2( 2000 BYTE ) CONSTRAINT CUST_MM_CENSUS_NN08 NOT NULL

,    PATIENT_NAME  VARCHAR2( 100 BYTE ) CONSTRAINT CUST_MM_CENSUS_NN09 NOT NULL

,    PATIENT_DOB  VARCHAR2( 10 BYTE )  CONSTRAINT CUST_MM_CENSUS_NN10 NOT NULL

,    PATIENT_AGE  CHAR( 1 BYTE )

,    DIAGNOSIS   VARCHAR2( 255 BYTE )

,    MD       VARCHAR2( 100 BYTE )

,    PROCESSED_IND NUMBER        DEFAULT 0

,    CONSTRAINT CUST_MM_CENSUS_PK PRIMARY KEY ( CENSUS_DATE, CENSUS_TIME, LOCATION, ROOM_BED )

      USING INDEX

      TABLESPACE D_CUST

      STORAGE ( INITIAL 1048576 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED )

) TABLESPACE D_CUST

   STORAGE ( INITIAL 1048576 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED );

Christian.Shay -Oracle

That works for me.

What version of the database are you using? I suspect that this error means you are using version 11 or earlier.

User_8HARS

That is correct, Cerner uses 11g (11.2.0.4)

Christian.Shay -Oracle

https://community.oracle.com/tech/developers/discussion/comment/16831823#Comment_16831823

I will double check, but I think this is a bug in that we are issuing a query that uses the USER_GENERATED keyword which was only introduced in Oracle 12.

User_8HARS

Thank you, most appreciated.