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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to oracle

Examples related to plsql