Passing Null / Empty String To Oracle Stored Procedure?

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:


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

Similar Messages:

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

DataSource Controls :: How To Convert The Oracle Stored Procedure To Sqlserver 2005 Stored Procedure

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

DataSource Controls :: Passing Query String Value To Stored Procedure

Mar 30, 2010

I'm trying to pass the text value of a text box as a query string value to a stored procedure. I was hoping someone could point me in the right direction. Button Code:


Stored Procedure:


The results page datalist:


Code Behind of results page:


I'm sure i've got this entirely backwards. However if I change the NULL value in the stored procedure with some string value (eg."Donkeys") it pulls all the values that contain "Donkeys" into the datalist on my results page.

View 2 Replies

Calling Oracle Stored Procedure From Application?

Jan 13, 2011

in my application, i am calling a stored procedure (oracle) to get some values from database.

Following is the sp:


how to call this sp from my code.

View 2 Replies

Databases :: How To Call Oracle Stored Procedure

Feb 23, 2011

I would like to know if there is a way to call Oracle stored procedure from inside code?

View 2 Replies

Databases :: How To Use Oracle Stored Procedure To Send Mail With Attachment

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.


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

Passing A Parameter To Stored Procedure In C#?

Mar 23, 2011

When I try the following:


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

ADO.NET :: Passing An Array To Stored Procedure?

Mar 21, 2011

I want to send an array of integer to sql server Stroed Procedure as parameter from vb program. How i can do this.

Secondly how I declare an parameter array in stored procedure which accept the array of integer.

View 1 Replies

ADO.NET :: Passing Parameters To A Stored Procedure Using A SqlDataAdapter?

Dec 10, 2010

I am trying to create a method which will access the database via a SqlDataAdapter. I have passed two parameters month and year. When I run it, I get the error "Object reference not set to an instance of an object". I have posted the complete code below. Please take a look..


View 1 Replies

Passing Blank Field Value To Stored Procedure C#?

Feb 21, 2011

I want to return all rows from a SQL Server 2008 database table into a SQL data source if a textBox field is blank. So I made a stored procedure with an if @date IS NULL clause.

Although the stored procedure seems to be working fine in Visual Studio 2008 the actual webpage displays no results.

I am guessing I have to send a DBNull value to the stored proc if textBox.Text == string.Empty. I tried the SqlDataSource1.SelectParameters.Add but it seems that I get a conversion error from DBNull.Value to string.

Is this the source of my problems or am I missing something else? How would I pass DBNull.Value to a stored proc if a textfield is blank?

View 2 Replies

Passing List Of Name / Value Pairs To Stored Procedure?

Aug 10, 2010

I have a name/value pair in a List<T> and needing to find the best way to pass these to a stored procedure.

Id Name
1 abc
2 bbc
3 cnn

What is the best way to accomplish this?

View 3 Replies

Stored Procedure Parameters - Sometimes NULL?

Jan 19, 2011

i have a stored procedure to update a table. not all fields are required and some can be blank.for example, title is not required, but when it's blank i get an error

Dim ptitle As New SqlParameter("@title", SqlDbType.NVarChar, 20)
ptitle.Value = objFormat.CheckSQL(title)
ex {"Procedure or Function 'sp_UpdateUser' expects parameter '@title', which was not supplied."} System.Exception

how can i fix it so that it allows for nulls if the data field is blank?

View 6 Replies

DataSource Controls :: Eliminate Returning Of Empty Table From Stored Procedure

Jul 12, 2010

I have a Procedure which should return only 1 Record on selecting record according to Priority.

DECLARE @Accounts nvarchar(MAX)
SELECT @Accounts = ISNULL([map_user_modules].[Accounts], '') FROM [map_user_modules]
WHERE [map_user_modules].[id_user] = 231 AND [map_user_modules].[id_module] = 5
IF ((@Accounts = '') OR (@Accounts = 'All'))
SELECT @Accounts = null

Select @@ROWCOUNT as Priority2

Select @@ROWCOUNT as Priority3

Select @@ROWCOUNT as Priority4

In the above statement Suppose we take a scenario where Select @@ROWCOUNT as Priority3 is executed, I get two tables returned, One is the Table returned by Select @@ROWCOUNT as Priority3 and the other is the Table returned by Select @@ROWCOUNT as Priority2 which has no Records. How can I eliminate the Empty table that returns no records.

View 1 Replies

SQL Server :: Passing Multivalue Parameter To Stored Procedure

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

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

SQL Server :: Passing An Array From C# Code-behind To Stored Procedure?

Nov 2, 2010

Via the button1_ click event I retrieve all files in a directory (Example Follows)


I now need to check whether this filename exists as a field in a SQL Server table. For example, if the directory retrieves a file named ExampleFile.txt which does not exist in the (Table = TableFileStore;s Field = FileRetrievals) then I want to insert the filename.

How do I construct a stored procedure to pass the filename(s) into a stored procedure as a parameter? Do I need to also create an array of filenames and pass the array into my stored procedure? Does the stored procedure belong within the foreach loop?

View 3 Replies

SQL Server :: Passing A Variable To A Remote Stored Procedure?

Feb 27, 2011

