Re: PLS-00801: internal error [phdcsql_canonical_sql]
From: Laurenz Albe <invite_at_spam.to.invalid>
Date: Tue, 5 May 2009 09:45:29 +0200
Message-ID: <1241509553.362462_at_proxy.dienste.wien.at>
Jorge Reyes wrote:
> 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"
Date: Tue, 5 May 2009 09:45:29 +0200
Message-ID: <1241509553.362462_at_proxy.dienste.wien.at>
Jorge Reyes wrote:
> 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"
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
Received on Tue May 05 2009 - 02:45:29 CDT