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

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

Calling Oracle stored procedure

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

I am calling oracle procedure in SSIS script task, This stored procedure retrun value as output, I tried OLEDB it not working, Now i am trying using ADO.net connection, while executing task i am getting following error msg


[Execute SQL Task] Error: Executing the query "exec ssisproctes" failed with the following error: "Unable to cast object of type 'System.Data.OracleClient.OracleConnection' to type 'System.Data.SqlClient.SqlConnection'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am able to test connection in connection manager?

any help to resolve this issue, or is there any other method to call oracle procedure and get output value in a SSIS variable




View Complete Post

More Related Resource Links

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



Here is my SP:

create or replace
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
WHERE substr(UserId,1,2) <> vUid
Order By UserId;

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)? 


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.



Calling Stored Procedure in asp.net

Hi! I am trying to insert a row via button event that should call an Oracle Stored Procedure, but the insert never happens. Please see code below: C#-code {code} using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.OracleClient; using System.Data;   public partial class new_user : System.Web.UI.Page {     protected void button1_click(object sender, EventArgs e)     {         OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();           b.UserID = "Test";         b.Password = "xxx";         b.DataSource = "ora11";           OracleConnection con = new OracleConnection(b.ToString());           try         {             con.Open();             OracleCommand spcmd = new OracleCommand();               spcmd.CommandType = CommandType.StoredProcedure;     &nbs

Calling another Stored Procedure within a stored procedure

I want to call a stored procedure within my main stored procedure to return a value. Here is the code snippet:   EXECUTE @ClaimNumber =  [dbo].[uspGetNextNumber] 'BRP_INT' , 'ClaimHeader', 'ClaimNumber', 'dlivelsb' When I run uspGetNextNumber by itself thru debugger, the proc returns the value. When I run the proc inside my stored procedure, it returns zero. Any thoughts on why this is not working?

Calling AS400 Stored Procedure from ASP.net C# Coding

Hi, I have a req in which I have to call an AS400 stored procedure.The stored has an input and output parameter. Can any body help me.I am new to this technology. Please help me its urgent Thanks, A2H  

Calling Oracle procedure from SSIS 'Execute SQl task' is not working


Hi ,


Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine

Now the procedure is changed to Oracle.So I have changed it to "BEGIN  PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN  PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing  'execute BEGIN  PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.

Please help me.. thanks in advance




calling stored procedure with in select statement



        Any one pls tell me how to call the stored procedure in SQL server

Problem with Oracle Stored Procedure/Web Form(C#)



I receive the following message when trying to insert a record into a table via Web Form(C#):

Error: Invalid operation. Connection closed.

The button event, when clicked, should connect to the database and then call the stored procedure and
insert a new record according to the text input on the Web Form. Hence, returning the new flight id (TID).

Note: Some entries can be null.



<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Add_Flight.aspx.cs" Inherits="Add_Flight" %>   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">   <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { width: 89%; } .style2 { height: 23px; } .style3 { width: 360px; } .style4 { width: 137px; } .style5 { height: 23px; width: 137px; } .style6 { width: 95px; } .style7 { height: 23px; width: 95px; } .style8 { width: 200px; } .style9 { width: 129px; } </style> </head> <body> <form id="form1" runat="server"> <div> <table cellspacing="3" class="styl

JDBC - calling stored procedure always returns 1 result set


I am running the following against Northwind on Sql Server 2005. It always returns a result set of 1 row regardless of the parameters passed:

Connection conn;
// not showing conn creation

CallableStatement callStmt = conn.prepareCall("{ call [Sales by Year] (?,?) });
callStmt.setDate(1, new Date (long_Jan_1_1996));
callStmt.setDate(2, new Date (long_Jan_1_1997));
ResultSet rs = callStmt.executeQuery();

Why do I only get 1 row of results back?

thanks - dave

Very funny video - What's your Metaphor?

Calling a stored procedure or function from another stored procedure


Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)


Finished running [dbo].[DeleteSetByTime].

This is my function:






RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:




Selecting Oracle Stored Procedure in SSRS Crashes Visual Studio 2005 IDE


Problem Conditions

I have a very simple Oracle (11g) Stored Procedure that is declared like so:

* FROM MyTable;

This compiles correctly. The cursor contains col1, col2 and col3.

In SSRS, i have a Shared Data Source that uses Oracle OLEDB Provider for Oracle 11g:


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);  


Getting multiple rows from oracle stored procedure and binding it to grid view



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;

Now create a stored procedure to return multiple rows .

           create or replace

           PROCEDURE getAllCity(p_recordset OUT types.cursor_type) AS


           OPEN p_recordset FOR


Oracle stored procedure using parameters


Hi, am having a problem sending parameters to the stored procedure, I'm not sure if i'm sending Oracle the parameters in the right way.

USING:oracle 11g x64, C#, ASP.net 4.0, ODP.NET


The header of the stored procedure on Oracle server is:


?PROCEDURE create_new_user(p_log_in_name IN VARCHAR2, p_password_text IN VARCHAR2, isSuccessful OUT NUMBER)


The code the use the stored procedure in C# asp.net:

       OracleConnection connect = new OracleConnection("User Id=xpcal;Password=password123;Data Source=ora11g;");
        OracleCommand command_create_user = new OracleCommand();

        command_create_user.Connection = connect;

        command_create_user.CommandText = "xpcal.xpcal_users.create_new_user";
        command_create_user.CommandType = CommandType.StoredProcedure;

        OracleParameter p_log_in_name1 = new OracleParameter();
        p_log_in_name1.OracleDbType = OracleDbType.Varchar2;
        p_log_in_name1.Direction = ParameterDirection.Input;
        p_log_in_name1.Value = tbox_user.Text;

        OracleParameter p_password = new OracleParameter();
        p_log_in_name1.OracleDbType = OracleDbType.Varchar2;
        p_log_in_name1.Direction = ParameterDirection.Input;
        p_log_in_name1.Value = tbo

TableAdapter/DataSet calling Stored Procedure



I am able to get data with an TableAdapter using OracleClient (ODP.NET) and typing SQL statements.

Now I want to just call a stored procedure from the database instead of calling a command like "select * from table".  But I cant choose "Use existing stored procedures", just "Use SQL statements" in the TableAdapter Configuration Wizard. So how to call a procedure?

c# code to create an array of parameters for oracle stored procedure



Working on a c# project that has oracle as backend.  I have problem creating a function in my codebehind page to create an array of paramenters for oracle stored procedure. for example i have the follwoing code for ms sql server...

SqlParameter[] parm = 


                         DataCon.createSqlParameter ("@id", DataCon.DBNullIfBlank(txtid.Text.ToString()), SqlDbType.Char,9 ) ,

                         DataCon.createSqlParameter ("@FName", DataCon.DBNullIfBlank(txtFName.Text.ToString()), SqlDbType.VarChar,14),

                         DataCon.createSqlParameter ("@MI", DataCon.DBNullIfBlank(txtMI.Text.ToString()), SqlDbType.Char,1),


 For the same in oracle I am using the followin

Calling stored procedure from funciton in sql server 2005


Is there any way to call stored procedure from funciton in sql server 2005?

If it is possible, please give some examples.



RAHUL Dot Net Developer

Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.
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