Hi all,
how to find which string is too long.. the below SQL return error : ORA-01489: result of string concatenation is too long.
SELECT count(profile_id) FROM (SELECT T.*, row_number() over (order by lower(profile_name) ASC ) rownumber
FROM (
SELECT
distinct ppg.profile_id as pid,
pd.profile_id as profile_id,
pd.profile_name as profile_name,
pd.last_modified as last_modified,
pd.type_code as type_code,
vuo.print_name as created_by,
vuo.id as created_by_id,
vuo.deleted as deleted,
(case
when pd.type_code = 'sd'
then
case
when pd.auto_install = 1 then 1
else 0
end
else 2
end) as auto_installable,
pd.auto_install as auto_install,
(case
when pd.type_code = 'sd'
then
case
when pd.auto_install = 1 then TO_CHAR(pd.auto_install_date)
else '--'
end
else 'NA'
end) as auto_install_date,
v_modify.id as user_id,
v_modify.print_name as modified_by,
v_modify.deleted as modify_deleted,
V_PROFILE_STATUS.noncompliant noncompliant,
(case
when V_PROFILE_STATUS.noncompliant is not null
then
case
when V_PROFILE_APP_FILE_COUNT.cnt > 0 then '2'
else '1'
end
else case
when V_PROFILE_APP_FILE_COUNT.cnt > 0 then '1'
else '--'
end
end) as profile_status,
V_PROFILE_APP_UPGRADE.upgrade upgrade,
vegb.bu_name as business_unit,
vegb.bu_id as bu_id,
listagg(vg.verifier_group_name,',') within group (order by ppg.verifier_group_id, vg.verifier_group_id ) as member_group,
listagg(ppg.verifier_group_id,',') within group (order by ppg.verifier_group_id ) as member_group_ids,
listagg(vegmc.member_equipment_name,',') within group ( order by vegmc.member_equipment_name) AS member_equipment_name,
vpfsl.assigned_files,
vpfsl.assigned_platform_applications,
decode(pd.winxp_path,null,'',pd.winxp_path) || decode(pd.winxpe_path,null,'', ','||pd.winxpe_path) || decode(pd.wince_path,null,'', ','||pd.wince_path) as destination_folder
FROM
profile_data pd,
V_PROFILE_APP_UPGRADE,
V_PROFILE_STATUS,
V_PROFILE_APP_FILE_COUNT,
V_USER_OPERATOR vuo,
V_USER_OPERATOR v_modify,
PROFILE_PLATFORM_GROUPS ppg,
V_EQUIPMENT_GROUP_BU vegb,
v_equipment_group_member_count vegmc,
verifier_group vg,
v_profile_file_software_list vpfsl
WHERE
V_PROFILE_APP_UPGRADE.profile_id = pd.profile_id
and V_PROFILE_STATUS.profile_id = pd.profile_id
and V_PROFILE_APP_FILE_COUNT.profile_id = pd.profile_id
and pd.CREATED_BY = vuo.ID(+)
and vuo.user_type not like '%operator%'
and pd.USER_ID = v_modify.ID(+)
and v_modify.user_type not like '%operator%'
and pd.profile_id = ppg.profile_id
and ppg.verifier_group_id = vegb.verifier_group_id
and ppg.verifier_group_id = vg.verifier_group_id
and ppg.verifier_group_id = vegmc.verifier_group_id
and pd.profile_id = vpfsl.profile_id
and vegb.bu_id IN ( 1000, 1025, 1026, 1075, 1100, 0)
group by
ppg.profile_id,
pd.profile_id ,
pd.profile_name ,
pd.last_modified,
pd.type_code ,
vuo.print_name,
vuo.id ,
vuo.deleted ,
pd.auto_install,
v_modify.id,
v_modify.print_name,
v_modify.deleted ,
V_PROFILE_STATUS.noncompliant ,
pd.auto_install_date,
V_PROFILE_APP_UPGRADE.upgrade ,
vegb.bu_name ,
vegb.bu_id,
V_PROFILE_APP_FILE_COUNT.cnt,
vpfsl.assigned_files,
vpfsl.assigned_platform_applications,
pd.winxp_path,
pd.winxpe_path,
pd.wince_path
) T);
Thx,