Oracle clob php

Question

So i get this error when i try to execute the following code

ociexecute(): ORA-00932: inconsistent datatypes: expected - got CLOB 


$where_clause = "A.sent_newsletter='N' AND
                     A.validated='Y' AND
                     (TRUNC(A.date_validity_to) >= TRUNC(SYSDATE) OR A.date_validity_to IS NULL) AND
                     (TRUNC(A.date_validity_from) <= TRUNC(SYSDATE) OR A.date_validity_from IS NULL) AND
                     A.id_category=21 AND
                     A.id_category=B.id_category AND
                       B.id_type=C.id_type AND
                       C.id_subscription=".OCIResult($stmt_users, "ID_SUBSCRIPTION")." AND
                       C.id_type=D.id_type AND
                     E.id_news=A.id_news AND";

    $stmt_news = OCIParse($conn, "SELECT *
                                  FROM (
                                    SELECT A.id_news,
                                           A.id_category,
                                           A.lang,
                                           A.title,
                                           A.subtitle,
                                           TO_CHAR(A.date_creation, 'DD/MM/YYYY') AS DATE_CREATION,
                                           D.description,
                                           E.text
                                    FROM ".$db_schema_name."news_header A,
                                           ".$db_schema_name."newsletter_typecat_profile B,
                                           ".$db_schema_name."newsletter_subtyp_profile C,
                                           ".$db_schema_name."newsletter_type D,
                                         ".$db_schema_name."news_paragraph E
                                    WHERE ".$where_clause."
                                          A.lang='".OCIResult($stmt_users, "LANG")."'
                                    UNION
                                    SELECT A.id_news,
                                           A.id_category,
                                           A.lang,
                                           A.title,
                                           A.subtitle,
                                           TO_CHAR(A.date_creation, 'DD/MM/YYYY') AS DATE_CREATION,
                                           D.description,
                                           E.text
                                    FROM ".$db_schema_name."news_header A,
                                           ".$db_schema_name."newsletter_typecat_profile B,
                                           ".$db_schema_name."newsletter_subtyp_profile C,
                                           ".$db_schema_name."newsletter_type D,
                                         ".$db_schema_name."news_paragraph E
                                    WHERE ".$where_clause."
                                          A.lang='".$default_language."' AND
                                          A.id_news NOT IN (SELECT A.id_news
                                                            FROM ".$db_schema_name."news_header A,
                                                                   ".$db_schema_name."newsletter_typecat_profile B,
                                                                   ".$db_schema_name."newsletter_subtyp_profile C,
                                                                   ".$db_schema_name."newsletter_type D,
                                                                 ".$db_schema_name."news_paragraph E
                                                            WHERE ".$where_clause."
                                                                  A.lang='".OCIResult($stmt_users, "LANG")."')
                                  ) ORDER BY id_category, id_news DESC");

E.text is a clob and i want to memorize it and than display it. Got any ideas why ? The code is working perfectly if i remove the E table. But now i want to display also the paragraphs from the news and for that i need the TEXT field.

while(OCIFetch($stmt_news) && (OCIFetchInto($stmt_news, $fields,OCI_ASSOC))) {
  $paragraph_text = $fields['TEXT']->load();
  $id_newsletter_collection[$id_newsletter_collection_index++] = OCIResult($stmt_news, "ID_NEWS");
  $newsletter_item[$newsletter_item_index++] = New newsletter_item(OCIResult($stmt_news, "ID_NEWS"),
                                                                   OCIResult($stmt_news, "LANG"),
                                                                   OCIResult($stmt_news, "TITLE"),
                                                                   OCIResult($stmt_news, "SUBTITLE"),
                                                                   OCIResult($stmt_news, "DATE_CREATION"),
                                                                   $paragraph_text,


                                            $sentence_local[OCIResult($stmt_news, "DESCRIPTION")][OCIResult($stmt_users, "LANG")]);
} /* end while */

Answer 1

@Leo already answered "WHY". You have more options than union all:

First is to use dbms_lob.substr(e.text) instead of e.text in select list.

select a.id_news, dbms_lob.substr(e.text)
  from a join e on e.id_news = a.id_news
  where <condition1>
union 
select a.id_news, dbms_lob.substr(e.text)
  from a join e on e.id_news = a.id_news
  where <condition2>

Second - change union combining your where clauses with or:

select a.id_news, e.text
  from a 
  join e on e.id_news = a.id_news
  where <condition1>
     or <condition2>

Second is not fully equivalent to your original query, it doesn't remove duplicates if any, but please check if this is suitable.

In both cases you should rewrite from and where parts, use joins.

Answer 2

You can't use DISTINCT with clob datatypes, UNION implies Distinct, so you can replace it for UNION ALL. It will solve the problem, but maybe you need to rewrite the query (depends on data you need).