[oracle] Declaring a variable and setting its value from a SELECT query in Oracle

In SQL Server we can use this:

DECLARE @variable INT;
SELECT @variable= mycolumn from myTable;

How can I do the same in Oracle? I'm currently attempting the following:

DECLARE COMPID VARCHAR2(20);
SELECT companyid INTO COMPID from app where appid='90' and rownum=1;

Why this is not working?

For storing a single row output into a variable from the select into query :

declare v_username varchare(20); SELECT username into v_username FROM users WHERE user_id = '7';

this will store the value of a single record into the variable v_username.

For storing multiple rows output into a variable from the select into query :

you have to use listagg function. listagg concatenate the resultant rows of a coloumn into a single coloumn and also to differentiate them you can use a special symbol. use the query as below SELECT listagg(username || ',' ) within group (order by username) into v_username FROM users;

Not entirely sure what you are after but in PL/SQL you would simply

DECLARE
  v_variable INTEGER;
BEGIN
  SELECT mycolumn
    INTO v_variable
    FROM myTable;
END;

Ollie.

ORA-01422: exact fetch returns more than requested number of rows

if you don't specify the exact record by using where condition, you will get the above exception

DECLARE
     ID NUMBER;
BEGIN
     select eid into id from employee where salary=26500;
     DBMS_OUTPUT.PUT_LINE(ID);
END;

One Additional point:

When you are converting from tsql to plsql you have to worry about no_data_found exception

DECLARE
   v_var NUMBER;
BEGIN
   SELECT clmn INTO v_var FROM tbl;
Exception when no_data_found then v_var := null; --what ever handle the exception.
END;

In tsql if no data found then the variable will be null but no exception