CODEWITHSUNDEEP
oracle-databasestored-proceduresprocedure
Arun Kabra
Arun Kabra

Reputation: 9

Stored Procedure Related

I am creating a stored procedure in oracle that is selecting records from login table -

create or replace procedure login_info
    (username IN varchar2, password IN varchar2, result OUT number)
as
begin
    select * from login;
end;

Whenever I am going to compile this it shows an error:

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

What does this mean? I do not understand this.

Upvotes: 0

Views: 59

Answers (2)

ravi chaudhary
ravi chaudhary

Reputation: 625

You have to store the result of your SELECT statement into a variable, you can use sys_refcursor to display the result.

create or replace procedure login_info
    (username IN varchar2, password IN varchar2, result OUT number, result_out OUT SYS_REFCURSOR)
as
l_query varchar2(1000) := Null;

begin
  l_query :=  'select * from login';
open result_out 
for l_query;
end;

above code will give you the output

Upvotes: 2

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

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

That means you need an INTO close when you issue bare SELECT from PL/SQL.

:D

More constructively: where do you think the result of your select would go in that code fragment? 

create or replace procedure login_info
    (username IN varchar2, password IN varchar2, result OUT number)
as
begin
    select * from login;
end;

You have to retrieve it somehow in order to be processed by your PL/SQL code. Assuming you have several rows to collect, you should use BULK COLLECT INTO:

create or replace procedure login_info
    (username IN varchar2, password IN varchar2, result OUT number)
as
    type my_tbl_type IS TABLE OF login%ROWTYPE;
    my_tbl my_tbl_type;
begin
    select * BULK COLLECT INTO my_tbl from login;

    -- do whatever you
    -- need here
    -- on `my_tbl`.
end;

As a final note, maybe are you looking for an explicit CURSOR instead? You should definitively take a look at PL/SQL 101: Working with Cursors. This is an interesting discussion both about SELECT ... INTO ... and CURSOR manipulation.

Upvotes: 0

Related Questions