SELECT statement in Oracle and SQL Server

Question

I have a SQL Server script like

declare @num int
begin
--//........while loops/ if conditions...
--//........remove code for simplicity
select num from tbl where cnd = @num;
end

I have done this in Oracle;

Declare v_num number;
begin
--//........while loops/ if conditions...
--//........remove code for simplicity
select num from tbl where cnd = v_num;
end;
/

I am getting an error

PLS-00428: an INTO clause is expected in this SELECT statement

The whole sql server query is about 500 line which is having may variables declared and assigned.... At the end of the script there is a select statement using many of those variables and multiple tables. How could I get that select statement work in Oracle?

We would actually need more code to understand what you want. Do you want to assign num to v_num ? Is v_num filled by another part of the code ? What is @num ? — A Hocevar
– A Hocevar, Commented Aug 7, 2015 at 13:01
The whole sql query is about 500 line which is having may variables declared and assigned.... At the end of the script there is a select statement using many of those variables and multiple tables. How could i get that select statement work in Oracle. — Praveen
– Praveen, Commented Aug 7, 2015 at 13:04
Did you get a blessing from the woodoo prist? How can we know what is wrong if you don't show thr Relevant code? — Zohar Peled
– Zohar Peled, Commented Aug 7, 2015 at 13:05
In your Oracle code you declare a variable v_num but use a variable @num. Is this a typo? I dont know oracle enough, but it is possible that the a select in pl/sql must select into a cursor or another table. At least the error message suggests that. — Peter Paul Kiefer
– Peter Paul Kiefer, Commented Aug 7, 2015 at 13:08

HAYMbl4 · Accepted Answer · 2015-08-07 13:09:17Z

1

If you want use v_num from decalre block you should rewrite your code follows:

Declare v_num number;
begin
--//........while loops/ if conditions...
--//........remove code for simplicity
select num from tbl where cnd = v_num ;
end;
/

If you use follows:

Declare v_num number;
begin
--//........while loops/ if conditions...
--//........remove code for simplicity
select num from tbl where cnd = :v_num ;
end;
/

you must insert value into parametr v_num

if you want select value from num to v_num you should rewrite you code follows:

Declare v_num number;
begin
--//........while loops/ if conditions...
--//........remove code for simplicity
select num into v_num from tbl;
end;
/

answered Aug 7, 2015 at 13:09

HAYMbl4

1,4802 gold badges15 silver badges29 bronze badges

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

Comments

Madhivanan · Accepted Answer · 2015-08-07 13:03:28Z

0

It should be

Declare v_num number;
begin
--//........while loops/ if conditions...
--//........remove code for simplicity
select num from tbl where cnd = :v_num;
end;
/

answered Aug 7, 2015 at 13:03

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

1 Comment

Praveen Over a year ago

Even if i do select num from tbl I am getting the error PLS-00428: an INTO clause is expected in this SELECT statement

Collectives™ on Stack Overflow

SELECT statement in Oracle and SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related