I have a SQL stored procedure on a SQL server that uses a variable to create a new database. This works in SQL I have a connection string that works I have added a sqldatasource to a webform, and configured the datasource to connect to the remote SQL database and am able to select my stored procedure. I am able to bind the variable to a control in the sqldatasource wizard (often two of the same control appear- if that is significant)

When I test the sqldatasource in the configuration wizard and put in a manual entry, the procedure runs and works

It is on the web page I am having troubles. I want the user to enter a name in a text box, click a button/link, and run the procedure. Ive used buttons and links...but not in the right way I think, becuase when I click them the stored procedure doesnt run.

As mentioned above, I have selected the right control in the sqldatasource wizard. So what do I need to do now?

Is it button click event? If so whats the syntax?

Should I be embedding my textbox in a dataview?

View 4 Replies

Passing Whole Dataset To Stored Procedure In MSSQL 2005?

Oct 5, 2010

How do I pass a dataset object to a stored procedure? The dataset comprises multiple tables and I'll need to be able to access them from within the SQL.

View 3 Replies

DataSource Controls :: Passing Radiobuttonlist Value To Stored Procedure?

Dec 11, 2010

I have an .ASPX form. All I want to do is simply "Submit" the form field values into a database.

"I have been tasked with re-writing the entire .ASPX form to use "Stored Procedures" instead of a "SQL-String" to insert form values. I think I have most of the form submitting to the database, except the "Radio Buttons" just say "NULL".

I do not know how to progam the Radio Buttons to insert the value (Yes, No, N/A) the user chose into the database. I have GOOGLED but cannot find an answer, and some answers are too complex for my level of understanding at this point.

I need to take the following code and change it so it just calls a Stored Procedure. If you could show me "Stored Procedure" code and how to change my current code to work with the Sproc it would be much appriciated. Here is the code.

I have "11" groups of Radio Buttons I need to change. Here is an example of one of the rows.

<td><input type="radio" name="group1" value="yes"></td>
<td><input type="radio" name="group1" value="no"></td>
<td><input type="radio" name="group1" value="N/A"></td>
<td><asp:Label ID="Label22" runat="server" Text="Surfaces exposed to corrosives pH between 5 and 9."></asp:Label></td>

Summary: I just want to call a stored procedure and store the value the user chose into my database.

View 5 Replies

SQL Server :: Passing Array Paramaters To Stored Procedure?

Nov 1, 2010

I need to send an array of strings to a stored procedure and then use that array of strings to create a filter of the data returned.for example I need need to send the following array to the strored procedure:


and use that array to run the fillowing sql command:


View 17 Replies

SQL Server :: Insert NULL Int Into Stored Procedure?

Jan 17, 2011

I'm strugling with this problem for quite some time and I've tried many different solution but nothing works. In my application there are 3 textboxes. First is mandatory but user can leave second and third empty. I that case I want that application insert NULL.

Here is .aspx code:


c# code:


Stored procedure:


I've tested code when user fills all 3 textboxes and it's working fine. If one second or third (or both) textbox(es) is(are) empty I wish to insert NULL into table. I've tried with DbNull.Value, null, 0, "3232131" and nothing works. I always get error Input string was not in a correct format. which is strange since code works when 2. and 3. tbs are filled and I'm not inserting string. And I've specified that table allows inserting of null values.

View 13 Replies

SQL Server :: Stored Procedure Is Not Passing In A Primary Key Into A Foriegn Key In Another Table?

Jul 14, 2010

I am a rookie developer here and I have tried looking around for an answer but I am little confused. I have a stored procedure that calls multiple stored procedures when I go to create a new contact.[Code]....

I though I could use the OUTPUT Command to copy out the NewContactID but I am getting all kinds of syntax errors. I am confused as to what I am missing here.

View 3 Replies

DataSource Controls :: Using LINQ Passing The Name Of The Stored Procedure Dynamically?

Feb 23, 2010

I have an application where depending on what the user selects run a different stored procedure.

I am using LINQ and am passing the stored procedure name into a sub that will run the stored procedure and return values that I am loading into a DDL.

For example I want to pass the procedure name "GetProducts_Hats" to the sub and not hardcode it.

Protected Sub LoadParamDropDowns(
ByVal SqlObjToExec
String )

I am passing the storedprocedure name in parameter SqlObjToExec

so instead of

values = mydb.GetProducts_Hats

I want to code it to be a variable

values = mydb.SqlObjToExec

But the above code does not work.

View 7 Replies

DataSource Controls :: Passing Column Name As Parameter To Stored Procedure?

Feb 12, 2010

ALTER procedure [dbo].[Sample2]
@Col as nvarchar(1000),
@Value as nvarchar(1000)


View 5 Replies

DataSource Controls :: Passing Default Parameter To Stored Procedure ?

Feb 13, 2010

I am using DAAB with stored procedures. I use stored procedures more then once if possible. If I add a parameter later in stored procedure, I have to supply value for this in functions where it is even not being used as well, other wise it shows an error that number of parameters are not same. if I add parameter in last of parameter list in stored procedure and set a default value for it and I may have not to provide value for this from functions where I am not using it.

View 1 Replies

Copyrights 2005-15, All rights reserved