Hi again

Using a query like this:


INSERT INTO OM_DB.CDRS_VALIDOS_TMP (
SELECT
FROM (
SELECT
FROM (
SELECT
FROM (
.....
)
)
FULL OUTER JOIN
(
SELECT ....
)
)
)

I receive this errors:

ORA-06550: line 0, column 0:
PLS-00801: internal error phdcsql_canonicalize_sql:state, Batch 1 Line
1 Col 1

Now, trust me, all the SELECT statement its ok, because i probe just
that part and its working, but when i try to insert that set into the
OM_DB.CDRS_VALIDOS_TMP table it doesnt work, i hear something about a
bug on this version, thats my only clue, any idea?

This are the oracle settings:

"Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi"
"PL/SQL Release 10.2.0.3.0 - Production"
"CORE 10.2.0.3.0 Production"
"TNS for Solaris: Version 10.2.0.3.0 - Production"
"NLSRTL Version 10.2.0.3.0 - Production"

Thanks in advanced.

Jorge Reyes wrote:

Does the tmp table have all of the correctly defined columns from the
resulting select...?

Are you using Warehouse Builder?

http://forums.oracle.com/forums/thre...hreadID=483857

but sounds like it could be bug related.

Jorge Reyes wrote:
The same statement worked on a different Oracle version, right?
Are there any comments in the SQL statement?
phdcsql_canonicalize_sql seems to be a function that strips comments
from SQL code to improve cursor sharing.

I searched the Oracle support databases, and found two possible
references:

Bug 5765958: this one is fixed in 10.2.0.4, and there is a one-off patch
for 10.2.0.3

Bug 6027121: there is no fix for this bug yet, but a workaround is to
set _optimizer_native_full_outer_join=force

You could try the latter, and if that does not work, upgrade to 10.2.0.4.

Yours,
Laurenz Albe