Skip to Main Content
  • Questions

  • ORA-06550 : PLS-00907: Without any acutal error

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kingh.

Asked: September 04, 2015 - 9:32 am UTC

Last updated: October 09, 2015 - 11:51 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Dear Tom,

I am getting a unique problem very inconsistently, while running an oracle procedure through SQL server job failing with following error message.

Error string: ORA-06550: line 0, column 0: PLS-00907: cannot load library unit XYZ.SP_ABC

I have checked, the SP_ABC procedure, it was not in invalid state, even the underlying referenced procedures and functions were also good.

When I have again restarted the step (after 2-3 hrs. doing absolutely nothing) it ran successfully. It has happened 2-3 times recently, past 3 years this procedure was working fine. We have recently updated the oracle version to 11.2.0.4.0, details are below.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Please give your suggestion, thanks in advance!

and Connor said...

How is your "SQL Server job" executing the Oracle procedure ? Is it connecting via ODBC ? or heterogenous gaetway ? some other means ?

If you've only started noticing it since the upgrade, one area for investigation is ensuring that perhaps all the components (eg SQL net client, ODBC if applicable, etc) on the machine that is connecting to the database are also at the same version.

I've seen in the past, when a local procedure is marked for recompilation (ie, some ddl means the procedure will be auto-recompiled on next invocation), then remote calling clients get themselves into a bit of mess.

Hope this helps. Let us know how you go with via a review.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-06550: line 0, column 0: PLS-00907: cannot load library unit

rochak agrawal, October 08, 2015 - 12:09 pm UTC

Hi,

I am facing similar issue, was there any solution identified by you
Connor McDonald
October 09, 2015 - 12:52 am UTC

I know at a previous client when they experienced this, they would drop the connection and re-establish a new one, and that would solve the issue.

You could try that if its possible in your environment.

Hope this helps.

Rochak, October 09, 2015 - 9:32 am UTC

Thanks for your response, this is happening during a batch execution triggered by SSIS package from Windows server where we have oracle client 32 bit 11.2.0.1 , however we have database on Linux Oracle 11.2.0.4 64 bit.

We have observation that when this issue is happening , if we flush shared pool, next run of batch is successful, however it happens again randomly.

It started very frequently after we upgraded database to 11.2.0.4 from 11.2.0.3. but at the same time windows server was also recently upgraded from windows 2003 to windows 2008 and SQLServer 2000 to SQLServer 2008.

Issue was there earlier too but happened only 1-2 times in 3-5 years. Now it is almost every week.

problem is we are unable to pinpoint problem is with SQL Server side or Oracle side. just mismatch.

please suggest.
Connor McDonald
October 09, 2015 - 11:51 am UTC

I think you are going to have to log a Support for this one.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library