Convert the SQL Query in Shell Script

Shell Programming and Scripting
1

Hi All,

I have a query with output below

select 'create synonym "'||TABLE_NAME||'" for '||Table_owner||'."'||table_name||'"'||chr(59) from user_synonyms;

==================

create synonym "RV_SBC_SIG" for WFCONTROLLER_TE."RV_SBC_SIG";
create synonym "AQM_TASK" for AWQM_TE."AQM_TASK";
create synonym "INTERFACE_MSG" for PRISM_TE."INTERFACE_MSG";

I want to convert that _TE to _TH in sql query itself. Can we do it using sed or any other way to do that?. Please suggest.

Thanks,
Mani

2 
sed 's#_TE#_TH#g' file.sql > tmp; mv tmp file.sql
1 Like
3

You can make use of REPLACE function to convert _TE to _TH in the SQL query itself.

select 'create synonym "'||TABLE_NAME||'" for '||replace(Table_owner,'_TE','_TH')||'."'||table_name||'"'||chr(59) from user_synonyms;