I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables. I'm trying to declare a variable and then select that variable. It sounds simple, but I am doing something wrong. Here's my code:
declare num number; begin num := 64; select num from dual; --error here end; /
This gives me an error message that says: "PLS-00428: an INTO clause is expected in this SELECT statement".
I tried a few other permutations of this but each gives me an error:
declare num number; begin num := 64; end; select num from dual; --"num" invalid identifier /
And this one also gives me an error.
declare num number; begin num := 64; end; / select num from dual; --"num" invalid identifier
Based on Mihai's comment, I tried putting the declare after the begin, but that threw errors as well:
begin declare num number; num := 64; --encountered symbol "=" when expecting ... end; / select num from dual; --"num" invalid identifier
For reference: here's how I would do this in SQL Server. It may not be clear, but the scope of the @num variable below is the file containing the declaration. This variable is not global.
declare @num int = 64 select @num
I am doing this is DBArtisan. Note that this question is similar to this one here, but I'm also asking how to use the variable. And I'm not interested in bind variables (I don't think). I want to write one-off queries like this:
select * from employee where manager_id = mgrID with the mgrID being a variable that I declare.