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.
After posting on another board, I was pointed to the sql:
ALTER PROCEDURE CREATESAMPLERESULTSANDLIMITS COMPILE
However this gives me the following error when run within the XE admin and within Oracle SQL Developer:
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;
Bizzarely I don't get an error when I execute the statement from code , but it makes no difference - the procedure is not compiled and cannot be used without throwing the original exception.
So any ideas on how to persuade Oracle to compile the procedure would be gratefully received!
2c
.NET Development32
|