Oracle Error ORA-32575 when editing data via an Oracle view

[Dave]

IN A NUTSHELL
MapInfo version 15.2.4 uses different INSERT syntax to MapInfo 8.5 when editing data in an Oracle view - and the syntax causes an Oracle error every time.

Is there a configuration option or other workaround to prevent MapInfo 15.2.4 from using this new syntax?  (The old syntax worked perfectly well and allowed Oracle views to be edited.)

Or am I missing something altogether?


DETAILS
Using MapInfo version 8.5, entering new records into a linked TAB file based on an Oracle view works as expected - as it has done for many years.  New records are created and available upon save and refresh.  Empty attribute fields are ignored, as expected.

Using MapInfo version 15.2.4, editing the same linked TAB file, based on the same Oracle view, fails whenever any attribute fields are left empty.  The error message is as follows:

"Oracle Error: ORA-32575: Explicit column default is not supported for modifying views. Operation cancelled."



Looking at an Oracle trace file of the transaction, the INSERT statement syntax used by version 8.5 uses NULL for all attribute columns which are created empty.

In version 15.2.4, the INSERT statement uses the DEFAULT keyword for all attribute columns which are created empty.  This raises the Oracle error because this syntax is not supported when editing Oracle views - as distinct from Oracle tables.

Version 8.5 Syntax
INSERT INTO "GISDB"."MIV_ROADS_PROPOSED" ("NAME", "OWNER", "CONSTRUCTION",
  "CONSTRUCTION_DATE", "COMMENTS", "CLASS", "CONDITION", "WIDTH_METRES",
  "USAGE", "ROUTE_PRIORITY", "CLEANUP_RESPONSIBILITY",
  "MAINTENANCE_RESPONSIBILITY", "REHABILITATION_DATE", "OBJECT_TYPE",
  "SOURCE", "ACCURACY", "CREATED_BY", "CREATED_DATE", "UPDATED_BY",
  "UPDATED_DATE", "MI_PRINX", "OPERATOR", MI_STYLE, "GEOLOC")
VALUES
 ('James Street', 'Test', 'Gravel', TO_DATE('2012-11-06', 'YYYY-MM-DD'),
  'THis is an example', NULL, NULL, 12, 'Unknown', 0, NULL, 'Test', NULL,
  'L_PROAD', 'Unknown operator using Portable at: 06-Nov-2012 12:24:39',
  'High', 'GISDB', TO_DATE('2012-11-06', 'YYYY-MM-DD'), 'GISDB',
  TO_DATE('2014-03-19', 'YYYY-MM-DD'), 13550, NULL, 'Pen (17, 5, 16711935)',
  :1)

Version 15.2.4 Syntax
INSERT INTO "GISDB"."MIV_ROADS_PROPOSED" ("NAME", "OWNER", "CONSTRUCTION",
 "CONSTRUCTION_DATE", "COMMENTS", "CLASS", "CONDITION", "WIDTH_METRES",
 "USAGE", "ROUTE_PRIORITY", "CLEANUP_RESPONSIBILITY",
 "MAINTENANCE_RESPONSIBILITY", "REHABILITATION_DATE", "OBJECT_TYPE",
 "SOURCE", "ACCURACY", "CREATED_BY", "CREATED_DATE", "UPDATED_BY",
 "UPDATED_DATE", "MI_PRINX", "OPERATOR", "MI_STYLE", "GEOLOC")
VALUES
('Dave Street', 'Test', 'Gravel', TO_DATE('2012-11-06', 'YYYY-MM-DD'),
 'THis is an example', DEFAULT, DEFAULT, 12, 'Unknown', 0, DEFAULT, 'Test', DEFAULT,
 'L_PROAD', 'Unknown operator using Unknown platform at: 08-Nov-2012 15:49:49; QA by GISDB',
 'High', 'GISDB', TO_DATE('2012-11-08', 'YYYY-MM-DD'), 'GISDB',
 TO_DATE('2014-03-19', 'YYYY-MM-DD'), 13553, DEFAULT, 'Pen (17, 5, 16711935)',
 :1)

