c# stored proc issue...

A

ace333

Member
Joined
Jul 19, 2005
Messages
22
CREATE OR REPLACE PACKAGE WES_UAT.MAILSERVICE
AS
PROCEDURE InsertEMail
(
i_SendTo IN EMAIL.SendTo%TYPE,
i_SendFrom IN EMAIL.SendFrom%TYPE,
i_Subject IN EMAIL.Subject%TYPE,
i_EMailBody IN EMAIL.EMailBody%TYPE,
i_BodyFormat IN EMAIL.BodyFormat%TYPE,
i_Priority IN EMAIL.Priority%TYPE,
i_LastUpdated IN EMAIL.LastUpdated%TYPE DEFAULT SYSDATE,
i_errorEmailAddress IN EMAIL.errorEmailAddress%TYPE,
i_ccEmailAddress IN EMAIL.ccEmailAddress%TYPE,
i_systemName IN EMAIL.systemName%TYPE,
i_inputDate IN EMAIL.inputDate%TYPE DEFAULT SYSDATE

);

PROCEDURE InsertEMail
(
i_SendTo IN EMAIL.SendTo%TYPE,
i_SendFrom IN EMAIL.SendFrom%TYPE,
i_Subject IN EMAIL.Subject%TYPE,
i_EMailBody IN EMAIL.EMailBody%TYPE,
i_BodyFormat IN EMAIL.BodyFormat%TYPE,
i_Priority IN EMAIL.Priority%TYPE,
i_LastUpdated IN EMAIL.LastUpdated%TYPE DEFAULT SYSDATE,
i_errorEmailAddress IN EMAIL.errorEmailAddress%TYPE,
i_ccEmailAddress IN EMAIL.ccEmailAddress%TYPE,
i_systemName IN EMAIL.systemName%TYPE,
i_inputDate IN EMAIL.inputDate%TYPE DEFAULT SYSDATE




)
IS
BEGIN

INSERT INTO
EMail
(SendTo,
SendFrom,
Subject,
EMailBody,
BodyFormat,
Priority,
LastUpdated,
errorEmailAddress,
ccEmailAddress,
systemName,
inputDate
)
VALUES
(i_SendTo,
i_SendFrom,
i_Subject,
i_EMailBody,
i_BodyFormat,
i_Priority,
i_LastUpdated,
i_errorEmailAddress,
i_ccEmailAddress,
i_systemName,
i_inputDate
);

COMMIT;

END INsertEMail;

The procedure is part of a package...

This is the c# that calls it...

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

using System.Web.Mail;
using log4net;
using MCE.ConfigHandler;

namespace WebApplication4
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{

public OracleConnection _oraConnection = null;
public void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here

OracleCommand _oraCommand = new OracleCommand();
_oraCommand.CommandText = "MAILSERVICE.InsertEMail";
_oraCommand.CommandType = CommandType.StoredProcedure;
_oraCommand.Connection = GetConnection();
_oraCommand.Parameters.Add("i_SendTo", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";
_oraCommand.Parameters.Add("i_SendFrom", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";
_oraCommand.Parameters.Add("i_Subject", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";
_oraCommand.Parameters.Add("i_EMailBody", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";
_oraCommand.Parameters.Add("i_BodyFormat", OracleDbType.Double, ParameterDirection.Input).Value = 1;
_oraCommand.Parameters.Add("i_Priority", OracleDbType.Double, ParameterDirection.Input).Value = 1;

_oraCommand.Parameters.Add(" i_errorEmailAddress", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";
_oraCommand.Parameters.Add(" i_ccEmailAddress", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";
_oraCommand.Parameters.Add(" i_systemName", OracleDbType.Varchar2, ParameterDirection.Input).Value = "CC";


try
{
_oraCommand.ExecuteNonQuery();
}
catch (OracleException oex)
{
Response.Write("Failed to execute stored procedure MAILSERVICE.InsertEMail"+"<br/>"+"<br/>"+ oex);
}
}

public OracleConnection GetConnection()
{
if ( _oraConnection == null )
{
_oraConnection = new OracleConnection(SecurityConfig.GetVersionSetting("WebSecurityDB") + SecurityConfig.GetVersionSetting("WebSecurityDBIdentity"));
_oraConnection.Open();
}
return _oraConnection;
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}


This is the error I am getting...

Failed to execute stored procedure MAILSERVICE.InsertEMail

Oracle.DataAccess.Client.OracleException ORA-06550: line 1, column 103: PLS-00103: Encountered the symbol ":" when expecting one of the following: ( - + case mod new not null others avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String procedure, String[] args) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String[] args) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at WebApplication4.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\webapplication4\webform1.aspx.cs:line 50


The error revolves around the three extra columns i have added and trying to added data to them

i_errorEmailAddress,
i_ccEmailAddress,
i_systemName,

I have no idea what the problem is as all the other values work and it seems the exact same.....

Can anyone spot the problem,,,
I dont think the column values are wrong as two are taken from sysdate so are not there as parameters but im not sure :(
 
Did you write the code? You seemed suprised the error catching worked...

Do you have to declare then define the procedure instead of just defining it as you do?

Anyway, I noticed that the 3 params you are talking of have spaces before their names, as such:

_oraCommand.Parameters.Add(" i_errorEmailAddress", OracleDbType

That might be the problem.
 
i spotted that too

Diesel said:
Did you write the code? You seemed suprised the error catching worked...

Do you have to declare then define the procedure instead of just defining it as you do?

Anyway, I noticed that the 3 params you are talking of have spaces before their names, as such:

_oraCommand.Parameters.Add(" i_errorEmailAddress", OracleDbType

That might be the problem.
Click to expand...

ya to my absolute horror i noticed that as well, i spend a day staring at this problem,,,, i nearly threw the computer across the room in frustration
 
Back
Top