You Asked
When we execute below sql from unixscript we get error .Kindly help on the issue
string beginning "CAPACITY_C..." is too long. maximum size is 239 characters.
SP2-0734: unknown command beginning "string beg..." - rest of line ignored.
@/exp_imp/metadata/db_report/sqlfile/comp_invalid_obj.sql CAPACITY_CORE,ICG_CA_CORE,COLLATERAL_BB_CORE,STGTEMP_AREA_CORE,CUSTOMER_CORE,CCB_FEEDS_CORE,REFERENCE_CORE,CR_CORE,CREDITANALYSIS_CORE,STGING_AREA_CORE,ENTL_CORE,TEMPBKP_CORE,SINGLE_FID_CORE_SRV,METADATA_CORE,SUPPORT_CORE,WSCCB_CORE,FACILITY_CORE,MARKET_DATA_CORE,WSCWF_CORE,COMMON_CORE;
comp_invalid_obj.sql :
select 'alter ' || decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' || owner||'.'|| object_name || ' compile ' || decode(object_type,'PACKAGE BODY',' body;',';')
from dba_objects where object_type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','VIEW')
and status = 'INVALID'
and owner in (decode('&1','ALL',owner,'&1'))
order by object_type , object_name;
and Chris said...
As it says in the docs, the maximum length of a substitution variable is 240 characters.
http://docs.oracle.com/database/121/SQPUG/apa.htm#SQPUG141 The string you're passing to just too long.
That said, what you're trying to do won't work anyway! The decode will convert the list into the single string of your object names. Not the list of individual names you need.
For example:
SQL>select * from dual
2 where 'a' in (decode('&1', 'ALL', 'a', '&1'));
Enter value for 1: a,b,c
Enter value for 1: a,b,c
old 2: where 'a' in (decode('&1', 'ALL', 'a', '&1'))
new 2: where 'a' in (decode('a,b,c', 'ALL', 'a', 'a,b,c'))
no rows selectedFor options to handle dynamic IN lists, see the following:
https://oracle-base.com/articles/misc/dynamic-in-lists