asked on

Shell script to compare data in Oracle databae

Below is a sample of data in TABLE_FORMAT tables. I want to Compare the actual table names in SCOTT schema with the data in TABLE_FORMAT. Insert into TEST123 table the names of tables not meeting the naming convention in TABLE_FORMAT table. I need help please.

The first query return a roll because "SCR_00083_UB_ALL_FNL_2" fall within the naming format. The second query didn't return any row because "SCR_00083_UB_ALL_FNL_2t" doesn't falls within the naming format. I want to insert the name of tables that do not fall in the naming conversion.

select * from (
select UPPER(TABLE_FORMAT) table_format, upper(replace(replace(replace(replace(table_format,'${TB_STAMP}','%'),'${SCENARIO}','%'),'${END_DATE}','%'),'_','\_')) new_table_format from AR9_EIP_TABLES_TEMPLATE) a
2 3 where 'SCR_00083_UB_ALL_FNL_2' like new_table_format escape '\'
4 ;

TABLE_FORMAT NEW_TABLE_FORMAT
---------------------------------------- ----------------------------------------
SCR_${TB_STAMP}_UB_ALL_FNL_2 SCR\_%\_UB\_ALL\_FNL\_2

select * from (
2 select UPPER(TABLE_FORMAT) table_format, upper(replace(replace(replace(replace(table_format,'${TB_STAMP}','%'),'${SCENARIO}','%'),'${END_DATE}','%'),'_','\_')) new_table_format from AR9_EIP_TABLES_TEMPLATE) a
3 where 'SCR_00083_UB_ALL_FNL_2t' like new_table_format escape '\';

no rows selected

Sujith

What is not working?
What is that you are trying to achieve here?
Why are you using shell script to do it anyway?

From the looks of it, what you are trying to do can be done with a single SQL statement.

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

I have a bigger question: Why do this in a shell script at all?

I'm not exactly sure what you are doing but it seems that it can ALL be done in SQL. Likely in a single insert or merge statement.

Can you provide more about your requirements? Possible some sample data and expected results?

jko n127

ASKER

Here is a sample of data

ttt_${CASE}_${T_STAMP}_inv_lt_st
ttt_${CASE}_${T_STAMP}_ar_lt_st
ttt_${CASE}_${T_STAMP}_ar_lt_st_2
ttt_${CASE}_${T_STAMP}_ar_lt_st_3
ttt_${CASE}_${T_STAMP}_ar_inv_tot
ttt_${CASE}_${T_STAMP}_ar_inv_tot_2

ttt_${T_STAMP}_recon_vs_TTT_recon
ttt_${T_STAMP}_recon_vs_TTT_diff
ttt_${T_STAMP}_recon_vs_TTT_fnl_df
ttt_${END_DATE}_recon_vs_TTT_df

TTT_12B_INV_ISSUE_&RUN_MONTH
TTT_12C_INV_ISSUE_&RUN_MONTH
TTT_15A_AR_INV_BASE_&RUN_MONTH
TTT_15A_OMS_MISS_DP_&RUN_MONTH
TTT_4A_6A_TILL_&RUN_MONTH
TTT_4B_11A_TILL_&RUN_MONTH
ttt98968_11A_CHRG_2_REQ_&RUN_MONTH
ttt98968_11B_CHRG_2_REQ_&RUN_MONTH
ttt98968_6A_CHRG_2_REQ_&RUN_MONTH
ttt98968_6B_CHRG_2_REQ_&RUN_MONTH
ttt_27A_AR_INPUT_&RUN_MONTH

nolong_yyy_xxxx_${END_DATE}
nolong_yyy_xxxx_req_${END_DATE}
nolong_yyy_uuu_xxxx_${END_DATE}
nolong_inv_xxxx_rel_${END_DATE}
nolong_uuu_pyr_pop_${END_DATE}

slightwv (䄆 Netminder)

That sample data really doesn't tell me anything.

What are the table names you have in the schema?
What are the table names you want to compare against?
What is the desired output?

Sujith

Use this query to list down the tables not matching your patterns

select  table_name
from    user_tables ut1 
minus
select  table_name
from    user_tables ut
inner join AR9_EIP_TABLES_TEMPLATE t1
on ut.table_name like regexp_replace(UPPER(t1.TABLE_FORMAT), '(\$\{[^}]+})|(\&RUN\_MONTH)' , '%')
;

Open in new window

Once you get the query correct. You can put it into an SQL script and call it from shell script if you require. But first thing is to get the query correct.

jko n127

ASKER

Sujith, Thanks a lot for you help. It is working

jko n127

ASKER

Thanks again for your help

Sujith

Fantastic. Glad to know.

If you want to insert the results into a table; add an insert in the beginning -

insert into test123 (table_name)
select  table_name
from    user_tables ut1 
minus
select  table_name
from    user_tables ut
inner join AR9_EIP_TABLES_TEMPLATE t1
on ut.table_name like regexp_replace(UPPER(t1.TABLE_FORMAT), '(\$\{[^}]+})|(\&RUN\_MONTH)' , '%')
;

Open in new window