Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure ORA-06550 error in Oracle XE
icon5.gif  Stored Procedure ORA-06550 error in Oracle XE [message #197155] Tue, 10 October 2006 05:15 Go to next message
Binary9
Messages: 5
Registered: October 2006
Junior Member
Hi,

I am having problems with Oracle XE and stored procedures from within C#.

Stored procedure is like this:
create or replace PROCEDURE CreateSampleResultsAndLimits 
( varSampleCode IN VARCHAR2 )
AS
BEGIN
<procedure code removed>
END CreateSampleResultsAndLimits;

The procedure is created by the using .net1.1's System.Data.OracleClient's objects. The pl/sql code is 'run' by using the ExecuteNonQuery method.

However the procedure cannot be used - The following exception reported when trying to execute it:
OracleException
System.Data.OracleClient : ORA-06550: line 1, column 7:
PLS-00905: object MATTESTV4.CREATESAMPLERESULTSANDLIMITS is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

However, if I go into the web admin of XE->Object Browser->Procedures, select the procedure and press compile I can then use it with no problems.

So my question (finally!); is there a way to automatically 'compile' the procedure so it can be used?
In addition, the solution ideally needs to work with Oracle 8+.

Any help gratefully received!

2c
Re: Stored Procedure ORA-06550 error in Oracle XE [message #197168 is a reply to message #197155] Tue, 10 October 2006 05:54 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what Oracle says about it:
Oracle

PLS-00905 object string is invalid

Cause: An invalid package specification or stored subprogram was referenced. A package specification or stored subprogram is invalid if its source code or any database object it references has been DROPped, REPLACEd, or ALTERed since it was last compiled.

Action: Find out what invalidated the package specification or stored subprogram, then make sure that Oracle can recompile it without errors.
Re: Stored Procedure ORA-06550 error in Oracle XE [message #197194 is a reply to message #197168] Tue, 10 October 2006 08:09 Go to previous messageGo to next message
Binary9
Messages: 5
Registered: October 2006
Junior Member
Thanks for the quick reply!

So how do you persuade Oracle to re-compile the procedure after its been created? The procedure is ok apart from not being compiled when created, once its manually compiled it works fine.

What I'm trying to avoid is the customer having to get their DBA's to compile the procedures as the rest of the application installation is practically automatic. Is there some SQL that can be run that will compile all procedures within an instance?

Thanks in advance

2c


Re: Stored Procedure ORA-06550 error in Oracle XE [message #197198 is a reply to message #197194] Tue, 10 October 2006 08:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you call an invalid procedure from Forms, Reports, Java or Pl/Sql, (or C if called via Extproc in my experiance) it will recompile automatically.

The question is, what is C# not doing that everything else does.
Which ODBC drivers are you using?
Re: Stored Procedure ORA-06550 error in Oracle XE [message #197200 is a reply to message #197155] Tue, 10 October 2006 08:18 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Possibly some of the packages have become invalidated because dependent packages have been recompiled. Try running dbms_utility.compile_schema to recompile everything and check if anything is invalid by doing a "select * from all_objects where status = 'INVALID'".

[Updated on: Tue, 10 October 2006 08:21]

Report message to a moderator

Re: Stored Procedure ORA-06550 error in Oracle XE [message #197392 is a reply to message #197200] Wed, 11 October 2006 02:18 Go to previous messageGo to next message
Binary9
Messages: 5
Registered: October 2006
Junior Member
Hi,

Thanks for the replies guys!

JRowbottom:
I'm not using ODBC, using OLEDB which is basically a wrapper on ODBC for ADO.NET.

Cthulhu:
From your reply I managed to find what I _thought_ would be the answer:
ALTER PROCEDURE CREATESAMPLERESULTSANDLIMITS
   COMPILE

However when I run this (within XE's web admin) I get this error:
ERROR at line 2: PLS-00103: Encountered the symbol "" when expecting one of the following:
   ; is with authid as cluster order using external
   deterministic parallel_enable pipelined
1. ALTER PROCEDURE CREATESAMPLERESULTSANDLIMITS
2.    COMPILE

If its run from within code and SQL Developer I get no errors/fails.

Again any help/advice greatly appreciated.


2c



Re: Stored Procedure ORA-06550 error in Oracle XE [message #197393 is a reply to message #197155] Wed, 11 October 2006 02:21 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I don't know that particular tool, but it probably requires a semi-colon to terminate the statement.
Re: Stored Procedure ORA-06550 error in Oracle XE [message #197396 is a reply to message #197393] Wed, 11 October 2006 02:31 Go to previous messageGo to next message
Binary9
Messages: 5
Registered: October 2006
Junior Member
Well spotted Embarassed
Unfortunatly makes no difference!

SQL Developer:
http://www.oracle.com/technology/products/database/sql_developer/index.html


2c
Re: Stored Procedure ORA-06550 error in Oracle XE [message #197400 is a reply to message #197155] Wed, 11 October 2006 02:45 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
That is a PL/SQL error but you have not issued a PL/SQL command. Maybe the XE admin tool is expecting PL/SQL commands. Try using

dbms_ddl.alter_compile(type varchar2, schema varchar2, name varchar2);
Re: Stored Procedure ORA-06550 error in Oracle XE [message #197696 is a reply to message #197400] Thu, 12 October 2006 06:20 Go to previous messageGo to next message
Binary9
Messages: 5
Registered: October 2006
Junior Member
Thanks for all the help Cthulhu.

Just tried what you suggested:
DBMS_DDL.ALTER_COMPILE ('PROCEDURE', NULL, 'CREATESAMPLERESULTSANDLIMITS');

But get:
ORA-00900: invalid SQL statement

Also tried it with the User ID instead of NULL and with exec before the statement, all combinations produce the same error.

Does XE support DBMS_DDL package?
Any other ideas to try or research?

About to set up a 9i test to see if it works on a full version of Oracle ...

Thanks for all the help so far!


2c


Re: Stored Procedure ORA-06550 error in Oracle XE [message #197700 is a reply to message #197155] Thu, 12 October 2006 06:29 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
This is a PL/SQL call so, if you were using a normal tool, you would do:

begin
DBMS_DDL.ALTER_COMPILE ('PROCEDURE', NULL, 'CREATESAMPLERESULTSANDLIMITS');
end;

As to what your XE Admin tool wants or expects, I have no idea.
Previous Topic: Procedure: insert else delete (merged)
Next Topic: sql "where" conditions (array)
Goto Forum:
  


Current Time: Sun Dec 04 23:06:19 CST 2016

Total time taken to generate the page: 0.13221 seconds