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?

Solution 1:

SELECT INTO

DECLARE
   the_variable NUMBER;

BEGIN
   SELECT my_column INTO the_variable FROM my_table;
END;

Make sure that the query only returns a single row:

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row

If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of an aggregate function, such as COUNT(*) or AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition.

A SELECT ... BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.

The implicit cursor SQL and its attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN provide information about the execution of a SELECT INTO statement.

Solution 2:

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.

Solution 3:

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