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 doing the following:
DECLARE COMPID VARCHAR2(20); SELECT companyid INTO COMPID from app where appid='90' and rownum=1;
Why this is not working?
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.
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;
When you are converting from
plsql you have to worry about
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;
no data found then the variable will be
null but no
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;
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;