Oracle – Variable Assignment and Usage in PL/SQL

oracleplsql

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.

Best Answer

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.

Related Question