Why I got this error in execution of this script (plsql oracle)

Question

I'm writing plsql script and I get an error in execution in the part when I use ROW_NUMBER() OVER (PARTITION BY... : this the line of my code that give me this error : inconsistent datatypes: expected - got CLOB

This is my code :

CREATE OR REPLACE VIEW "VW_APP_PU_LOCATION_CLUSTER" ("ID", "BATCH_ID", "PRODUCTION_SYSTEM_ID", "CODE", "URGENCY", "CURRENT_STATE", "VALUE", "ELEMENT_ID","VIEW_ID") AS 
  SELECT app.id AS id
    ,      app.batch_state AS batch_id
    ,      app.production_system_id AS production_system_id
    ,      atp.code AS code
    ,      app.urgency_code AS urgency
    ,      app.current_state AS current_state
    ,      dbms_lob.substr(el.value, 4000, 1) AS value
    ,      el.element_id AS element_id
    , ROW_NUMBER() OVER (PARTITION BY LOWER(el.value) ORDER BY loc.code) AS view_id    
    FROM   application app
    CROSS  JOIN application_type atp
    CROSS  JOIN location loc
    CROSS  JOIN application_order ord
    LEFT JOIN data_element el ON (app.id = el.application_id AND el.element_id = isexistsparam('Pu Generation','SORTING_DATA_ELEMENT'))
    WHERE  app.application_type_id = atp.id
    AND    app.location_id = loc.id
    AND    app.application_order_id = ord.id
    AND app.current_state in 
    (
      select distinct sd.state 
      from process_state_definition psd, 
      state_definition sd, 
      application_type appType, 
      application appli,
      process_definition pd
      Where 
      ((appType.code is null)or (appType.id = appli.application_type_id AND sd.id = psd.state_definition_id)
        AND psd.application_type_id = appType.id 
        AND psd.process_definition_id = pd.id 
        AND psd.state_type='START'
        AND pd.name = 'Pu Generation')
   )    
    ORDER  BY loc.code asc,
CASE WHEN isexistsparam('Pu Generation', 'SORTING_DATA_ELEMENT') != -1 
THEN dbms_lob.substr(el.value, 4000, 1) ELSE NULL
END
    ,      ord.date_ordered asc
    ,      app.application_number asc;

can someone explain to me what is wrong with ROW_NUMBER() function Thanks

Answer 1

There is restriction:

You cannot specify LOB columns in the ORDER BY clause of a query, or in the GROUP BY clause of a query or in an aggregate function.

You can transform CLOB into CHAR if value less 4000 characters.

*My explanation or how I understand: ROW_NUMBER is the analytic function. Analytic functions compute an aggregate value based on a group of rows. We have PARTITION BY that grouping data based on LOB column. So, returning to restriction:

You cannot specify LOB columns ... in the GROUP BY clause of a query ...

**Sorry for my English.