Error encountered: ORA-32575


I look forward to any insights, suggestions or advice.

Cheers!

[Simon]

Hi Dave,

Nice write-up. This appears to be a bug in the 64-bit version of MapInfo Pro. There are a few ways around it - use the 32-bit version, or alter the affected columns in the table to have default values (eg alter table GISDB.MIV_ROADS_PROPOSED modify (CLASS NUMBER(1) DEFAULT NULL); ).

I'll request getting this bug fixed for the 64-bit version.

Regards,

Simon

[Dave]

Thanks Simon,

For the moment the 32-bit option remains available, while the business plans its move to the 64-bit version.  But this won't be the case indefinitely.

Also, the problem relates to any inserts into an Oracle view (tables are not a problem.)  We use views extensively for managing spatial data in an Oracle database.  The tables underlying the views already have DEFAULT values specified in many cases.  But the problem is that the statement "INSERT INTO {view} ... VALUES (... DEFAULT, ...)" is illegal Oracle syntax.  I have no way of intercepting this or working around it in the database because the error is thrown on statement parsing.

Please do escalate this for bug fix in the 64-bit version of MapInfo as soon as possible.  It will be a show-stopper for us otherwise!

Cheers,
David

[Simon]

Hi Dave,

Thanks for the clarification. Apologies, I probably shouldn't have used your view name in the table example :)

Here's an example of a table/view combo I created in Oracle with default values specified, which works when I edit the view in MiPro:

drop table base_table;

create table base_table (

test_column1 number(1) default 0 not null,

test_column2 varchar2(30) default 'DEFAULT' not null,

mi_style varchar2(30) default null,

mi_prinx number(10) not null primary key,

geoloc mdsys.sdo_geometry not null

);

create or replace view view_of_base_table as select * from base_table;

insert into view_of_base_table values (1,'TEST',null,1,MDSYS.SDO_GEOMETRY(2001,8311,SDO_POINT_TYPE(153,-27.5,null),null,null));

commit;

Now create a spatial index on the base table geometry and add metadata into the mdsys.user_sdo_geom_metadata and mapinfo.mapinfo_mapcatalog tables (for both table and view). Once that is done the table should be editable without giving any errors :)

I also tried creating the view with less columns and it was also editable. If you have time, it would be interesting to see what results you get. I'm hoping it will help you develop a work-around until it's resolved in MiPro.

Regards,

Simon Smith

[Dave]

Thanks for the suggestion, Simon,

However... this still doesn't work in the MapInfo context.  

MapInfo 15.2.4 replaces any blank fields with the word DEFAULT when constructing the insert statement which is passed to the database.

Your example uses the word "null" and the insert statement is valid against a view:

insert into view_of_base_table values (1,'TEST',null,1,MDSYS.SDO_GEOMETRY(2001,8311,SDO_POINT_TYPE(153,-27.5,null),null,null));

However if you attempted the following statement - which is what MapInfo 15.2.4 creates if you edit the view in a browser and leave your "test_column2" field blank - you will encounter the error:

insert into view_of_base_table values (1,'TEST',DEFAULT,1,MDSYS.SDO_GEOMETRY(2001,8311,SDO_POINT_TYPE(153,-27.5,null),null,null));

Happily, all is not lost - I've since received advice that this issue was fixed in the 16.02 release of MapInfoPro.  I have yet to test it, but I suspect that the under-the-hood amendment will make MapInfo edits change back to using the word NULL instead of DEFAULT, which was the way things used to work.

Problem solved!  (... provided an upgrade to 16.02 is available to you)

Cheers,
Dave

[Simon]

Hi Dave,

Yes that is correct - DEFAULT was changed to NULL in the insert statement otherwise it gives an ORA-32575 error.

Regarding the advice you received, I received confirmation overnight that the issue is fixed in version 16.0.2. I'm using version 16.0.2 of MiPro, so that explains why I don't get the error when adding records to the view.

Regards,

Simon