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]

Hello,

The following program works (I've used EXEC_SQL PERFORMING instead of a cursor but that should not make any difference):

REPORT  z_describe_table NO STANDARD PAGE HEADING.

PARAMETERS:
  p_conn        TYPE dbcon_name,
  p_owner       TYPE char30,
  p_table       TYPE tabname.

TYPES: BEGIN OF ty_tab_desc,
  colname       TYPE char30,
  datatype      TYPE char20,
  datalen       TYPE i,
  precision     TYPE i,
END OF ty_tab_desc.

DATA: gt_columns   TYPE TABLE OF ty_tab_desc,
      gw_columns   LIKE LINE OF gt_columns.


EXEC SQL.
  set connection :p_conn
ENDEXEC.
IF sy-subrc <> 0.
  WRITE: / 'ERROR: cannot set connection to', p_conn.
  STOP.
ENDIF.
EXEC SQL.
  CONNECT TO :p_conn
ENDEXEC.


EXEC SQL PERFORMING append_col.
  select column_name, data_type, data_length, data_precision
         into :gw_columns-colname,
              :gw_columns-datatype,
              :gw_columns-datalen,
              :gw_columns-precision
         from dba_tab_columns
         where owner = :p_owner and table_name = :p_table
ENDEXEC.

LOOP AT gt_columns INTO gw_columns.
  WRITE: / gw_columns-colname,
           gw_columns-datatype,
           gw_columns-datalen,
           gw_columns-precision.
ENDLOOP.

EXEC SQL.
  disconnect :p_conn
ENDEXEC.



*&---------------------------------------------------------------------*
*&      Form  append_col
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM append_col.
  APPEND gw_columns TO gt_columns.
ENDFORM.                    "append_col

Can you try it out and let us know?

Rgds,

Mark M