Clobs and Blobs being included in "Find" methods of generated stored procs (procedures.sql)

[GoogleCodeExporter]

What steps will reproduce the problem?
1.
 CREATE TABLE  "ClobTable" 
    (   "ID" RAW(16) NOT NULL ENABLE, 
    "VALUE" CLOB NOT NULL ENABLE, 
    "CREATED" TIMESTAMP (6) NOT NULL ENABLE, 
    "LASTMODIFIED" TIMESTAMP (6) NOT NULL ENABLE, 
    )
 /
2.  Create project for Oracle

What is the expected output? What do you see instead?
Clobs and blobs both (though I only show a clob) are included in the where 
clause of the stored proc.  Clobs and blobs can not be in a join.  Here is 
the Find proc for the above table that is generated in procedures.sql:

CREATE OR REPLACE PROCEDURE ProjectDAL.ClobTable_Find
    (
        p_SearchUsingOR IN NUMBER  ,
        p_Id IN RAW  ,  
        p_Value IN CLOB  ,
        p_Created IN TIMESTAMP(6)  ,
        p_Lastmodified IN TIMESTAMP(6)  ,
        cur_OUT IN OUT types.cursorType  
    )   
                IS
                BEGIN

                open cur_OUT for

                SELECT
                  ID
    , VALUE
    , CREATED
    , LASTMODIFIED
                FROM
                ProjectDAL.ClobTable_Find
                WHERE 
  VALUE = p_Value OR p_Value is null 
    AND CREATED = p_Created OR p_Created is null 
    AND LASTMODIFIED = p_Lastmodified OR p_Lastmodified is null 
 AND p_SearchUsingOR = 0                        

                UNION
                    SELECT
                      ID
    , VALUE
    , CREATED
    , LASTMODIFIED
                FROM
                ProjectDAL.ClobTable_Find
                WHERE 
  VALUE = p_Value AND p_Value is not null 
    OR CREATED = p_Created AND p_Created is not null 
    OR LASTMODIFIED = p_Lastmodified AND p_Lastmodified is not null 
 AND p_SearchUsingOR = 1;           
    END;

    /

What version of .netTiers and CodeSmith are you using?
.netTiers 2.3, CodeSmith 5.2

Please provide any additional information below.
When running procedures.sql with clobs and blobs you get one of the errors 
respectively:
 PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB 
 PL/SQL: ORA-00932: inconsistent datatypes: expected - got BLOB

I'm not sure what the solution should be here.  I haven't tested it but 
DBMS_LOB.COMPARE maybe some help here. As in:

dbms_lob.compare(VALUE, p_value) = 0 AND p_Value is not null

  Of couse it may not because of the clobs and blobs involved in the union.

Original issue reported on code.google.com by wordry...@gmail.com on 16 Apr 2010 at 4:10

[GoogleCodeExporter]

I haven't actually tried to run it yet but using 

dbms_lob.compare(VALUE, p_value) = 0 

gets rid of the errors.

Brian

Original comment by wordry...@gmail.com on 16 Apr 2010 at 4:50

[GoogleCodeExporter]

Hello,

We recently committed a bunch of oracle fixes in the latest nightly build. 
Could you try to reproduce this.

Thanks
-Blake Niemyjski

Original comment by bniemyjski on 21 Sep 2010 at 12:36

• Changed state: AwaitingInfo
• Added labels: Platform-Oracle
• Removed labels: Platform-SQL