.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Post New Web Links

Oracle Store Procedure does not return any thing in the out parameter

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :ASP.Net
 

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 Complete Post


More Related Resource Links

How many out parameter we can return by Store procedure?

  

How many out parameter we can return by Store procedure?


Waiting for reply.


Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Value ....

  

My Store Prodecure.....

------------------------------------------------------------------------------------

ALTER PROC [dbo].[Polls_Polls_SaveAdmin]  
@UserID INT,
@CategoryID INT,
@TypeID INT,
@OptionID INT=NULL,
@Title nvarchar(4000),
@Description nvarchar(MAX),
@StartDate DATETIME,
@EndDate DATETIME,
@PollID INT,
@OptionA NVARCHAR(100),
@OptionB NVARCHAR(100),
@OptionC NVARCHAR(100),
@OptionD NVARCHAR(100)
AS
    DECLARE @SaveStatus INT
    DECLARE @SaveStatus1 INT
    SET @SaveStatus = 0
    SET @SaveStatus1=0

BEGIN
    IF @PollID>0
       BEGIN
        IF NOT EXISTS (SELECT POLLID FROM POLLS WHERE (CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) ) OR (CAST(CONVERT(VARCHAR, EndDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) AND PollID<>@PollID))
            BEGIN
           &

store procedure parameter sequence Change In runtime...

  

hi...

this is my code:


builder.Append(string.Format("INSERT INTO deads " +
            " (DieID, Title, FName) " +
            "VALUES ( {0}, {1}, {2} )",
            dt_excel_allRows.Rows[1][0].ToString(), dt_excel_allRows.Rows[1][1].ToString(), dt_excel_allRows.Rows[1][2].ToString()))
           




some of my col datatype is nVarchar...

in run time because  my value in my table (dt_excel_allRows) are FARSI ; the sequence of my parameter changed and it cant be executed!!!!!

of course the problem is because of FARSI is rtl languges....

how can i fix the problem...

i can not user sqlPArameter becuse of some reason....

i think some thing like rtl or ltr can fix my problem....but how?

tanx



Error on getting return value from Oracle procedure in SSIS package

  
In my case, I want to execute a stored procedure in Oracle database using SSIS package and if there has error in Oracle, I want to get the error message from Oracle and pass it to SSIS to log the error.

I can execute the stored procedure through SSIS successfully, but the problem is I cannot get the error message using OUT parameter of procedure in Oracle, the following part is what I have done to test whether SSIS can fulfill my requirement:

1. Oracle client installed, use Microsoft OLE DB Provider for Oracle, Test Connection to Oracle succeeded.

2. The stored procedure created in Oracle database:
    create or replace p1(in1 varchar2, out1 OUT varchar2) as begin
        out1 := 'nice';
    end;


3. In the SSIS package, I add a "Execute SQL Task" component:

Variable defined = User::output (String datatype)
ResultSet = Single Row
SQLSourceType = Direct Input
SQLStatement =

    declare
        in1 varchar2(10);
        out1 varchar2(10);
    begin
        in1 := 'wow';
        out1 := '1a2';
&nbs

How to call Oracle Stored Procedure which has an output parameter from SSIS?

  

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

 

    PROCEDURE Interface_Begin

    (p_from_dttm       OUT varchar2,

     p_error_code            OUT number,

     p_error_text       OUT            varchar2,

     p_proc_name        OUT varchar2);  

 


How to save return value from store procedure in ssis and use that value to call another store proce

  

I have bunch of store procedures and each procedure is depend on previous procedure's return value.I am using Execute Sql task to call store procedure.I don't have any input parameters,just simple select statement.I want to save the return value from that store procedure into variable and use that value to call another store procedure.

Can someone help on this...

Thanks in advance..


Sort by gridview SortExpression parameter via Stored Procedure

  

I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                        <Columns>
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                <ItemTemplate>
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />
                            <

Trying to run a stored procedure from vb code with oracle data provider.

  

Hello,

Here is my SP:

