I was trying to view the source of an Oracle View in the "Source" tab of the view, and it was showing nothing and printing "java.lang.IllegalStateException: Reformat failed, normalized Strings differ.." followed by the text of the view in the error pane, but not showing the sql in the viewing pane.
With some experimentation in Oracle sql developer I determined that Squirrel doesn't like that the view SQL contains a blank line. Below is the text of the sql. It's the blank line just before the final SELECT that makes it choke. Note also I don't why it shows the first 2 lines of the sql as super-long lines, it doesn't show it that way in sql developer, and it doesn't show it that way in the error message that was shown when it had the blank line. This leads me to believe the squirrel sql parser has other problems with this sql. To my way of thinking, if squirrel can't cope with the sql, it should at least just show it as it came from the database.
CREATE OR REPLACE VIEW ASSESSMENT_PRIORITY_V AS with opar as ( select unit_id,listagg(opar,',') within group (order by opar) as opar from ( select distinct ci.unit_id,ibis.stock.itemOPARshort(ci.item_id) as opar from ibis.collection_item ci where ci.current_item = '*' ) group by unit_id ),
stock_details as ( select distinct cu.unit_id,sn.name,ctm.name_list,cu.field_number,op.opar,ltrim(ce.coll_day||' '||ce.coll_month||' '||ce.coll_year) as date_collected,cultivated,cu.notes,ce.locality,cu.create_date as record_created from collecting_teams ctm join collecting_event ce on (ctm.id = ce.ctm_id) join collection_unit cu on (ce.event_id = cu.event_id) and (upper(cu.cultivated) in ('W','O','C')) join determination det on (cu.unit_id = det.unit_id) join simple_name sn on (det.taxon_id = sn.taxon_id) and current_det = '*' left join opar op on (cu.unit_id = op.unit_id) where exists ( select 1 from collection_item ci join item_situation isit on (ci.item_id = isit.item_id) and isit.current_sit = '*' and isit.current_qty > 0 join situation sit on (isit.sit_id = sit.sit_id) and sit.institution_code = 'ANBG' where (cu.unit_id = ci.unit_id) and ci.current_item = '*' ) ),
most_recent_assessment as
(
select
af.unit_id,
max(af.assessment_for_id) as current_assessment_id
from ibis.assessment_for af
join ibis.assessment_type at on (af.ass_type_id = at.ass_type_id)
and at.type = 'Collection'
group by af.unit_id
)
select
distinct sd.unit_id,
case when af.result is null then 'unassessed' else af.result end as assessment_priority,
sd.name,
sd.name_list as collector,
sd.field_number,
af.notes as assessment_notes,
af.start_date as assessed_date,
sd.date_collected,
sd.cultivated,
sd.opar,
sd.locality,
sd.notes,
sd.record_created
from stock_details sd
left join most_recent_assessment mra on (sd.unit_id = mra.unit_id)
left join ibis.assessment_for af on
(
mra.current_assessment_id = af.assessment_for_id
)
Sorry, I can't reproduce your problem. I tried the following:
If you don't see the problem when you reformat the view in SQuirreL's SQL editor, either, a possible reason could be that the view code in the database contains unprintable characters which cause the problem.
Anyway I implemented your suggestion to show source code that fails to reformat as it came from the database. The change is committed to our GIT repository and will be available in future snapshots and versions.
Hi, when you say you don't see a problem when you reformat the view, do you see it actually reformatting it, or are the first 2 lines super long, as if it has ignored them? The above code is as it comes out of the the Squirrel formatter, but it doesn't look formatted to me.
That the two long lines aren't formatted is a limitation of SQuirreL's formatter.
I just committed the following feature (excerpt from change log):
"Option to use the Vertical-blank Sql-formatter (https://github.com/vertical-blank/sql-formatter) instead of SQuirreL's internal formatter.
To switch to the Vertical-blank formatter go to menu File --> Global Preference --> tab "SQL formatting".
This change was inspired by the discussion in bug #1455."
Maybe the Vertical-blank formatter will suit your needs more.
Hi, you say that the two long lines are a "limitation" of the SQuirrel formatter, but you don't say if that would have led to it considering it a "failure" in the reformatting when it came from the database, leading to the original issue.
Anyway, I could suggest you recreate tables with the original names and try the exact view, but short of that it sounds like the changes you've done might fix the main problem of being unable to see the view code.
As mentioned above I am not able to reproduce the failure (exception/error message).
The limitation was that the long lines didn't get formatted. This limitation is resolved by the option of using the Vertical-blank Sql-formatter.