Oracle 11g Stored Procedure - Expects A Parameter As A Number, But Receives A String?
Mar 23, 2011
we recently upgraded an application (.NET 2.0) that we maintain to use Oracle 11g. The application uses MS Enterprise Library 2.0. We've found that when the database.ExecuteNonQuery(Oracle.DataAccess.Client.OracleCommand) method is called, it bombs when the stored procedure expects a parameter as a number, but receives a string. This didn't happen prior to upgrading. If I cast the parameter to an Int, I don't recieve the error. Was something possible missed during the upgrade? The issue occurs regardless if we are hitting an 11g database, or a 10g database.
EDIT: I neglected to mention that this same issue does not occur when ExecuteDataReader is called (handles implicit conversion of the datatype).
View 1 Replies
Similar Messages:
Mar 1, 2010
We have an ASP.NET web service that invokes a stored procedure on our DB (Oracle 11g). The problem is that the call to the procedure blows up every time an empty string is passed as one of the parameters. We're seeing an ORA-01084 error, which indicates to me that call is failing before the procedure is actually run.
Here's the procedure, minus some logic that I believe is not relevant:
[Code]....
As an experiment, I modified the web service to pass null rather than an empty strings. When null is passed in, I see an error indicating "wrong number or types of arguments in call to 'CREATEREPORT."
I also tried passing DBNull.Value whenever the params were null/empty, but that resulted in the error
Parameter 'p_column_id': No size set for variable length data type: String.
(Of course, p_column_id was the empty parameter in this case).
So, how can I successfully pass empty strings as parameters to my stored procedure? We definitely want to allow the p_column_id parameter to be empty.
View 1 Replies
Apr 7, 2010
Driving me mad on a personal project; I know I've done this before but elsewhere and don't have the code. As far as I can see, I'm setting the parameter, I'm setting its value, the connection is open, yet when I try to fill the dataset I get the error 'Procedure or function expects parameter "@test" which was not supplied'. (This is obviously a simplified test! Same error on this or the real, rather longer code though.)
C#:
SqlCommand l_oCmd;
DataSet l_oPage = new DataSet();
l_oCmd = new SqlCommand("usp_test", g_oConn);
[code]...
View 1 Replies
May 11, 2010
I keep getting this error:
Procedure 'cnpcpiFarmFormAssignField' expects parameter '@chUserId', which was not supplied.
[Code]....
View 4 Replies
Jul 28, 2012
getting this error while inserting near ExecuteNonQuery(); Procedure or function 'Transaction' expects parameter '@idcount', which was not supplied.
View 1 Replies
Nov 11, 2010
I have finally got my paging code for a datalist to work correctly. However, the stored procedure I use receives a Parameter called 'name' which is a VarChar 4. I'm having a little trouble implementing the handling of the 'name' parameter in the following code. I receive the error [Procedure or function 'search' expects parameter '@name', which was not supplied.]
Private Sub DataBind()
Dim objConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQL2005_504887_2onetennisConnectionString").ConnectionString) [code]....
I think it will look something like the following but I'm not sure because this syntax doesn't look like it would work with my above code.
cmdTest.CommandType = Data.CommandType.StoredProcedure
cmdTest.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10))
cmdTest.Parameters("@TestParam").Value = "Testing"
View 1 Replies
Apr 2, 2010
This is surareddy. i nead some small clarification in the "Stored Procedure"
how to convert the oracle Stored Procedure to sqlserver2005/2008 Stored Procedure.
right now i am enhancing the project that project already developed the oracle Stored Procedure. now our company is using sqlserver 2005/2008.
how to convert the Oracle Stored Procedure to sqlserver 2005 Stored Procedure
View 4 Replies
Jun 16, 2010
How can I get the delete method to work? I keep getting an error:
"Procedure or Function 'deleteArticleAttachment' expects parameter '@articlefileid', which was not supplied."
[Code]....
View 3 Replies
Jul 19, 2010
I have a stored procedure which is declared as follows: ALTER PROCEDURE dbo.thisProc @ID int,@TypeID int, @DocID int, @Section varchar(10) What I need is to be able to do this: If @ID is supplied, execute a particular if block if @ID is not supplied then move check if @TypeID is input and then execute another if block. I don't want to write multiple sql queries to deal with different inputs/page sections that a user is viewing.
SAMPLE CODE:
CREATE PROCEDURE GetArticle
@ID int,@DoTypeID int, @DocID int, @Sec varchar(10)
AS
IF @ID IS NOT NULL AND , @DocID IS NOT NULL AND @Sec = 'inner'
BEGIN
SELECT "my query is here"
WHERE Articles.ID = @ID AND Articles.DocID = @DocID
END
ELSE IF @ID IS NULL AND @DocID IS NULL AND @Sec = 'list'
BEGIN
EXEC GetDocList @DocTypeID
END
ELSE IF @ID IS NULL AND @DocID IS NULL AND @Sec = 'full'
BEGIN
EXEC GetDocList @DocTypeID
END
View 1 Replies
Jan 13, 2011
in my asp.net application, i am calling a stored procedure (oracle) to get some values from database.
Following is the sp:
[code]....
how to call this sp from my asp.net code.
View 2 Replies
Feb 23, 2011
I would like to know if there is a way to call Oracle stored procedure from inside code?
View 2 Replies
Oct 20, 2010
I am goig to execute an oracle store procedure using C# through through ODP.NET. The store procedure executes succsfully but it does not return any value in the out parameter.
When I execute the same store procedure with the same input parameteres from within SQL*Plus. It does return values.
This is to be clearify here that in the oracle store procedure the data type for the out parameter is number and in the c# the variavle that will hold its value is Integer. I also tried using Long on the C# side but it is still not working. After the successfull execution of the store procedure the C# variable contains nulll value.
View 3 Replies
Apr 10, 2010
i want to send mails internally and externally for eg. i want to send mail to my boss in the company and also to the customer which doesnt belong to our domain.
[URL]
and also i want to attach an excel file which is stored in my computer. so how am i going to access that file through the same procedure and then attach the file and send it to both internal as well as external email ids.
View 1 Replies
Feb 4, 2011
My Stored procedure takes one parameter @IDs and it has values in it like '1,2,3'. I want to get rid of these single quotes. How can I do it? Like I want just 1,2,3 and NOT '1,2,3'. How do I modify this parameter value inside my SP?
View 3 Replies
Mar 23, 2011
When I try the following:
[Code]....
I get an error saying No overload for Add method takes 2 arguments. HOw would i pass the PaycheckID in then as a paramter?
View 2 Replies
Mar 16, 2011
Being new to using the declarative syntax of SqlDataSource I am trying to figure out a way to set the value of a parameter to a stored procedure. I have a Client_ID that is passed via the Request Object and I need to set the Client_ID before the stored procedure of the SqlDataSource is executed.
Does the stored procedure parameter have to be predefined in the ASPX markup or can it be added dynamically in the code-behind? Would anyone have an example that demonstrates the SqlDataSource markup with a stored procedure and parameter as well as setting that parameter value in code-behind?
View 1 Replies
Oct 7, 2010
how do I add an output parameter to a Stored Procedure that returns a code or string to the calling app
View 1 Replies
Jul 30, 2010
I am having some trouble with the below code snippet. It is a stored procedure which executes on load that has an output parameter which should display in a label. Familiar which Vb however not so much in C#.
View 2 Replies
Jan 26, 2011
I have an application with 40 or so separate databases that share a number of identical table schemas. Each is for a separate agency that performs functions identical (nearly) to the others. I have a number of reports that involve complex queries that each agency is required to produce. There is a "top-level" agency that would like to run the same reports but have the data consolidated for ALL agencies. I have tried having the query generated simply re-produced for each sub-agency by using a "UNION" ..
SELECT .... FROM [Agency].[dbo].[Table] ... UNION SELECT ... FROM [Next Agency].[dbo].[Table]....
This works for a few agencies selected/included but when they try to select all agencies or more than a few, it fails because the query is too large. I have tested a stored procedure:
CREATE PROC Test @DB AS
SELECT ... FROM [@DB].[dbo].[Table]...
Thinking I can build a smaller query by having up to 40 items of "EXEC Test (db) UNION EXEC Test (next db)" That also fails.... won't permit the database specifier as a parameter (plus, I don't know if I can do a UNION on the EXEC results).
View 9 Replies
Jan 9, 2011
i made stored procedure like that
select AssetCode ,Description
,EquipmentID from [FATMS].[fnMaintenanceHeaderRead](@ContextUser,@ContextClient,@ContextLang,@ContextApp,@ContextOrg,'1',@fnXmlParamMaintenanceHeader)
MH
where MH.Status
IN
( @Status)
when i put three values for parameter (@status) not work but one value work
how to put three values please to parameter @status when excecute that stored procedure
EXEC @return_value = [FATMS].[spMaintenanceReport]
@ContextUser = N'4806',
@ContextClient = 1,
@ContextLang = N'en-US',
@ContextApp = N'FATMS',
@ContextOrg = 23,
@ActionCode = N'1',
@MaintenanceHeaderId='',
@SearchText='',
@EntryType='0',
@Status=N' '
SELECT 'Return Value' = @return_value
View 12 Replies
May 18, 2010
How do I set my insert identity return value parameter in codebehind? I have working code now but i have all of my parameters set in codebehindexcept the following:
[Code]....
View 6 Replies
Nov 23, 2010
I have many stored procedure in database sql server , so ow can i count the number of stored procedure which tel me the result
like for example i made 39 sps but i dnt know
Is there any way to count the number of stored procedures in specific database?
View 7 Replies
Feb 17, 2010
how can i pass a session variable in a C# web form to a stored procedure..?
View 1 Replies
Jul 28, 2010
I want to know, how do i send a multivalue parameter from C# to Stored Procedure which is used in WHERE CLAUSE in IN statement?
for eg:
CREATE PROCEDURE [dbo].[sp_GetPolicyDivOff1]
@Insid varchar(5000)=null
AS
SET NOCOUNT ON
SELECT MST_GroupCompany.GC_Name,MST_DivisionalOffice.DIV_Name FROM MST_DivisionalOffice INNER JOIN MST_GroupCompany ON
MST_DivisionalOffice.DIV_FK_GC_ID=MST_GroupCompany.GC_ID where GC_Name IN(@Insid)
View 10 Replies
Jul 14, 2010
I need to send a table with value as a parameter in a stored procedure. It is possible any how?
View 3 Replies
