1

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.

  • That didn't do it. I added that example to my post. I think it's Oracle's syntax to put the declaration before the begin. Thanks anyway. – user2023861 Sep 15 '14 at 17:08
  • I believe your 'select' statement is confusing people; it appears you simply wanted to echo back the variable contents/value. In that case, the dbms_output.put_line() syntax will work [once the PL/SQL block is evaluated.] – JasonInVegas May 6 '15 at 15:56
  • @JasonInVegas, You're probably right. I submitted an answer below that uses with to simulate variables, and it does more than echo a variable. Are you familiar with PL/SQL? Do you know a better way to write the query in my answer? – user2023861 May 8 '15 at 14:28
3

I think these answers are all getting a) way too complicated, and b) off-track relative to what user2023861 wants to accomplish:

"I am trying to declare a variable, then select that variable." means two different things in either PL/SQL or in SQLplus, and perhaps two more things in DBArtisan. To accomplish the objective in SQLplus, use "Substitution variables"...excellent background is found here.

My example, doesn't precisely follow your SQL Server T-SQL example, but it's close and is NOT procedural (read: no anonymous PL/SQL block.)

PL/SQL with Substitution variables:

DEFINE min_salary = 1000
DEFINE mgrID = 7

select *
from employees
where
    salary > &min_salary
    and mgrID = &mgrID
;

All at the SQL-Plus prompt....from my fictional employees table, I got this result:

    SALARY      MGRID
---------- ----------
      2000          7
      1001          7
2 row selected.

If you want to make this semi-procedural, then you use BIND Variables and the declarative syntax of PL/SQL similar to T-SQL. (In my opinion this is a HUGE waste of effort, but is included to help clarify variable types.)

SQLPlus using PL/SQL Bind variables:

 VARIABLE   v_in_min_salary number;
 VARIABLE   v_mgrID         number;    /* vars are defined within SQL+ */
 VARIABLE   v_out_salary    number;
 VARIABLE   v_out_mgrID     number; 

BEGIN
    :v_in_min_salary    := 1000;   /* vars are assigned values in PL/SQL */ 
    :v_mgrID            := 7; 

 select salary, mgrID 
  into :v_out_salary, :v_out_mgrID 
  from employees 
  where salary > :v_in_min_salary and mgrID = :v_mgrID
 ;

end;
/

PRINT :v_in_min_salary   /* here the in and out vars are displayed  */
PRINT :v_mgrID
PRINT :v_out_salary
PRINT :v_out_mgrID

Fully-procedural approach using PL/SQL:

set serveroutput on;  /* environment setting within SQLplus to see output */
DECLARE
   v_in_min_salary number   := 1000;
   v_mgrID    number        :=7; 
   v_out_salary number;
   v_out_mgrID    number; 

BEGIN
  SELECT salary, mgrID 
    into :v_out_salary, :v_out_mgrID 
    from employees 
    where salary > :v_in_min_salary and mgrID = :v_mgrID
  ;
  DBMS_OUTPUT.PUT_LINE('Salary Output is= '||:v_out_salary);
  DBMS_OUTPUT.PUT_LINE('Manager ID = '||:v_out_mgrID);
END;
/

So, when do I use each case? I use SQLplus Substitution variables 75% of the time when I write SQL scripts that I want to parameterize the input for. Executing a script at the SQLplus prompt allows passing inbound arguments, so my favorite Substitution variables are &1 and &2 as in the following example:

   @get_table_size.sql <schema> <table_name>  
        which in practice becomes 
   @get_table_size jason employees

and executes SQL code similar to

SELECT extents 
  FROM all_tables 
  WHERE UPPER(table_owner) = upper('&1')  /* inbound first argument */
  and UPPER(table_name) = upper('&2')     /* inbound 2nd argument   */
;

Notice how the inbound arguments are assigned into substitution variables, then used in the SQL statement. I never use the middle example, and I always use the 3rd examples (procedural PL/SQL) when writing functions, procs, and packages.

| improve this answer | |
  • --PS I am interested in DBArtisan and the differences it may have when using variables, so I downloaded the free trial and will post any relevant differences once they are uncovered. – JasonInVegas Jun 23 '15 at 17:25
  • I tried your PL/SQL with Substitution variables example and I get an error message that says ORA-01008: not all variables bound. I got the same error message with your other two examples. I don't think the last two examples would work for me if I wanted a result set, like this TSQL one select * from employee where manager_id = @mgrID Maybe it's a DBArtisan thing – user2023861 Jun 23 '15 at 17:48
2

Your first attempt is the correct syntax for an anonymous PL/SQL block

The error message

an INTO clause is expected in this SELECT statement

told you precisely what the problem is.

You can't just "select" inside a stored procedure (or a PL/SQL block) and then have the result magically "returned" from there. This is a fundamental difference between PL/SQL and T-SQL.

If you want a procedure to return a result you either need a REF CURSOR as an out parameter or you need to create a function that returns a result set. But in both cases you can't just call the procedure/function. The caller needs to consume the result somehow if the procedure returns REF CURSOR or explicitly use it instead of a table, e.g. select * from table(my_function());

| improve this answer | |
  • When I use a REF CURSOR or create a function, is this something I will have to delete when I'm done with it? Or does it go out of scope? And do you have a simple example? I'm having trouble finding one simple example script that contains all of these ideas succinctly. – user2023861 Sep 15 '14 at 18:08
2

Here's an option I put together that both gets the job done and avoids ugly boilerplate code:

with vars as
(
    select 
        1000  v_min,
        7     v_mgrID
    from dual
)

select *
from employees
where 
    salary > (select v_min from vars)
    and mgrID = (select v_mgrID from vars)

This may not be the most performant code, but it's easy to understand. I have found that the documentation for PL/SQL in regards to reading from and writing to variables extremely poor. I'd be happy to be proven wrong, but I can't find a simple example of PL/SQL code that contains the same concepts exhibited in the following TSQL code:

declare @min_salary int = 1000
declare @mgrID int = 7

select *
from employees
where
    salary > @min_salary
    and mgrID = @mgrID

With this code, I don't need to know about cursors, or loops, or code blocks.

| improve this answer | |
  • Which SQL client are you using? In the Oracle world such a query parameter is usually handled by the SQL client (e.g. by using &v_min in SQL*Plus. See the manual for examples: docs.oracle.com/cd/E11882_01/server.112/e16604/… I believe the same works in SQL Developer. Other clients might have other ways of doing that. – a_horse_with_no_name Sep 15 '14 at 20:09
  • DBArtisan 9.6.1 – user2023861 Sep 15 '14 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.