Class cl_sql_metadata_ora for Cluster Tables???

[Former Member]

I am trying to use class CL_SQL_METADATA_ORA to get oracle level metadata for any database table. I am able to succesfully use this for transparent tables, but for some reason my code doesnt work for Cluster tables(maybe because of the way they are stored in the database). Is there any other way I can get the oracle level metadata for cluster tables. Any inputs or suggestions are highly appreciated.

Here is the piece of code:

   DATA:
    sqlerr_ref   TYPE REF TO cx_sql_exception,
    md_ref       TYPE REF TO cl_sql_metadata,
    tabdescr_tab TYPE adbc_table_descr_tab,
    schema_rgtab TYPE adbc_name_rgtab,
    table_rgtab  TYPE adbc_name_rgtab.
  FIELD-SYMBOLS:
    <tab_rg>     LIKE LINE OF table_rgtab,
    <tabdescr>   LIKE LINE OF tabdescr_tab,
    <schema>     LIKE schema,
    <tabname>    LIKE tabname.

  md_ref = _con_ref->get_metadata( ).

  TRY.
      IF schema IS INITIAL.
        APPEND INITIAL LINE TO table_rgtab ASSIGNING <tab_rg>.
        <tab_rg>-sign = 'I'.
        <tab_rg>-option = 'EQ'.
        <tab_rg>-low = tabname.
        CALL METHOD md_ref->get_tables
          EXPORTING
            schema_rgtab    = schema_rgtab
            table_rgtab     = table_rgtab
          IMPORTING
            table_descr_tab = tabdescr_tab.

        READ TABLE tabdescr_tab INDEX 1 ASSIGNING <tabdescr>.
        IF sy-subrc <> 0.
          WRITE: 'Table not found:', tabname.               "#EC NOTEXT
          p_subrc = 1.
          RETURN.
        ENDIF.
        ASSIGN <tabdescr>-schema TO <schema>.
        ASSIGN <tabdescr>-table_name TO <tabname>.
        _qualified_tabname = tabname.
      ELSE.
        ASSIGN schema TO <schema>.
        ASSIGN tabname TO <tabname>.
        CONCATENATE schema '.' tabname INTO _qualified_tabname.
      ENDIF.
*     get all columns of the table/view from the database catalog
      CALL METHOD md_ref->get_columns
        EXPORTING
          schema_name = <schema>
          table_name  = <tabname>
        IMPORTING
          column_tab  = _column_tab.
      DESCRIBE TABLE _column_tab LINES _col_cnt.
      IF _col_cnt = 0.
        WRITE: 'Table not found:', tabname.                 "#EC NOTEXT
        p_subrc = 1.
        RETURN.
      ENDIF.
    CATCH cx_sql_exception INTO sqlerr_ref.
      PERFORM handle_sql_exception(adbc_demo) USING sqlerr_ref.
      p_subrc = 2.
      RETURN.
  ENDTRY.

[Former Member]

Hi,

Did you try passing the cluster name instead of table name?

you can get the table type from table DD02L .

I think it will work because the cluster would be available.

Thanks

AJR

[Former Member]

I did try sending a cluster table, but the get_table method does not return anything. The table type in the get table method is a constant with type T for transparent table.

[Former Member]

I tested the class method in SE24 , it returned some values but i didnt pass table type.

[Former Member]

Did you try a "Cluster" table. It does not return anything when i run the class. Which table did you run?

[Former Member]

I tried BSEG table's Cluster name RFBLG .

[Former Member]

See the problem is if I use RFBLG in the class the get columns method returns only 8 entries where as BSEG has 313 entries which is not what i want.

[JanStallkamp]

Hi.

I'm not an expert of CL_SQL_METADATA_ORA but I know a little bit about the SQL metadata class of another database. I think it would be a good idea to ask your question in the "Sap on Oracle" forum as the database experts might spend their time there.

Second: I'm not sure if SAP has ever documented this classes or documented them for public use. There might be changes in the code of this classes in the future.

The class CL_SQL_METADATA and the classes derived from it are directly accessing the database to get their information. You are bypassing DDIC by using them. Perhaps you can give us an idea what you want to achive by getting metadata directly from the database. Perhaps there is another way to do this.

If you want to use CL_SQL_METADATA_ORA I would strongly recommend to write your coding in a way that checks the database type and the decides which metadata class to use. If you don't do this you will end up with coding that cannot be used after a possible database migration.

