on 07-14-2008 1:15 PM
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?
Due to authorization problems I couldn't check but I think the solution would work
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The Query
SELECT * FROM USER_TAB_COLUMNS ...
does not create any result nor any error, it's just empty.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.