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:

[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


ADVERTISEMENT

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 View Related

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 View Related

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:

[Code]....

Stored Procedure:

[Code]....

The results page datalist:

[Code]....

Code Behind of results page:

[Code]....

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 View Related

Databases :: Call A Stored Procedure From Within A Stored Procedure In Oracle

Jun 10, 2009

I am new to oracle and trying to figure out how to call a Stored Procedure from within Another Stored Procedure. In SQL Server Syntax is Exec StoredProcedureName Parameter1, Parameter2 . . . .

View 2 Replies View Related

Converting Empty Textbox To Null Instead Of Empty String?

Oct 27, 2009

I have an app where I am writing values to a database. One field is a textbox that is the ID (set in sql to start at 1 and auto increment) In my stored procedure if it is null or len < 1 then insert. However, this textbox is left blank which sets the value to an empty string and the stored procedure is looking for an int.

This is the line of code I am having problems with:

cmd.Parameters.Add("@QuoteID", SqlDbType.Int, 4).Value = Convert.ToInt32(txtQuoteNumber.Text);

I get input string in incorrect format because the value I am guessing is "" a string when it wants an int.Is there a way to say if the textbox is blank then it is a null value not ""

View 9 Replies View Related

Calling Oracle Stored Procedure From Application?

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 View Related

Databases :: How To Call Oracle Stored Procedure Using C#

Dec 1, 2009

im new to using stored procedures and i have the following stored procedure...

CREATE OR REPLACE PROCEDURE DRL_PROCEDURE2(var_SOURCE_OBJECTID IN varchar2, var_NEW_OBJECTID OUT varchar2)
AS
BEGIN
SELECT MAX(NEW_OBJECTID)
INTO var_NEW_OBJECTID
FROM DRL_CONVERSION
WHERE SOURCE_OBJECTID = var_SOURCE_OBJECTID;

View 1 Replies View Related

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 View Related

Databases :: How To Return Result From Oracle Stored Procedure

Jan 15, 2007

I am basically a SQL Server developer but I have been asked to work on Oracle 8i appication. I would want to know

1.) how to write a stored procedure in oracle which gives resultset of the query ( select * from emp)

2.) call it in c# code

3.) use the results of the strored procedure from step1 to bind a datagrid.

View 15 Replies View Related

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.

[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 View Related

Databases :: Getting Multiple Rows From Oracle Stored Procedure And Binding It To Gridview?

Apr 15, 2008

To get this stuff you need to create a Ref Cursor to return recordset. create or replace PACKAGE Types AS

TYPE cursor_type IS REF CURSOR;

END Types;

View 1 Replies View Related

Databases :: How To Create TableAdapter For Oracle Stored Procedure With T_CURSOR Output Parameter

Oct 23, 2009

I'm trying to call Oracle stored procedure with T_Cursor output parameter to produce TableAdapter (Dataset Schema xsd)

View 1 Replies View Related

Forms Data Controls :: Intercepting Null And Empty String Values Before Using Filter String?

Dec 7, 2009

I currently have some trouble dealing with null and empty string values from my datasource, namely that they seem to break my filter strings when they get there from comboboxes, textfields etc... I'd like to know what's a good way to catch them before they enter the filterstring and turn them into something more useful, such as default none/no/empty etc...

View 3 Replies View Related

Passing A Parameter To Stored Procedure In C#?

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 View Related

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 View Related

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)
myCommand.Parameters.Add(ptitle)
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 View Related

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..

[Code]....

View 1 Replies View Related

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 View Related

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 View Related

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.

BEGIN
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

BEGIN
Select @@ROWCOUNT as Priority2
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

BEGIN
Select @@ROWCOUNT as Priority3
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

BEGIN
Select @@ROWCOUNT as Priority4
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

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 View Related

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:

[Code]....

c# code:

[Code]....

Stored procedure:

[Code]....

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 View Related

Passing A Fields Value Based On A Parameter Stored Procedure?

May 21, 2009

I have the following to create a stored procedure and it works fine, but I need to get the list of JobId's in the output without supplying parameters. Here is what I am trying to do:

The parameter is automatically entered through code.

CREATEPROCEDURE spSelectedJobs
@id intOUTPUT
AS[code].....

This would provide a list of jobs that the user has not selected.

View 1 Replies View Related

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
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 View Related

DataSource Controls :: Passing Parameters To Stored Procedure?

Nov 24, 2009

I've implement paging in my datalist. I've followed the steps given in this link. [URL] Everything work fine. But i don't know how to pass search parameter to this below stored procedure.

CREATE PROCEDURE [dbo].[LoadDataTest]
@startRowIndex int,
@pageSize int,
@totalCount int output
AS
BEGIN
SET NOCOUNT ON;
SET @totalCount = 0
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT * FROM (
Select SampleTable.*, ROW_NUMBER() OVER (ORDER BY SampleTable.AutoID DESC) as RowNum
FROM SampleTable WHERE
SampleTable.Active = 1
) as ArticleList
WHERE
RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY AutoID DESC
SELECT @totalCount = Count(AutoID) FROM SampleTable WHERE Active = 1
END
END
CREATE PROCEDURE [dbo].[LoadDataTest]
@startRowIndex int,
@pageSize int,
@totalCount int output
AS
BEGIN
SET NOCOUNT ON;
SET @totalCount = 0
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT * FROM (
Select SampleTable.*, ROW_NUMBER() OVER (ORDER BY SampleTable.AutoID DESC) as RowNum
FROM SampleTable WHERE
SampleTable.Active = 1
) as ArticleList
WHERE
RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY AutoID DESC
SELECT @totalCount = Count(AutoID) FROM SampleTable WHERE Active = 1
END
END

View 7 Replies View Related

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)

[Code]....

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 View Related

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 View Related

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 View Related

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.

<tr>
<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>
</tr>

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

View 5 Replies View Related

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:

[Code]....

and use that array to run the fillowing sql command:

[Code]....

View 17 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved