SQL Server to Oracle SQL Developer Query

Question

I have this script written in SQL Server 2008 and need to convert it to Oracle SQL this way I can run it in Oracle SQL Developer. Would anyone know the correct syntax? The main things here I would like to know are the following: 1) Declaring local variables 2) Setting local variables 3) Use the local variables 4) Using dates. Thanks very much for your help in advanced.

DECLARE @Name VARCHAR(55)
DECLARE @Age INT
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @NAME = 'Tim'
SET @Age = 55
SET @StartDate = '5/10/1999'
SET @EndDate = '9/22/2008'

SELECT *
FROM TblIdentifier ident
WHERE Name = @Name AND 
      Age = @Age AND 
      Birthday BETWEEN @StartDate AND @EndDate 
ORDER BY ident.ID

score 0 · Accepted Answer · answered Oct 18 '13 at 00:40

0

Yes. You should consider looking at SQL Plus instead for the scripting. Follow the link in the comment above. There is a way to kind of fake what you want, so you can use it in SQL Developer. Here is a simple example

WITH
konstants AS
(
    SELECT 'Tim' NAME,
           55    AGE
    FROM DUAL
)
SELECT *
FROM TblIdentifier 
WHERE NAME = (SELECT NAME FROM konstants) 
  AND AGE  = (SELECT AGE  FROM konstants)

This puts the constants at the top of the query, so they are in only one place, and it is easy to find and change when you need to.

answered Oct 18 '13 at 00:40

EvilTeach

28,120
21
85
141

Would dates work the same way? – Bob Bionella Oct 18 '13 at 00:48
Yes, you can do that. It is a little trickier. '10/10/2013' is a string not a date. You would need to use the built in TO_DATE function to turn the string into a date. – EvilTeach Oct 18 '13 at 01:00
Would you be able to add that to your example so I have a better idea? Thank you for your help! I really appreciate it. – Bob Bionella Oct 18 '13 at 01:07
It is left as an exercise for the reader. – EvilTeach Oct 18 '13 at 17:53

SQL Server to Oracle SQL Developer Query

1 Answers1