create or replace
PROCEDURE ZGETUSERSSIGNONS (vUid IN VARCHAR2, p_getuserssignon_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
FROM ZSIGNON
WHERE substr(UserId,1,2) <> vUid
Order By UserId;
END ZGETUSERSSIGNONS;

I would like to run this SP from code and fill a gridview with the result. 

I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 

Second:

Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

(I am using VS 2005, VB).

Thank you.

 

 


Using IN clause(string parameter) in Stored Procedure

  
Hi guys. I need to use IN clause in a selection query. From all the resources that I found from google, it's all about adding a function to split a integer parameter into a list. But I got a string parameter, for example 'A1,B1,C1' I tried to use the below where single quotes in @CustCode is double up. ___________________________________ Declare @SQL VarChar(1000)   SELECT @SQL = ' SELECT [something]' SELECT @SQL = @SQL + ' FROM [Table]' SELECT @SQL = @SQL + ' WHERE CustomerCode IN ('  + @CustCode + ')'   EXECUTE (@SQL) _____________ Executing the stored procedure in MSSQL works fine, but it didn't when executing from Visual Studio 2005. Please advice.

optional output parameter in CLR Stored Procedure

  
I'm trying to transpose a Procedure that sends mail via CDO.Message and Ole Automation to a .Net CLR Procedure. this procedure must have the same parameters in the same order as the original to maintain compatibility with everything that already uses it. the declaration goes as follow   <Microsoft.SqlServer.Server.SqlProcedure(name:="SP_ENVIAEMAIL")> _<br/> Public Shared Sub ENVIAEMAIL(<SqlFacet(MaxSize:=100)> Optional ByVal De As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Para As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Copia As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Oculta As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Assunto As String = "", <SqlFacet(MaxSize:=-1)> Optional ByVal Mensagem As String = "", _<br/> <SqlFacet(MaxSize:=-1)> Optional ByVal Anexo As String = "", _<br/> <strong><SqlFacet(MaxSize:=1000)> <Out()> Optional ByVal Retorno As String = ""</strong> , _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Smtp As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Usuario As String = "",

Help!, Problem with creating my store procedure.

  
I have two tables. Product_Table with ProductID as the primary key. My other table, ProductSKU_Table which has SKU as the primary key and ProductID as the foreign key. I am trying to create a store procedure to insert into both tables. How do I insert the primary key from the Product_Table into the foreign key of the ProductSKU_Table? This is what I have so far:ALTER PROCEDURE [dbo].[sp_addNewProduct] ( @productName varchar(50) = null, @shortDescription varchar(100) = null, @longDescription varchar(4000) = null, @active bit = null, @category int = null, @sku varchar(11), @skuDescription varchar(200) = null, @orginalPrice money = null, @sellingPrice money = null, @specialPrice money = null, @qtyOnHand int = null, @qtyAtSupplier int = null, @imagePath varchar(200) = null, @releaseDate datetime ) AS BEGIN DECLARE @lastIDInserted int INSERT INTO Product_Table(Name, ShortDescription, LongDescription, Active, CategoryId) VALUES(@productName, @shortDescription, @longDescription, @active, @category) SET @lastIDInserted = @@IDENTITY INSERT INTO ProductSKU_Table(ProductId, SKU, Description, OrginalPrice, SellingPrice, SpecialPrice, QtyOnHand, QtyAtSupplier, ReleaseDate, imagePath, CreatedDate, ModifiedDate) VALUES(@lastIDInserted, @sku, @skuDescription, @orginalPrice, @sellingPrice, @specialP

Insert into and Store procedure problem

  
Hello I am trying to create store procedure wich will insert data in temp table CREATE PROCEDURE GetDataForUpdate AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDataForUpdate_temp_tb end go WITH cte as (select Sp.[Item No_], Sp.[Starting Date], It.[No_], It.[Manufacturer Code], It.[Description], Sp.[Unit Price], row_number () over (partition by Sp.[Item No_] order by Sp.[Starting Date] desc) as rn from dbo.[Main-db$Sales Price] AS Sp JOIN dbo.[Main-db$Item] AS It ON Sp.[Item No_] = It.[No_] WHERE Sp.[Sales Code]='RETAIL' AND Sp.[Item No_] LIKE 'I%' ), cte2 as ( SELECT [Item No_],SUM(ISNULL(Quantity, 0)) AS Qty FROM [Main-db$Item Ledger Entry] WHERE [Item No_] LIKE 'I%' AND ( [Location Code] = 'WH-AB-#2' ) GROUP BY [Item No_] ) SELECT cte.[Manufacturer Code],cte.[Description], CONVERT(int, cte2.[Qty]) AS 'Qty',CONVERT(int, cte.[Unit Price]) AS 'Unit Price' INTO GetDataForUpdate_temp_tb FROM cte JOIN cte2 ON cte.[Item No_]=cte2.[Item No_] WHERE rn = 1 ORDER BY cte.[Item No_],[Starting Date] But after execute, lefts only this part of query in store procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetDataForUpdate] AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDa

problem for using webservice (sql parameter stored Procedure)

  
i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

stored procedure with parameter quest

  
I can execute this statement fine: SELECT item,cust_name,OFFICE from sample where cust_name = @p1 but what if I wanted to use "where cust_name like @p1" ? It does not seem to give any results nor error. I have tried "where cust_name like '%' + @p1 + '%' to no avail

SQL Server 2008 Stored Procedure output parameter

  
I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

Launching a stored procedure with parameter from Excel 2003

  
I have a stored procedure in SQL Server which needs a parameter and returns a resultset.   sp_xxx 'parameterValue'   I'd like to know if it is possible to launch this stored procedure through Excel 2003 and get the resultset in the active spreadsheet.   I have tried to do this with Microsoft Query but it doesn't allow parameters in queries that can't be graphically represented.   I have tried also through an ODC files but I get an error.   Is it possible to do this?

ExecuteNonQuery trimming return parameter value

  
Hi all,I am observing a rather strange behavior. I have a procedure which selects a field from a matching record and returns it. The procedure works as expected returning proper values, when I execute the procedure from Visual Studio Server Explorer tab. The procedure isALTER PROCEDURE dbo.p_UpdNinjaName ( @ninja_name nvarchar(50), @user_id int, @ReturnMessage nvarchar(250) output ) AS /* SET NOCOUNT ON */ select ninja_name from users where ninja_name =@ninja_name if (@@ROWCOUNT> 0) begin SET @ReturnMessage = 'EAE'; /*Error : Already Exists*/ end else begin update users set ninja_name = @ninja_name where user_id = @user_id set @ReturnMessage = (Select ninja_name from users where user_id = @user_id) endThis is being called from the DAL as follows:SqlCommand cmd = new SqlCommand(storedProc, this._connection, this._transaction); cmd.CommandType = CommandType.StoredProcedure; DBParameterMap.Enumerator mapEnum = dbParameterMap.GetEnumerator(); while (mapEnum.MoveNext()) cmd.Parameters.AddWithValue(mapEnum.Current.Key, mapEnum.Current.Value); mapEnum = outputParameters.GetEnumerator(); while (mapEnum.MoveNext()) cmd.Parameters.AddWithValue(mapEnum.Current.Key, mapEnum.Current.Value); foreach (st
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend