Set value for an oracle variable and use as parameter

Question

What’s wrong when I try to set a value to an oracle11g variable and use it as parameter ? I found a post with the same doubt and adopted one of the solutions but the error continues:

DECLARE
 ID NUMBER := 1888;
BEGIN     
        SELECT * FROM TB_CLIENTE WHERE   ID_CLIENTE = ID;
END; 

error:

ORA-06550: line 4, column 9: PLS-00428: an INTO clause is expected in this SELECT statement

Answer 1

Hard, when you create an Oracle query, it expects you to put the query return in a variable, otherwise you should declare a cursor.

Your query is correct, but if you intend to loop (while) on it, you actually need to declare a cursor, if you just want to take the data from a single client, then you can do the query like this, but you will be required to declare the variables for each field you want to take.

Your query with return for variables, is something similar to this:

DECLARE
    ID NUMBER := 1888;
    ID_QUERY NUMBER;
BEGIN     
    SELECT ID_CLIENTE INTO ID_QUERY FROM TB_CLIENTE WHERE ID_CLIENTE = ID;
END;

This way, I take the ID_CLIENTE of the query return, and put in the ID_QUERY variable that I declared.

---

As cursor, it is already different, you declare the same together with the variables, and then open it:

DECLARE
    ID NUMBER := 1888;
    ID_QUERY NUMBER;
    CURSOR C_CLIENTE (CLI_ID) IS
           SELECT ID_CLIENTE FROM TB_CLIENTE WHERE ID_CLIENTE = CLI_ID;
BEGIN     
    OPEN C_CLIENTE(ID);
    LOOP
        FETCH C_CLIENTE INTO ID_QUERY;
        EXIT WHEN C_CLIENTE%NOTFOUND
    END LOOP;
    CLOSE C_CLIENTE;
END;

There are other ways to open the cursor, similar to a foreach, something very basic if you want to search more:

--Sendo C o cursor e rec a variável dinâmica criada
FOR rec IN C LOOP  
    minhaVariavel := rec.CampoCursor;
END LOOP;