cancel
Showing results for 
Search instead for 
Did you mean: 

EXEC SQL: DESCRIBE of an External Table

Former Member
0 Kudos
607

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?

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Due to authorization problems I couldn't check but I think the solution would work

Former Member
0 Kudos

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

Former Member
0 Kudos

The Query

 SELECT * FROM USER_TAB_COLUMNS ...

does not create any result nor any error, it's just empty.

Former Member
0 Kudos

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
Employee
Employee
0 Kudos

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
0 Kudos

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

Former Member
0 Kudos

You can query dba_tab_columns, alternatively to desc <table>:

SQL> desc sapsr3.t000;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MANDT                                     NOT NULL VARCHAR2(9)
 MTEXT                                     NOT NULL VARCHAR2(75)
...
 LOGSYS                                    NOT NULL VARCHAR2(30)

SQL> select column_name from dba_tab_columns where table_name = 'T000';

COLUMN_NAME
------------------------------
MANDT
MTEXT
....
LOGSYS

But off course you need to have permissions to query this oracle inventory view then.

Best regards

Michael