How to get field names of external db table

[Former Member]

Hello,

is there a way how to get field names of external db table?

i have external db (Oracle) connected like this but i don't have field names of table i want to read and have no other way how to get it since my coleague is off.

exec sql.

Connect to : db_con_gis

endexec.

exec sql.

set connection: db_con_gis

endexec.

Thank you

[Former Member]

Hi

I usually uses TOAD Toad for Oracle | Oracle Database Tools | SQL Development & Administration | Dell Software

There's a trial version you can try to use it

[Former Member]

Unfortunately i can't install any additional software but thank you. Does anyone know if there is any query for this?

[RaymondGiuseppi]

There are tables in Oracle that contains this information, look at ALL_TAB_COLUMNS.

Regards,
Raymond

[Sandra_Rossi]

typo: ALL_TAB_COLUMNS (with one N)

[RaymondGiuseppi]

Corrected 🙂

[Juwin]

You can take help from cl_sql_connection & cl_sql_result_set to get this. Example code to display the list of fields with their types are given below. I tried it in my system, and it works for me.

data: dbconn type ref to cl_sql_connection,

      results type ref to cl_sql_result_set,

      error type ref to cx_root.

parameters: p_table type char10 obligatory,

             p_conn type dbcon_name obligatory.

try.

     dbconn = cl_sql_connection=>get_connection( p_conn ).

     results = dbconn->create_statement( )->execute_query( |SELECT * FROM { p_table }| ).

     cl_demo_output=>display( results->get_metadata( ) ).

     dbconn->close( ).

   catch cx_root into error.

     message error->get_text( ) type 'S'.

     if dbconn is not initial.

       dbconn->close( ).

     endif.

endtry.

Thanks,

Juwin

[Former Member]

Works great, many thanks!

[OsamaRaghib]

Thank you so much! it worked.