ezoracle extension - Error (932): ORA-00932: inconsistent datatypes: expected - got CLOB

Friday 03 September 2010 12:44:41 am

Hi Javi,

From what I read on http://projects.ez.no/ezoracle, you have to use ezoracle 1.8.2 with eZ Publish 4.0.x. Version 2.0.3 is for eZ Publish 4.1, 4.2 and 4.3.

Cheers

Damien
Planet eZ Publish.fr : http://www.planet-ezpublish.fr
Certification : http://auth.ez.no/certification/verify/372448
Publications about eZ Publish : http://pwet.fr/tags/keywords/weblog/ez_publish

Friday 03 September 2010 1:49:37 am

Hi Javi. Bugs/errors with oracle are best reported in the extension's own forums.

The error code you found is apparently for oracle application server. The exact definition of ora-00932 you can find at http://ora-00932.ora-code.com/.

I just ran the same query on my local install: eZP 4.2.0 + oracle 11.2. It works, and does even return a few rows.

I think that

1. either the code does not work on oracle 10 (but I'll have to install one before I can test)

2. or your db structures are a bit different from what they should be

In fact I suspect that point 2 is in effect: looking at table definitions in my schema, there's no trace of LOBs in either ezcontentclass, ezcontentclass_classgroup or ezcontentclass_name.

Could you:

1. try to reach the setup/upgrade check page and see if the db validation page gives you any hints

2. run a DESC statement on those 3 tables and paste here the results?

Principal Consultant International Business
Member of the Community Project Board

Friday 03 September 2010 2:57:46 am

Hi Gaetano,

Thanks for your reply. I send you more information.

1. Try to reach the setup/upgrade check page and see if the db validation page gives you any hints:

The database is not consistent with the distribution database.

To synchronize your database with the distribution setup, run the following SQL commands:

DROP INDEX ezfind_elevate_configur00012_i;
CREATE INDEX ezfind_elevate_config_sq ON ezfind_elevate_configuration ( search_query );
DROP TABLE ezvideoflv;
DROP TABLE starrating;

2. Run a DESC statement on those 3 tables and paste here the results?

SQL> desc ezcontentclass;
Name Null? Type
----------------------------------------- -------- ----------------------------
ALWAYS_AVAILABLE NOT NULL NUMBER(38)
CONTENTOBJECT_NAME VARCHAR2(255)
CREATED NOT NULL NUMBER(38)
CREATOR_ID NOT NULL NUMBER(38)
ID NOT NULL NUMBER(38)
IDENTIFIER VARCHAR2(50)
INITIAL_LANGUAGE_ID NOT NULL NUMBER(38)
IS_CONTAINER NOT NULL NUMBER(38)
LANGUAGE_MASK NOT NULL NUMBER(38)
MODIFIED NOT NULL NUMBER(38)
MODIFIER_ID NOT NULL NUMBER(38)
REMOTE_ID VARCHAR2(100)
SERIALIZED_DESCRIPTION_LIST CLOB
SERIALIZED_NAME_LIST VARCHAR2(3100)
SORT_FIELD NOT NULL NUMBER(38)
SORT_ORDER NOT NULL NUMBER(38)
URL_ALIAS_NAME VARCHAR2(255)
VERSION NOT NULL NUMBER(38)

SQL> desc ezcontentclass_classgroup;
Name Null? Type
----------------------------------------- -------- ----------------------------
CONTENTCLASS_ID NOT NULL NUMBER(38)
CONTENTCLASS_VERSION NOT NULL NUMBER(38)
GROUP_ID NOT NULL NUMBER(38)
GROUP_NAME VARCHAR2(255)

SQL> desc ezcontentclass_name;
Name Null? Type
----------------------------------------- -------- ----------------------------
CONTENTCLASS_ID NOT NULL NUMBER(38)
CONTENTCLASS_VERSION NOT NULL NUMBER(38)
LANGUAGE_ID NOT NULL NUMBER(38)
LANGUAGE_LOCALE NOT NULL VARCHAR2(20)
NAME NOT NULL VARCHAR2(255)

Thanks!

Friday 03 September 2010 4:35:29 am

Btw:

- why are you using the starrating extension instead of the native ezstarrating one? ezstarrating is certified to support oracle

- the ezvideoflv extension from Damien Pobel might have trouble on Oracle - you have been warned... (you might of course either ask him or me for fixes, or even code them on your own and provide the patches)

Principal Consultant International Business
Member of the Community Project Board

Friday 03 September 2010 8:15:11 am

If you are using version 2.0.3, you should not have the behaviour you describe... (btw, the two extensions have nothing to do with your problems - I never said that)

I suggest you:

1. verify in extension/ezoracle/settings/dbschema.ini.append.php if you have a line mentioning ezcontentclass.serialized_description_list

2. download again the extension and check if it corresponds to what you have installed

Principal Consultant International Business
Member of the Community Project Board

Friday 17 September 2010 5:00:09 am

I had the same issue today. ENV: latest ez 4.3 + ezoracle 2.0.3

The line

ColumnTypeTranslation[ezcontentclass.serialized_description_list]=longtext;varchar(3000)

is present in extension/ezoracle/settings/dbschema.ini.append.php, but table is created as clob-type.

I had to modify mysql2oracle-schema.php unter extension/ezoracle/scripts and add a new array key to the $columnTypeTransTable (Line 41):

$columnTypeTransTable = array(
    'ezurlalias.source_url'      => 'VARCHAR2(3000)',
    'ezurl.url'      => 'VARCHAR2(3000)',
    'ezurlalias_ml.action' => 'VARCHAR2(3000)',
    'ezurlalias_ml.text' => 'VARCHAR2(3000)',
    'ezurlalias.destination_url' => 'VARCHAR2(3000)',
    'ezcontentobject_tree.path_identification_string' => 'VARCHAR2(3100)',
    'ezcontentobject_trash.path_identification_string' => 'VARCHAR2(3100)',
    'ezimagefile.filepath' => 'VARCHAR2(3000)',
    'eznotificationcollection.data_subject' => 'VARCHAR2(3100)',
    'ezrss_import.url' => 'VARCHAR2(3100)',
    'ezrss_import.import_description' => 'VARCHAR2(3100)',
    'ezcontentclass.serialized_name_list' => 'VARCHAR2(3100)',
    'ezcontentclass.serialized_description_list' => 'VARCHAR2(3000)',
    'ezcontentclass_attribute.serialized_name_list' => 'VARCHAR2(3100)',
    'ezpending_actions.param' => 'VARCHAR2(3000)'
    );

Only the line

'ezcontentclass.serialized_description_list' => 'VARCHAR2(3000)',

was added.. after that you can run all scripts ( with --drop) again and it works..

Best regards,

Patrick

Monday 18 October 2010 8:11:21 am

It works!

Thank you,

Javi