Regards,

Jan

[Former Member]

Jan,

Thank you for your response. I have developed a utility which replicates any SAP table to a Non Sap system. I generate a "create table script file" and a "data file" in my program which when transferred to the non sap system automaticaly triggers the script creates the table and loads the data in their database. For the script file I need the Metadata as stored in the SAP database which i retrieve using cl_sql_metadata and then map it to get the equivalent datatype in the destination database.

I do check what the underlying SAP database is and then select the appropriate cl_sql_metadata class. So Database migration is not an Issue. The only problem I am facing with this utility is I cannot create the script file for cluster tables as i cannot get the oracle metadata.

I will also try the other forum you mentioned.

Thanks.

[JanStallkamp]

Hi.

> Thank you for your response. I have developed a utility which replicates any SAP table to a Non Sap system. I generate a

> "create table script file" and a "data file" in my program which when transferred to the non sap system automaticaly triggers

> the script creates the table and loads the data in their database. For the script file I need the Metadata as stored in the SAP

> database which i retrieve using cl_sql_metadata and then map it to get the equivalent datatype in the destination database.

The problem with using CL_SQL_METADATA is that you cannot create a really "clone" of the table with it. There is information about database tables (like partitioning) that is not available via this classes. Beside that there is data about tables that is stored in the DDIC.

I think there is no easy way to "clone" tables. Why do you need to copy many SAP tables to another system?

> I do check what the underlying SAP database is and then select the appropriate cl_sql_metadata class. So Database

> migration is not an Issue. The only problem I am facing with this utility is I cannot create the script file for cluster tables as i

> cannot get the oracle metadata.

Be carefull when writing this code! It is not easy to write code that works on all database platforms. For example the schema range tab is very important when calling get_tables from CL_SQL_METADATA_DB4.

Keep in mind that you might want to copy primary keys or indexes too.

Best regards,

Jan Stallkamp

[Former Member]

Thanks for responding again

> The problem with using CL_SQL_METADATA is that you cannot create a really "clone" of the table with it. There is information

> about database tables (like partitioning) that is not available via this classes. Beside that there is data about tables that is stored

> in the DDIC. I think there is no easy way to "clone" tables. Why do you need to copy many SAP tables to another system?

I agree with your concerns but my goal is NOT to get a exact clone of the SAP table. We have multple SAP and Non SAP systems in our landscape and we have one Data management organization which tries to ensure that related data(normally master data) in all systems are in sync. They run regular reports to ensure this. These reports need data from all these systems. So my goal is to give them the needed data for thier analysis. They just need some fields from selected tables.

> Be carefull when writing this code! It is not easy to write code that works on all database platforms. For example the schema range tab is very important when calling get_tables from CL_SQL_METADATA_DB4.

Keep in mind that you might want to copy primary keys or indexes too.

I agree, but we are just thinking short term and we dont anticipate any major database changes in near future.

[JanStallkamp]

Hi.

Short remark: If you quote someone please use the > sign and don't use the code-tags. There is no line wrapping in quote blocks which results in extreme long lines. I've removed that from your postings.

> I agree with your concerns but my goal is NOT to get a exact clone of the SAP table. We have multple SAP and Non SAP systems in our landscape and we have one Data management organization which tries to ensure that related data(normally master data) in all systems are in sync. They run regular reports to ensure this. These reports need data from all these systems. So my goal is to give them the needed data for thier analysis. They just need some fields from selected tables.

Ok, that's fine. I just worked on a project where cloning was one of the ideas and that is really quite complex...

> > Be carefull when writing this code! It is not easy to write code that works on all database platforms. For example the schema range tab is very important when calling get_tables from CL_SQL_METADATA_DB4.

> Keep in mind that you might want to copy primary keys or indexes too.

>

> I agree, but we are just thinking short term and we dont anticipate any major database changes in near future.

If you are aware of this and decide to just do it on the one database you use at the moment I'm fine with this. I just wanted to point to this topic because many people are not aware of all the issues that can arise from it. And as ABAP offers OpenSQL as an abstraction layer from the real database many ABAP programmers don't have much knowledge about database specific programming. And it's better to give a warning in the forums to someone who already knew about this than to let someone without this knowledge run into problems.

Best regards,

Jan