How To Run Oracle Stored Procedures, Especially With REF CURSOR
May 16, 2008
Hello,
Does SQL server 2005 provide capability to run Oracle stored procedures. I already have a linked server established for Oracle.
I have several oracle stored procedures that :
a) Accept multiple input parameters and return multiple out parameters.
b) Accept multiple input parameters and return a REF CURSOR as out parameter.
If you have any sample code, can you please post it here along with any suggestions. I researched, but there seems to be no solution, especially for REF CURSOR. Much appreciate it.
Thnx
Sam
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
Hi all, I wrote following SP in Oracle but getting Red color with Into symbol at Procedure Name in List of Procs.
create or replace procedure EXAMPLE(:year in char, TYPE in char) AS BEGIN IF :year IS NULL THEN select s.survey_year,st.survey_type_name,count(s.survey_id) as count from tqdb_survey s,TQDB_SURVEYS_TYPE st where s.survey_type_id=st.SURVEY_TYPE_ID and st.survey_type_name=TYPE and s.is_active='N' and s.survey_year>0 group by s.SURVEY_YEAR,st.survey_type_name; ELSE IF TYPE IS NULL THEN select s.survey_year,st.survey_type_name,count(s.survey_id) as count from tqdb_survey s,TQDB_SURVEYS_TYPE st where s.survey_type_id=st.SURVEY_TYPE_ID and s.survey_year=:year and s.is_active='N' and s.survey_year>0 group by s.SURVEY_YEAR, st.survey_type_name; END IF; END EXAMPLE
Hello Everyone, First problem : I have an application which uses Power Builder and Oracle and my job is to convert it so that it can run on a SQL server database. Oracle allows empty strings - '' - to mean NULL. If the application inserts an empty string in a column of a table, Oracle takes it to mean NULL. How can i have this functionality in SQL Server?
Second Problem: SQL Server does not allow stored procedures like sp_addlogin and sp_droplogin etc., to be executed from within a transaction. So everytime i need to execute these stored procedures thru my application, i disconnect and set autocommit to true and connect again. after executing these stored procedures, i disconnect again , set autocommit to false and then connect again. is this how it should be done or is there some better way of doing it.
All help will be highly appreciated. I really need help on these problems - fast. Thanks
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
I need to get recordset returned by oracle sp in execute sql task to process futher in For Each Loop container and on same lines i want to use oracle sp for extraction data in Data Flow Task. Could anybody suggest if it how we could do it in SSIS?
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Please pardon my inexperience, I am new to SSIS. I am having some difficulty with using Oracle
procedures in SSIS. I have installed and configured the Oracle Client Software for 10g. I have managed to
create a connection to the Oracle database that I will be using. I am currently using the Oracle Provider For
OLE DB. I want to add an OLE DB Source component to the Data Flow which I then want to configure to
use an Oracle procedure to bring back the data that I need.
When I want to execute the Oracle Proc in the same way that I would normally execute a SQL Proc it returns
an error saying:
TITLE: Microsoft Visual Studio ------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.".
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
I am trying to generate a report using SSRS ( SQL server 2012 & Visual studio 2013). when I try to execute stored procedures I am getting below error. Below are the details of enviroment.
1. SQl serverr 2012 2.Oracle 12c client 3.Oracle Server 12c 4.Visual studio 2013
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
I have a stored procedure that basically recieves the where clause of a select statement and executes the new sql statement... ie:
CREATE PROCEDURE [dbo].[bsa_GetImportIDs] (@FilterText varchar(1000)) AS
DECLARE @MySQL varchar(1000)
SET @MySQL = "SELECT Import_ID FROM tblImport WHERE " + @FilterText
EXEC (@MySQL) GO
Now, in another stored procedure, I need to use the stored procedure above in a cursor so that I can execute an insert statement for each occurance of the Import_ID that appears in that dataset... ie:
CREATE PROCEDURE [dbo].[bsa_PutLargeCase] AS
DECLARE @CaseID uniqueidentifier SET @CaseID = NewID() Declare @ImportID uniqueidentifier
Declare curClient Cursor FAST_FORWARD for SELECT Import_ID FROM dbo.bsa_GetImportIDs (@FilterText) <---- this does not work!!!
Open curClient FETCH NEXT FROM curClient INTO @ImportID WHILE @@FETCH_STATUS = 0 BEGIN
EXEC dbo.bsa_PutCaseDetail @CaseID, @ImportID
FETCH NEXT FROM curClient INTO @ImportID END
CLOSE curClient DEALLOCATE curClient
GO
How can I utilize my first stored procedure in the cursor of the second? ... or Are there any other approaches that may be a better solution to what I am trying to accomplish?
Hello. I'm having a perfectly(!) normal stored procedure that returns a Resultset with one row (containing an ID I want). Not I need that ID in another stored procedure and I can't get it out from the stored procedure.
exec @blabla = MyProc -- works well if I use return exec MyProc @blabla -- works using OUTPUT keyword
But neither of these examples works with a CURSOR as the @blabla. Do I need to specificly pass a cursor as a return value, wich would give me bellyache, or can I do something like this:
DECLARE @MyCursor CURSOR SET @MyCursor = CURSOR FOR exec MyProc
any of you have an idea how i can declare an output parameter for my cursor which is inside a stored procedure. i would lik to see the output using the exec command but i don't know how to get the out from my cursor. please help!
Hi guys!!I am trying to fill a cursor with the results of a StoredProcedured, but SQL give me an syntax error message , does any one cangive me some helpI am using SQL Server, this is the first lines of my codeDECLARE FRates_Cursor CURSOR FORexec GET_FJRs_Eng 'all'OPEN FRates_Cursorif I run just the exec GET_FJRs_Eng 'all' line it give me the dataresults I am trying to put into the cursor, what that means is thestored is working fineThanks in advance
Has anyone ever tried to use a cursor as an output variable to a stored proc ?
I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc @parentId integer, @outputCursor CURSOR VARYING OUTPUT AS BEGIN TRAN T1 DECLARE parent_cursor CURSOR STATIC FOR SELECT parentTable.childId, parentTable. parentValue FROM parentTable WHERE parentTable.parentId = @parentId OPEN parent_cursor
SET @outputCursor = parent_cursor
DECLARE @childId int DECLARE @parentValue varchar(50) FETCH NEXT FROM parent_cursor INTO @childId, @parentValue WHILE @@FETCH_STATUS = 0 BEGIN SELECT childTable.childValue FROM childTable WHERE childTable.childId = @childId
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue END
CLOSE parent_cursor DEALLOCATE parent_cursor COMMIT TRAN T1 GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.
My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter() da.SelectCommand = New SqlCommand("myStoredProc", conn) da.SelectCommand.CommandType = CommandType.StoredProcedure Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int) paramParentId.Value = 1
Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor") paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?
I have a stored Procedure that is looping through multiple cursors.
It is never finding any records in any curosr that is using a local variable in the where clause...Help
Alter Procedure ProjectedIncome As SET ROWCOUNT 0 Declare -- Date types @startdate smalldatetime ,@enddate smalldatetime ,@ProjectedDate smalldatetime ,@termination smalldatetime ,@effectivedate smalldatetime -- Integer ,@Nums int ,@nums2 int ,@ClientId int ,@AssetId int ,@ProductID int ,@Policies int ,@product int ,@Per int ,@Projected int
-- String ,@debugtext varchar(150) ,@productid2 varchar(15) -- float ,@rate float ,@Cap float --bit ,@Override bit --Money ,@AnnualPremium Money ,@Value Money ,@Premium Money ,@PaymentAmount Money --Doubles ,@PremCalc int ,@HoldPrem int ,@HoldCom int ,@CumBal int ,@CumPrem int ,@MonthlyPrem int ,@XBal int ,@CapPrev int ,@PremTier int ,@Incriment int
--Declare cursor for System Variables DECLARE SystemVar_cur cursor for SELECT ProjectionStartDate,ProjectionEndDate from SystemVariables
--Declare the Cursor for Asset Definitions declare AssetDef_cur cursor for SELECT termination,effectivedate,ClientID,AnnualPremium,A ssetID,ProductID,Policies from AssetDefinitions
--Declare cursor for CommisionDefinitions declare CommisionDef_cur cursor for Select a.product,a.per,a.cap,a.rate,a.value from CommisionDefinitions a where a.product = @ProductId2;
--Declare cursor for projections declare projections_cur cursor for Select a.override,a.premium,a.paymentamount from projections a where a.date = @ProjectedDate and assetid = @AssetId;
-- Select from the SystemVariables Table OPEN SystemVar_cur FETCH SystemVar_cur INTO @startdate,@enddate CLOSE SystemVar_cur DEALLOCATE SystemVar_cur -- Open the AssetDefinition File and loop through -- INSERT INTO debug_table VALUES('Open the Asset Cursor')
Open AssetDef_cur
Fetch AssetDef_cur INTO @termination ,@effectivedate ,@clientId ,@AnnualPremium ,@assetId ,@ProductId ,@Policies
While @@fetch_status = 0 Begin-- begin AssetDefinitions Loop
--If Asset is not Terminated
If @termination IS NULL BEGIN-- begin @termination IS NULL SET @MonthlyPrem = (@AnnualPremium/12) SET @debugtext = 'MonthlyPrem = AnnualPrem' + CAST(@AnnualPremium as Char) + '/12' INSERT INTO debug_table VALUES(@debugtext)
If @effectivedate > @startdate SET @ProjectedDate = @effectivedate Else SET @ProjectedDate = @startdate -- end if SET @PremCalc = 0 SET @CumBal = 0 SET @XBal = 0 SET @HoldCom = 0
-- Fetch the Projection Record open projections_cur
fetch projections_cur INTO @override,@premium,@paymentamount If @@fetch_status = 0 BEGIN IF @override = 1 BEGIN-- begin @override = 1
SET @CumPrem = @premium SET @CumBal = @paymentamount SET @HoldPrem = @CumPrem SET @HoldCom = @CumBal END-- end @override = 1 Else SET @HoldPrem = @MonthlyPrem END CLOSE projections_cur While @ProjectedDate <= @enddate BEGIN-- begin While @ProjectedDate <= @enddate SET @CapPrev = 0 --reset cap balance SET @XBal = 0 SET @debugtext = 'Begin Get Commision Record For Product' + CAST(@productID as CHAR) INSERT INTO debug_table VALUES(@debugtext) SET @productid2 = @productid SET @PremTier = @HoldPrem
---NOW Open the CommisionDef table OPEN CommisionDef_cur
FETCH CommisionDef_cur INTO @product,@per,@cap,@rate,@value IF @@fetch_status <> 0 BEGIN SET @debugtext = 'ERROR? ' + CAST(@@error as Char) INSERT INTO debug_table VALUES(@debugtext) END WHILE @@fetch_status = 0 BEGIN-- begin While CommisionDef Fetch = 0 SET @debugtext = 'Found Commision Record' + CAST(@product as Char) INSERT INTO debug_table VALUES(@debugtext)
If @Per = 0 BEGIN-- begin If @Per = 0
SET @Incriment = @Cap - @CapPrev If @PremTier > @Incriment SET @XBal = @XBal + (@Incriment * @Rate) Else BEGIN-- begin @PremTier > @Incriment If @PremTier >= 0 SET @XBal = @XBal + (@PremTier * @Rate) END-- end @PremTier > @Incriment SET @debugtext = 'XBal ' + CAST(@XBal as CHAR(10)) INSERT INTO debug_table VALUES(@debugtext)
SET @CapPrev = @Cap SET @PremTier = @PremTier - @Incriment END-- end If @Per = 0
Else BEGIN-- begin If @Per <> 0
SET @XBal = @value * @Policies / 12 SET @HoldCom = 0 SET @PremCalc = 0 SET @CumBal = @XBal SET @debugtext = 'CumBal' + CAST(@CumBal as Char) INSERT INTO debug_table VALUES(@debugtext)
SET @HoldPrem = @Policies END-- end If @Per <> 0
FETCH CommisionDef_cur INTO @product,@per,@cap,@rate,@value END-- end While CommisionDef Fetch = 0
CLOSE commisionDef_cur -- Fetch the Projection Record open projections_cur
fetch projections_cur INTO @override,@premium,@paymentamount IF @@fetch_status = 0 BEGIN -- begin Projection Fetch = 0 IF @override = 1 SET @HoldCom = @CumBal ELSE -- If not overridden, set the fields to Update the projection File BEGIN-- begin @override <> 1 SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100 SET @Premium = @HoldPrem - @PremCalc UPDATE projections SET projected = @projected, premium = @Premium where assetid=@AssetID and date = @ProjectedDate SET @HoldCom = @XBal END-- end @override <> 1
END-- end Projection Fetch = 0 ELSE BEGIN -- Begin Projection Fetch else IF @@fetch_status = -1 BEGIN-- begin Projection Fetch = -1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100 SET @Premium = @HoldPrem - @PremCalc SET @debugtext = '((xbal - holdcom)*100 + 0.5)/100 ' + CAST(@Xbal as char) + ' , ' + CAST(@holdcom as CHAR) INSERT INTO debug_table VALUES(@debugtext)
SET @debugtext = 'Projection Record Not Found so Write it' INSERT INTO debug_table VALUES(@debugtext) --Projection record was not found so write it SET @override = 0 INSERT INTO Projections (AssetId,Date,Premium,Projected,Override,Payment,P aymentAmount) VALUES(@AssetId,@ProjectedDate,@Premium,@Projected ,@override,0,0) SET @HoldCom = @XBal END-- end Projection Fetch = -1 END -- end Projection Fetch else
CLOSE projections_cur
SET @ProjectedDate = DateAdd("m", 1, @ProjectedDate) SET @PremCalc = @HoldPrem -- Fetch the Projection Record OPEN projections_cur
FETCH projections_cur INTO @override,@premium,@paymentamount IF @override = 1 BEGIN-- begin @override = 1
SET @CumBal = @paymentamount SET @HoldPrem = @HoldPrem + @CumPrem END -- end @override = 1
ELSE SET @HoldPrem = @HoldPrem + @MonthlyPrem CLOSE projections_cur
END-- End the While ProjectedDate <=@enddate END --End the If Termination is NULL Fetch AssetDef_cur INTO @termination ,@effectivedate ,@clientId ,@AnnualPremium ,@assetId ,@ProductId ,@Policies END CLOSE AssetDef_cur DEALLOCATE AssetDef_cur DEALLOCATE projections_cur DEALLOCATE CommisionDef_cur
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for Select emp_no, emp_name, event_date, Test_no, Code, Test_result From test_table1 order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor.
The doctors table has the following columns with specialism allowing NULL values
doctor ( staff_no CHAR(3), doctor_name CHAR(12), position CHAR(15), specialism CHAR(15), PRIMARY KEY(staff_no) )
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure.
CREATE PROCEDURE STP_EMPSAL @empno int, @Employee_Cursor CURSOR VARYING OUTPUT FOR SELECT empno FROM AdventureworksDW.dbo.emp AS OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor into @empno; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRAN UPDATE emp set sal= sal+ 2000 where empno = @empno and comm is null mgr='Scott'; FETCH NEXT FROM Employee_Cursor into @empno; COMMIT; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor;
Hi guys!i want to create one cursor in the t-sql. the problem is i want to usestored procedure instead of select command in cursor.can anyone tell me how can i use stored procedure's o/p to createcursor?i'm using sql 2000 and .net 2.0thanks,Lucky
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
