Jacob Alley Jacob Alley - 1 month ago 16
SQL Question

ORA-06550 when trying to run stored procedure in oracle c#

using (var command = new OracleCommand(sqlText, oraConnection) { CommandType = CommandType.StoredProcedure })
{
command.BindByName = true;
var parameters = command.Parameters;
parameters.Clear();
parameters.Add("p_date", OracleDbType.Date, ParameterDirection.Input).Value = DateTime.Now;
command.ExecuteNonQuery();
}


Here is my code in VS15. I am trying to execute a stored procedure and pass in my only parameter as a date.

create or replace procedure cwi_pat_deductions_export
(
:p_date date
)
is

v_count number;


This is the beginning of the stored proc. I am getting Oracle Error : ORA-06550 at line 1 col 7 (so right after the word "CREATE"). I am not a sql dev by trade, so I am struggling to see where the error is. I dont feel like it is on my end (the c# side) but it very well could be (our sql developer insists that it must be a problem on my end, as the stored proc compiles and runs fine in PlSql Developer).

are you not allowed to start stored procs with Create or replace? i thought that was standard.

Answer

You don't pass the full CREATE OR REPLACE PROCEDURE ... text of the procedure when you want to call it. The point of the procedure being stored is that this is already stored in the database.

If you want to call the stored procedure, the sqlText parameter should be just the stored procedure name, cwi_pat_deductions_export. If that doesn't work, and you get an invalid identifier error, try preceding the name with the owner of the stored procedure, for example some_schema.cwi_pat_deductions_export.