This forum is currently locked. You can't register or post questions at this time. (read more)

ORA-06512 - preceding error message

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

rcb
Member
Posts: 9
Joined: Tue Sep 11, 2012 9:52 am

ORA-06512 - preceding error message

Postby rcb » Tue Sep 11, 2012 10:11 am

Hi Tim,

we are using Oracle 10.2.0.5 on Solaris. Our business logic is coded in some PLSQL packages.

An error occures in this function (inside a package):

Code: Select all

CREATE OR REPLACE PACKAGE BODY Y AS
...
    FUNCTION some_fct
    (
        nKnz     NUMBER
    )
    RETURN NUMBER
    IS
       nID NUMBER;
 
    BEGIN
         SELECT O.ID INTO nID
         FROM tbl_o O JOIN tbl_p P ON O.ID = P.ID
         WHERE O.KNZ = nKnz;
 
         RETURN nID;
 
    EXCEPTION
       WHEN NO_DATA_FOUND THEN RETURN -1;
 
    END some_fct;
...
END Y;


The error points to the select statement:

Code: Select all

ORA-06512: at "X.Y", line 137


I would expect a preceding error (like ORA-01722) to have more detail. But there was nothing more.

At first I added an additional exception handler:

Code: Select all

WHEN OTHERS THEN
   dbms_output.put_line( 'Error Stack...' );
   dbms_output.put_line( dbms_utility.format_error_stack );
   dbms_output.put_line( 'Error Backtrace...' );
   dbms_output.put_line( dbms_utility.format_error_backtrace );
   raise;


The result was:

Code: Select all

Error_Stack...
 
Error_Backtrace...
ORA-06512: at line 1
ORA-06512: at "X.Y", line 137


The SQL statement alone works fine.

What can I do to get the preceding (real) errors?

Many Thanks.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-06512 - preceding error message

Postby Tim... » Tue Sep 11, 2012 10:57 am

Hi.

The short answer to this is I don't know.

I guess the thing I would do next is check the alter log on the server to see if any system level errors (600 or 7445) errors are being generated a result of this. Maybe a bigger issue like this is masking something...

Have you tried stripping this out into a separate package (or stand alone function and running it on its own. I would be interested to see if it is reproducible in its smallest form.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

rcb
Member
Posts: 9
Joined: Tue Sep 11, 2012 9:52 am

Re: ORA-06512 - preceding error message

Postby rcb » Tue Sep 11, 2012 2:30 pm

Hi Tim,

it's too bad that there is only a short answer. :(

I tested the following:
  • run the affected SQL statement stand alone --> success
  • run the affected function inside SQL (select some_fct from dual;) --> success
  • run the function as an anonymous block --> success
  • move the content of some_fct to the calling function --> ORA-6512 points to the SQL statement
  • there is no line in the alert log with respect to this error.
  • I cloned the database and run the same function --> success

Once or twice I got ORA-1423 without any preceding error. I could not reproduce it, either.

Some weeks ago we had a total server crash, which caused corrupted datafiles. Luckily the database could be recovered without any loss of data. dbverify did not show any corruption after it.

Any ideas, workarounds would be appreciated.

Many Thanks.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-06512 - preceding error message

Postby Tim... » Tue Sep 11, 2012 5:42 pm

Hi.

When you moved it to a calling function it failed. Was the calling function new one, or and existing one you amended?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

rcb
Member
Posts: 9
Joined: Tue Sep 11, 2012 9:52 am

Re: ORA-06512 - preceding error message

Postby rcb » Wed Sep 12, 2012 5:59 am

Hi Tim,

all needed PLSQL packages were cloned for the tests. The result was the same: ORA 6512.

Inside the test packages I moved the content of some_fct to an existing function.

Kind regards,
Ralph

rcb
Member
Posts: 9
Joined: Tue Sep 11, 2012 9:52 am

Re: ORA-06512 - preceding error message

Postby rcb » Wed Sep 12, 2012 6:53 am

Hi Tim,

I cloned the tables used in the SQL statement. It seems to be the way out. All works fine again.

Now I'm wondering what is the difference between these two sets of tables (beside the data blocks)? At a glance both sets should work and they really do when the SQL statement runs stand alone.

I would like if you could provide some ideas to dig deeper.

Many Thanks,
Ralph

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-06512 - preceding error message

Postby Tim... » Wed Sep 12, 2012 8:06 am

Hi.

I'm not really sure how to dig deeper, other than to raise an SR with Oracle support.

If you said the query always failed against one table, but worked against another I would be thinking block corruption and advise this:

http://www.oracle-base.com/articles/mis ... uption.php

The problem is, you can access the original table fine from SQL and an anonymous block, but not from a stored procedure/function/package. That muddies the water somewhat. That makes me think Oracle support may be the sensible next step.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

rcb
Member
Posts: 9
Joined: Tue Sep 11, 2012 9:52 am

Re: ORA-06512 - preceding error message

Postby rcb » Thu Sep 13, 2012 7:17 am

Hi Tim,

I will check for block corruption using your article and if needed raise an SR.

Thank you for your support.

Ralph

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-06512 - preceding error message

Postby Tim... » Thu Sep 13, 2012 7:20 am

OK. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests

cron