甲骨文CLOB PHP

Question

所以，当我尝试执行以下代码

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是一个CLOB，我想记住它，我得到这个错误，不是显示它。有什么想法为什么？ 如果我删除E表，代码工作正常。但现在我想要显示新闻中的段落，为此我需要TEXT字段。

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

不能使用DISTINCT 与 CLOB 数据类型，UNION意味着显着，所以你可以取代它UNION ALL。它将解决问题，但也许你需要重写查询（取决于你需要的数据）。

Answer 2

@Leo已经回答“为什么”。您有更多的选择比union all：

首先是使用dbms_lob.substr(e.text)而不是e.text在选择列表中。

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> 

二 - 改变你的union条款where与or结合：

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

二是不能完全等同于原始查询，它不如有删除重复，但请确认是否是此适当。

在这两种情况下，您应该重写from和where部件，使用joins。