Simple use of variable with select * in Oracle

Question

I know MSSQL very well. But when trying some basic things of SQL in Oracle I am getting in trouble. Tried to search on other sites but didn't found proper + simple example. Suppose I have SQL statements as follows

Declare @mID int = 45
Select * from MyTable where id= @mID

I tried to write same code in Oracle (SQL Developer). But it didn't worked.

Declare 
mID number := 45
Begin
Select * from MyTable where id= mID
End;

Actually, I tried some more statements but didn't executed. All I got was ERRORS. Can anyone come up with simple & correct solution?

In what sense did it not work? Does it not give the expected output or is there an error message? — SouravA
– SouravA, Commented Jan 15, 2015 at 11:03
You can't execute simple SELECT in PL/SQL. You need to put it in some record- or table-type variable. — yamny
– yamny, Commented Jan 15, 2015 at 11:05
You are missing a ; after the end statement. - If that still won't work, try to add a : before the second occurrence of variable mID to expand it. — ShellFish
– ShellFish, Commented Jan 15, 2015 at 11:07

user330315 · Accepted Answer · 2015-01-15 11:26:25Z

1

Oracle does not have variables in SQL, only in PL/SQL. And unlike SQL Server where everything is "T-SQL", Oracle makes a clear distinction between SQL and (procedural) PL/SQL.

Variables in SQL statements must be done (supported) by the SQL Client, not the server.

SQL Developer supports (client side) variables the same way as SQL*Plus does it.

define mid = 45
Select * 
from MyTable 
where id= &mid;

The DEFINE command is documented here:
https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve017.htm#i2697507

(I don't usually use SQL Developer so I don't know if this is also documented in the manual there)

answered Jan 15, 2015 at 11:26

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

SouravA Over a year ago

This is exactly what I was looking for in the comment to the previous answer. I am interested to read more on this. Can you point me to some good references please?

user330315 Over a year ago

Check the link to the SQL*Plus manual. SQL Developer supports most of it and check the SQL Developer manual: docs.oracle.com/cd/E11882_01/doc.112/e12152/intro.htm#CHDDHAAI

SouravA Over a year ago

Thanks. But I am actually interested to learn in depth on what your answer just said.

Variables in SQL statements must be done (supported) by the SQL Client, not the server.  SQL Developer supports (client side) variables the same way as SQL*Plus does it.

user330315 Over a year ago

Variables in SQL*Plus are explained in the SQL*Plus manual.

Sanders the Softwarer · Accepted Answer · 2015-01-15 11:15:08Z

0

You don't know SQL, you know MS SQL :)

Simple & correct solution depends of what you want to get. Do you wish return recordset to client? Or maybe use it in next server statements? Or something else?

MSSQL does not supports (or maybe didn't support in past) query parameters, so many years it would be the way to make parametrized queries in MSSQL - declaring variable and using them into query. Oracle supports parametrized queries, so in this case you should just do it directly:

SELECT * FROM YourTable WHERE id = :id

Client uses this technique something like this:

with TOraQuery.Create(nil) do
try
  SQL.Text := 'SELECT * FROM YourTable WHERE id = :id';
  ParamByName('id').AsInteger := 45;
  Open; 
  ...
  ParamByName('id').AsInteger := 46;
  Open; 
  ...
  ParamByName('id').AsInteger := 47;
  Open; 
finally
  Free;
end;

Oracle didn't supports session variables instead, so MSSQL-like way isn't applicable here. Closest way to do this into Oracle is

declare
  p_id YourTable.ID%type := 45;
begin
  open :result for select * from YourTable where id = p_id;
end;

edited Jan 15, 2015 at 11:15

answered Jan 15, 2015 at 11:10

Sanders the Softwarer

2,5061 gold badge15 silver badges29 bronze badges

5 Comments

AK47 Over a year ago

Means instead of :id, I should put value? like SELECT * FROM YourTable WHERE id = 45. Is it what you are saying?

Sanders the Softwarer Over a year ago

No, you should not put value, you should set this value. I'll write example.

SouravA Over a year ago

@SanderstheSoftwarer - This is my take on this. I do not work on Oracle, but I do know that one of the main difference between Oracle and SQL Server is that to work on Oracle, you need to know PL-SQL. Unlike SQL, where we can just declare variables and use them, PL-SQL is a programming language and hence, you can't just use variables. In PL-SQL the safest way to use variables is to use programming objects, like a stored proc.

SouravA Over a year ago

(continued) - Once I heard that SQL Server is flexible in this aspect, that you can freely use variables in a new SQL window and very similarly could use them in stored proc. But if you are working with Oracle, using them in a new window is not the same as using them in a proc. I am probably not making sense because this is something I am trying to recall, but please let me know if this does make any sense. Thanks.

Sanders the Softwarer Over a year ago

It's too big question for comments. Shortly, MSSQL has a single scripting language named T-SQL which supports SQL dialect and some other feautures. There is not way to access server beyond T-SQL. Oracle has SQL dialect which can be called either directly or via programming language named PL/SQL, but have no server-supported scripting capabilities.

Collectives™ on Stack Overflow

Simple use of variable with select * in Oracle

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related