EXEC SQL: DESCRIBE of an External Table

[#1]

Hello,

I try to get a description of the all the fields of an (external) oracle table but I'm no to happy yet.

"SELECT" to this oracle connection work but I can't use "DESC"-command successful.

The report looks like this:

REPORT z_desc_ext_table.
 
DATA: wa(500) TYPE c.
 
DATA: dbcon_name(30) TYPE c VALUE 'PDMQ' . "Name in DBCO
 
 
START-OF-SELECTION.
 
  EXEC SQL.
    SET CONNECTION :dbcon_name
  ENDEXEC.
  EXEC SQL.
    connect to :dbcon_name
  ENDEXEC.
 
 
  EXEC SQL.
    open c for
    desc table.in.oracle.
  ENDEXEC.
 
 
  DO.
    EXEC SQL.
      fetch next c into :wa
    ENDEXEC.
    IF sy-subrc  0.
      EXIT.
    ENDIF.
    WRITE: / wa.
  ENDDO.
 
 
  EXEC SQL.
    disconnect :dbcon_name
  ENDEXEC.

When the report is executed the following dump is produced:

...

Database error text........: "ORA-00900: invalid SQL statement"

Triggering SQL statement...: "FETCH NEXT "

Internal call code.........: "DBDS/NEW DSQL"

...

000300 DO.

000310 EXEC SQL.

fetch next c into :wa

000330 ENDEXEC.

000340 IF sy-subrc 0.

000350 EXIT.

000360 ENDIF.

...

Does anyone have an idea how to make this work?

[Former Member]

Thanks for your quick reply.

I think I do not have the rights to read this table. Now the error is:

Runtime errors DBIF_DSQL2_OBJ_UNKNOWN

Exception CX_SY_NATIVE_SQL_ERROR

Table does not exist in database.

[fidel_vales]

Hello Dominik,

desc is not any SQL command that you can perform.

It is a sqlplus command:

http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1009123

and you cannot use from outside SQLPLUS.

From the error you comment, you are correct. If you try to access an external table you must have authorizations at DB level to read it.

[Former Member]

Ok, i already suspected that, as i am a dba, i am used to query dba_* views all the time. Can you please try to query user_tab_columns instead. This should work, if the table is owned by the user you use for the remote access.

Regards

Michael