CODEWITHSUNDEEP
javaoracle-databasejdbcdatabase-metadata
vinsse2001
vinsse2001

Reputation: 47

How to get the structure of SYS_NC columns (in an Oracle database) using a jdbc driver?

I've developed an application to analyze differences between 2 database tables (e.g., when migrating from DB2 to Oracle) - through JDBC connections - and I have a test case that I don't know how to solve:

some columns (in the Oracle database) are indexed in EBCDIC, via SYS_NC functions (e.g. SYS_NC00037$=NLSSORT("COL3",'nls_sort=''EBCDIC'''')), so when I search for the table's unique indexes, I get e.g. COL1, COL2, SYS_NC00037$, instead of COL1, COL2, COL3 (which I need, to be able to run the data comparison).

I can't see how to retrieve the information that SYS_NC00037 is associated with COL3, via the JDBC driver?

Looking at the data returned by DbVisualizer or DBeaver (which connect via JDBC), I can only see information about SYS_NCs in the DDL generation (which I don't know how to retrieve either, although it seems possible via JDBC). Thanks in advance for your answers!

Upvotes: 2

Views: 852

Answers (1)

Paul W
Paul W

Reputation: 11274

Whenever a function-based index is created, the definition of any expression columns is stored in dba_ind_expressions, keyed by column position (within the index). So:

SELECT ic.column_position,
       ic.column_name,
       ie.column_expression
  FROM dba_ind_columns ic,
       dba_ind_expressions ie
 WHERE ic.index_owner = ie.index_owner
   AND ic.index_name = ie.index_name
   AND ic.column_position = ie.column_position
   AND ic.column_name = 'SYS_NC00037$'

Just be aware that column_expression is a long which means you may need some PL/SQL to convert that to a varchar2(4000) in order to work with it in SQL downstream.

Upvotes: 1

Related Questions