Call Oracle Stored Procedure With Output Refcursor From Ssis
Nov 6, 2007
I would like to call a oracle stored procedure which looks like this
Create procedure in oracle sql plus
CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY GroupsPackage
AS
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE)
AS
BEGIN
OPEN results_cursor FOR
SELECT GroupID, GroupName, GroupDescription, LastUpdateDate
FROM Groups
ORDER BY GroupName;
END;
END;
/
Execute procedure in oracle sql plus
set autoprint on;
var x refcursor;
groupspackage.usp_selectgroups(:x);
end;
/
This works in sql plus. But when I try to run the same (or tried everything) in the execute statement in ssis using execute sql task, I am not able to make it work. I am getting an error message. Please advice, what am I doing wrong. Or is this possible or is there any other way to get this working.
View 3 Replies
ADVERTISEMENT
Jun 15, 2007
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);
View 10 Replies
View Related
Apr 7, 2006
Hi,
How to call a stored procedure on MS Sqlserver from web forms and get back output
Thanx,
Nishu
View 1 Replies
View Related
Apr 11, 2007
I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DBMicrosoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:
[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.
Any help on this would be most appriciated.
View 3 Replies
View Related
Jul 21, 2006
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
View 29 Replies
View Related
Sep 21, 2015
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 :Â @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 :Â @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 :Â @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutputÂ
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
View 3 Replies
View Related
May 21, 2015
I have ssis package which is credated by VS-2010.
I want execute this SSIS package from the stored procedure (SQL server 2005).
View 3 Replies
View Related
Nov 7, 2015
I want to call "oracle" stored procedure with output parameter from SSIS ole db command task.
Actually I am able to successfully call the procedure but my Output value is not updating in the mapped column.
I used below PL/SQL query.
DECLARE
IS_VALID VARCHAR2(200);
BEGIN
IS_VALID(
PARAM1 => ?,
PARAM2 => ?,
IS_VALID => IS_VALID
);
? := IS_VALID;
END;
If I try to supply "OUTPUT" word I get error:
"ORA-06550: line 1, column 45:
PLS-00103: Encountered the symbol "OUTPUT" when expecting one of the following:Â Â . ( ) , * @ % & = - + < / >"
BEGIN
IS_VALID(
?,
?,
? OUTPUT
);
END;
how to receive output parameter value of oledb command while calling oracle stored procedures.
View 4 Replies
View Related
Mar 27, 2008
I am new to the SSIS.
For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:
dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'
Does anyone know, how do I do the similar thing from SSIS environment.
1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?
Thanks everyone.
View 6 Replies
View Related
Nov 12, 2007
I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ?
When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.
View 9 Replies
View Related
Jun 4, 2015
I have developed an SSIS Package which uses an ODBC connection to an AS400 iseries stored procedure. I use an Execute SQL Task. The query is Call Doctrack.PubFeed(?,?,?,?). The procedure takes 2 input parameters and 2 output parameters (3rd and 4th parameters) The data types of the output parameters are an integer and varchar.  As part of the procedure data is inserted into a table on the iseries.Â
When I run the package using breakpoints to view the values of the variables I see that the stored procedure returns values for the output parameters and the execute SQL task is a success and proceeds to the next task in the package. The whole package ends successfully.However, when the table on the iseries is checked nothing has been inserted into it. To test further, I manually run the procedure on the iseries using the same parameters. The run is successful. And when the table is checked, there are in fact new rows inserted.
What can possibly be the issue since I am not getting any errors when I run the package? Oh I should add that prior to the execute Sql Task, there is a data flow task which moves data from a SQL Server database to a table on the iseries (successfully) using the same ODBC connection. The execute sql tasks uses that information for the Stored procedure.
View 6 Replies
View Related
May 30, 2008
Hi,
I'm using Execute SQL Task to call an Oracle stored procedure. The following is the error that I get.
Error: 0xC002F210 at Validate and Transfer Actuals, Execute SQL Task: Executing the query "{call RS2_RealProject_ETL.TransformLoadAction}
" failed with the following error: "ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TRANSFORMLOADACTION'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
SQL statement syntax: {call RS2_RealProject_ETL.TransformLoadAction}
Per my review of the Forums, I'm entering the SQL syntax correct that is as follow and also use parameter names in Oracle in ordinal method starting from 0.
I have three "input" and one "output" parameter for the stored procedure that they all are of type "NUMBER" in oracle and I've defined them in "Parameter Mapping" window of Excute SQL Task as "NUMERIC".
I'd appreciate your help.
Thanks,
Reza
View 8 Replies
View Related
Feb 21, 2006
Hello,
Is there an oracle provider out there that will let me invoke a parameterless stored procedure that is in a package in my Oracle source?
Better could that stored proc receive a prameter?
Still better, could I use a stored proc in a OLEDB source component and get the resutls from its only out variable (ref cursor) into my SSIS dataflow?
I haven't been able to get any of these basic functionalities working with either the Oracle OLEDB or the Microsoft OLEDB for Oracle provider...
If not, are there any plans to enahnce the MS provider to handle that?
A more tricky question :
Why does the ReportingService data processing extension for Oracle sources allow such things and not the .NET provider in SSIS?
Thanks
View 6 Replies
View Related
Sep 4, 2015
I had the SP, I want to call in Script Task , had the Result set data value then I need pop up message box. So how can I call stored procedure result in message box in ssis script task using C#.
and I want to use SSIS -OLEDB connection.
Â
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
cm = Dts.Connections["OLE_TEST_"];
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
sqlComm = new System.Data.SqlClient.SqlCommand("Exec dbo.sOp_xx_XXXe_VXX 280", sqlConn);
sqlComm.ExecuteNonQuery();
above code , no message box.
View 2 Replies
View Related
Mar 19, 2008
Hi pals,
I am facing problems while calling Oracle stored procedure which has no parameters.
I have used Microsoft OLE DB provider for Oracle.
I have taken one Execute SQL task and set the SQLStatement as
call procedurename
I also set the IsStoredProcedure property to True for Execute SQL task.
Is there any synatx problem?
I am getting an error saying
Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.
I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error
Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name
Can anyone help me out on this regard!
Thanks in advance.
View 2 Replies
View Related
May 29, 2006
Hi,
I figured out a way to execute an Oracle Stored Procedure from an Execute SQL Task by using
Declare
Begin
SomeStoredProc(?,?,?);
End;
with an OLE DB connection using the Oracle Provider for OLE DB.
The parameters are getting passed in and the procedure executes but if for some reason it fails SSIS is painting the task green and keeps processing. I'm guessing that's because the outer Declare/End statement completed sucessfully.
I couldn't get it to work as a function with a return value. :(
Is there another way to execute an Oracle stored procedure that I missed?
Can you call an Oracle stored procedure from a Script Task and then fail it on parameter value?
Thanks
John Colaizzi
View 4 Replies
View Related
Aug 9, 2006
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
View 10 Replies
View Related
Jan 12, 2003
I have been trying to execute Oracle Stored Procedure From SQL Server using Linked Server without luck. The scenario is explained in detail below ..
Environment :
SQL Server Version : 2000
OS : Win NT 4.0 SP5
Oracle Version : 8.0
OS : Win NT 4.0 SP5
User : Scott
Oracle Procedure
Name : TEST_PROC
Parameters: None
The procedure look something like this ...
Create or Replace PROCEDURE TEST_PROC
AS
BEGIN
Insert into Table1 Values('A');
END;
Scenario One
Executing the stored procedure using Linked Server (JDEV), configured Using OLEDB For Oracle, results in the following error
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'TEST_PROC' on remote server 'JDEV'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Scenario One
Executing the stored procedure using Linked Server (SCOTT), Configured Using OLEDB for ODBC, results in the following error
Could not execute procedure 'TEST_PROC' on remote server 'SCOTT'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Could you please help me out with a workaround. In case SQL-DMO is the only workaround, can you provide some sample programs.
The bigger picture is, replicating Execution of a SQL Server Stored Procedure on Oracle.
View 3 Replies
View Related
Sep 10, 2007
I'm calling a procedure on a remote Server (local SQL2005, remote SQL2005) and I need the return value.
Local:
declare @value int execute ('exec mbtest1.dbo.psybcis ?', @value OUTPUT) at [REMOTESQLSERVER] select @value
Remote:
create procedure [dbo].[psybcis] (@value int OUTPUT) as begin select @value = '13' end
I do not get a value in the OUTPUT variable - just NULL. Documentation says:
Execute a pass-through command against a linked server
{ EXEC | EXECUTE } ( { @string_variable | [ N ] 'command_string [ ? ] ' } [ + ...n ] [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ] ) [ AS { LOGIN | USER } = ' name ' ] [ AT linked_server_name ] [;]
There is an OUTPUT parameter, but how does it work? Is my syntax wrong?
It works when using following syntax,
declare @value int exec [REMOTESQLSERVER].mbtest1.dbo.psybcis @value OUTPUT select @value
but I need to use this procedure call in a distributed transaction to a Sybase ASE server and this syntax is not allowed for cross-system-calls.
So first I want to get it work from SQL2005 to SQL2005.
View 2 Replies
View Related
Jan 29, 2008
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
View 6 Replies
View Related
Jul 20, 2005
We have set up Oracle database as a linked server in SQL Server.We are able to access Oracle tables fine.I am trying to call a Oracle stored procedure in SQL Server as follows:declare @p1 varchar(1000)set @p1 = 'HHH'exec GENRET..OPS$GENRET.BOB_TEST_PROC @p1This is the message:Server 'GENRET' is not configured for RPC.Please help.Thanks in advancev
View 1 Replies
View Related
Dec 8, 2006
I am working on an OLAP modeled database.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
View 9 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
Sep 19, 2006
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
View 1 Replies
View Related
Aug 29, 2007
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),)
ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate
)
View 5 Replies
View Related
Mar 31, 2006
I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav
View 2 Replies
View Related
Aug 7, 2001
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks
View 2 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 1 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 2 Replies
View Related
Nov 9, 2005
Greetings,
Even though this may be not right place with this issue I would like to try!
I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.
Many thanks in Advance
View 1 Replies
View Related
Jul 4, 2006
hi,
i created a stored procedure and below is the code segment of it;
create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS
Declare @SQL_INS VarChar(1000)
SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'
Exec (@SQL_INS)
Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
View 5 Replies
View Related
Dec 11, 2007
Hi, i wanna know if we can call a stored procedure from another one. If yes, what's the syntax?
Thanks
View 3 Replies
View Related
Oct 6, 2007
Respected Sir/MAm
i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table.
i want to call this strd procedure in the VC++ main.cpp file....
Could you please help me for the correct syntax code with example.
Thank You.
Upali
View 4 Replies
View Related