9.2.0.5 Bug 3591119: Doing INSERT SELECT with UNION


When you use 9.2.0.5 database version and you’re inserting into a table from a select with an UNION clause you get a very nasty error. The connection blows and dumps a 7445 core dump error.

This is one of the many “features” of this dreadfull version: you should never use odd patchset numbers in Orace 9i! Stay away from 9.2.0.3, 5 and 7.

For the likes of you that ran on this error and want to patch it before upgrading the database where’s the Metalink note:

ORA-7445 [INSAPE] When Doing Insert With UNION on 9.2.0.5

Doc ID:
Note:272755.1
Type:
PROBLEM

Last Revision Date:
24-JUN-2005

<!– @page { margin: 2cm } H3 { margin-top: 0.49cm; margin-bottom: 0.49cm; page-break-after: auto } H3.western { font-family: “Times New Roman”, serif; font-size: 13pt } H3.cjk { font-family: “Lucida Sans Unicode”; font-size: 13pt } H3.ctl { font-family: “Tahoma”; font-size: 13pt } P { margin-bottom: 0.21cm } A:link { color: #0000ff } –>

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.5 to 9.2.0.5
This problem can occur on any platform.

Symptoms

Running Insert with UNION on 9.2.0.5 fails with ORA-3113 and generates a trace file.

This error occurs when inserted value is too large for the column.

From the trace file you can see the below ORA-7445 error:
ORA-07445: exception encountered: core dump [insape()+40] [SIGSEGV] [Address not mapped to object] [0x0] [] []
Current SQL statement for this session:
insert into test_length
select 1234 from dual union select 1234 from dual
Stack Trace
ksedmp ssexhd sigacthandler insape insLoadRow insfch insdrv insexe opiexe opiall0 kpoal8 opiodr ttcpip

The same SQL fails with the expected ORA-1401 in other versions.

Cause

The failure with a core dump instead of the expected ORA-1401 is Bug 3591119.

Solution

The correct error will be signalled if you apply a patch for 3591119 on 9.2.0.5, or apply 9.2.0.6 where it is resolved.

References

@ Bug 3591119 – Ora-7445 [Insape] From Insert With Union And Too Large Column Values

About these ads