CLOB ORACLE (LARAVEL TEXT DATATYPE) FAILS AT ORDER (ORA-00932: inconsistent datatypes: expected - got CLOB) and query join searchs fails.

nirvale commented 9 months ago

Summary of problem or feature request

Problem

-Build a datatable with oracle... -The data filed DESCRIPTION, is a text datatype in the migration, but into oracle database gets CLOB datatype. -The query gets a model select and join to catalog table -The initial draw its ok, all data on screen Problem 1 The ordering in the CLOB fails with ORA-00932, need to be added the dbms_lob.substr() in the final query... but, i don't know how to get add the function, maybe... RAW sql... but how can i inser the function dmbs_lob? any way... At today i did disabled the ordering in that column... Is not really efficient, but almost first 100 characters would be util...

Problem 2

The query is a join... the search fails... obviously... searchs with model, and cant find the column because not exists..

Code snippet of problem

''''''SQL ABOUT ORDERING CLOB DATATYPE, NEEDS DBMS_LOB.SUBSTR() FUNCTION. --sql


select t2. from
(  select
rownum AS "rn", t1.
from
(
select
"TABLE1"."ID", "TABLE1"."NAME", "TABLE1"."CLOB","TABLE2"."DESCRIPTION"
from
"TABLE1"
inner join
"TABLE2"
on
"TABLE2"."ID" = "TABLE1"."ID_TABLE2"
order by
dbms_lob.substr("DESCRIPTION",100) asc
) t1
where

rownum <= 10) t2
where
t2."rn" >= 1;
AND THE SEARCH ERROR IN A JOIN QUERY...
DataTables warning: table id=DATATABLE-ID - Exception Message:
Error Code    : 904
Error Message : ORA-00904: "TABLE1"."DESCRIPTION": invalid identifier
THE QUERY
return  $model = Datacenter::select('table1.id','table2.name','table1.clob','table2.description',)
->join('table2','table2.id','=','table1.id_table2');

System details

-OPENSUSE OS -PHP 8.2 -LARAVEL 10

Laravel-Datatables Version 10

yajra commented 8 months ago

As the error stated, you cannot order on clob datatype. However, you can use orderColumn and add a custom sql for it to work. Maybe something like:

->orderColumn('description', "DBMS_LOB.SUBSTR(description) $1")

nirvale commented 8 months ago

->orderColumn('description', "DBMS_LOB.SUBSTR(description) $1")
wow, I did read it into documentation, and... I didn't it, my mistake, I'm sorry... orderColumn it works, ayway... order by text column, and search into a text column into a datatable maybe is a not very efficient proccess with a larger tables to try in web, I will not do it any more but is good to know how doit.

Thank you. ``

yajra / laravel-datatables