0

Why does this fail in SQL Developer against an Oracle database and what would the an appropriate way of achieving the same?

DECLARE v_pNbr NUMBER := 100150; 
begin
     select * from TARGETTABLE where PROCESSNBR = v_pNbr;
end;

ORA-06550: line 3, column 2: PLS-00428: an INTO clause is expected in this SELECT statement


Coming from SQL Server I have gotten used to creating variables in SSMS query editor/page and then running them across multiple selects. Hence it allows one to understand the operations with a change to one instead of many. The examples I have seen seem to hint a historical terminal usage instead of editor which may preclude such a way of working.... Ergo what is accepted way of working with pl/sql as such.

  • What are you trying to do? FYI - Oracle doesn't "return data sets" like that. – Michael Kutz Sep 19 '18 at 15:47
1

You do not need PL/SQL for that, you can use plain SQL after initializing a bind variable, for example.

variable v_pNBr number;
exec :v_pNBr := 100150;

select * from TARGETTABLE where PROCESSNBR = :v_pNbr;

Or you can simply use:

select * from TARGETTABLE where PROCESSNBR = :v_pNbr;

And SQL Developer will prompt for the value of the variable.

PL/SQL code is executed on the database server and it does not display a resultset on the client. Yes, PL/SQL code can store results in an intermediate memory area on the server, then the client is responsible for getting and displaying that content. Another way is opening a cursor in PL/SQL and returning that to the client. Or you can create a PL/SQL table function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.