-2

I have been working on a meta table which is an integration of 3 tables (with same database structure but column names might be different) and I have to write a function which should check if the column has a function(to_Char()) on them. There are many columns as well. So the syntax has to be generalized to search all the columns that has to_char function is written or not.

Example: 

CONONICAL          DB1          DataType DB1  Function DB1      DB2
CUSTOMER_TABLE    customer_tb   Table         CUSTOMERS         Table
CUSTOMER_ID       Cust_id       number        to_char(cust_id)  cust_id number
CUSTOMER_NAME     Cust_name     varchar2(20)  cust_name         varchar2(20)
CUSTOMER_ADDRESS  Cust_address  varchar2(20)  cust_add          varchar2(20)
manikanth
  • 9
  • 1
  • Nobody is available here to solve your assignments. Please be more specific and tell us what you did so far and what exactly problem you are facing – Ghayel Nov 30 '15 at 20:01
  • 2
    "*if the column has a function on them*" does not make any sense to me. A column does not "have" a function unless you are talking about a computed column. –  Nov 30 '15 at 21:06
  • Are you talking about function based INDEXES on columns? Please be specific if you want qucik and quality response – Avrajit Roy Dec 01 '15 at 07:33
  • I have no idea what "*Function DB1*" is supposed to mean. A column does not "have" a function. It might be _used_ by one. –  Dec 01 '15 at 07:50

1 Answers1

0

If i am not wrong you are asking for all Function based INDEXES with their DDL so that you can identify what type of INDEX is applied on which column Hope the below code helps.

SELECT IDX.TABLE_OWNER,
  IDX.INDEX_NAME,
  IDX.INDEX_TYPE,
  IDX.TABLE_NAME,
  IND.COLUMN_NAME,
  dbms_metadata.get_ddl('INDEX',IDX.INDEX_NAME,IDX.TABLE_OWNER) ddl_script
FROM all_indexes IDX,
  ALL_IND_COLUMNS IND
WHERE OWNER         = <OWNER_NAME>
AND IND.INDEX_NAME  = IDX.INDEX_NAME
AND IND.TABLE_OWNER = IDX.TABLE_OWNER
AND IDX.INDEX_TYPE  = 'FUNCTION-BASED NORMAL';
Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25