August 4, 2008 at 6:26 am
Hi,
Currently we are shifted on Oracle from MsSQL 2005. I want to convert a stored procedure which will take dynamic query in string format and execute it. Here is MsSQL equivalant:
CREATE Proc [dbo].[GetCars]
@YearOfManf Int,
@PriceFrom Decimal,
@PriceTo Decimal
As
Begin
Declare @sql nVarchar(2000)
set @sql=''
If(@YearOfManf is not null)
set @sql=@sql+' And ManufactureYear='+Convert(Varchar(4),@YearOfManf)+''
If(@PriceFrom Is Not Null and @PriceTo Is Not Null)
set @sql=@sql+' And ExpectedPrice Between '+Convert(Varchar(10),@PriceFrom)+' And '+Convert(Varchar(10),@PriceTo)+''
IF(@PriceFrom Is Not Null and @PriceTo Is Null)
SET @sql=@sql+' And ExpectedPrice >= '+Convert(Varchar(10),@PriceFrom)+''
set @sql='SELECT
CarID,
ManufactureYear
ExpectedPrice
FROM
CarMaster
WHERE Visible = 1 AND Deleted = 0 '
+ @sql +'
GROUP BY
ID, ManufactureYear, ExpectedPrice'
exec sp_executesql @sql
End
Is anybody knw how to convert this line: exec sp_executesql @sql
Thanx in advance...
-Abhijit
August 4, 2008 at 11:14 pm
You can use DBMS_SQL package in this case.
http://download-uk.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 5, 2008 at 12:48 am
You won't get along with just changing this row. First thing - this priocedure simply won't work because syntax is different. Second thing - as you will be using one or another kind of DYNAMIC SQL, it is quite easy to use variables as literals and do the things wrong way in Oracle. Especially if this will be for a busy site. If not using bind variables with dynamic SQL you'll be flooding shared pool with thousands of almost the same statements differing just in variable values.
For the right way how to do that you can see this asktom link.
It contains also many discussions what is good, what not and why.
Gints Plivna
Two remarks
- somehow tinyurl doesn't work for me if clicked only once. But it works when I refresh the page.
- original link is as follows (without space before P):
http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::: P11_QUESTION_ID:1288401763279
Gints Plivna
http://www.gplivna.eu
August 6, 2008 at 1:30 am
Support Gints.
By the way, you do not "convert" storedprocs from SQL Server to Oracle, you learn what the storedproc is supposed to do and you rewrite it from scratch.
The process is more kind of a "translation" in between two languages; the "translator" has to be fluent on both of them.
Sooner or later you are going to learn that's the easiest and more cost effective way of doing it.
Been there, done that. Over 600 storedprocs in a single project.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 7, 2008 at 7:19 pm
When you re-write your procedure use "execute immediate" and not package dbms_sql. This package is very powerful, but syntactically a nightmare.
Also, you cannot issue a select statement inside a procedure. You need to select into a container, which can be single variable, record or collection of variables/records.
One more thing, "select col into var from table" in Oracle is the same as select var = col from table in Sql Server.
You really need to learn the pl/sql syntax, which is quite different from T-SQL once you progress beyond simple sql statements.
Cheers,
Win
August 8, 2008 at 1:26 am
win (8/7/2008)
Also, you cannot issue a select statement inside a procedure. You need to select into a container, which can be single variable, record or collection of variables/records.
Or ref cursor, which is the most performant way to pass data (more correctly say - handle to data) along. Collections of scalars and even more collections of records generally need much memory, but with ref cursors client can operate and fetch only as many rows as necessary. Also client can fetch 1 (bad idea) or n (for example 100) rows each time. But database doesn't need to collect all let's say 10K rows, collect them into a collection and pass along all this big bit bucket.
Gints Plivna
Gints Plivna
http://www.gplivna.eu
August 8, 2008 at 3:05 am
Thank you very much to all...
I got the desired solution, of cource using replies in this forum.
I use 'ref cursor' and 'execute immediate' in my sp.
Thanks again...
-Abhijit
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply