How do I declare and use variables in Oracle?

  • My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL:

    declare @startDate int
    select @startDate = 20110501
    

    And I get this error:

    declare @startDate int
    select @startDate = 20110501
    Error at line 1
    ORA-06550: line 1, column 9:
    PLS-00103: Encountered the symbol "@" when expecting one of the following:
    
       begin function package pragma procedure subtype type use
       <an identifier> <a double-quoted delimited-identifier> form
       current cursor
    

    How do I declare and use variables in Oracle?

    oracle is pure pain

  • ik_zelf

    ik_zelf Correct answer

    9 years ago

    Inside pl/sql block:

    declare
     startdate number;
    begin
      select 20110501 into startdate from dual;
    end;
    /
    

    using a bind variable:

    var startdate number;
    begin
      select 20110501 into :startdate from dual;
    end;
    /
    

    PL/SQL procedure successfully completed.

    SQL> print startdate
    
     STARTDATE
    ----------
      20110501
    

    in a query:

    select object_name 
    from user_objects 
    where created > to_date (:startdate,'yyyymmdd');  /*prefix the bind variable wïth ":" */
    

    This unfortunately does not work for me. var my_num NUMBER; BEGIN SELECT 12345 INTO my_num FROM dual; END; / select * from my_table sa where sa.my_col = :my_num;

    what error do you get? (just tested and works)

    I actually tried the solution posted by Jon of All Trades and that worked perfectly for my needs -- i.e. using DEFINE and referencing the variable with &.

  • SQL*Plus supports an additional format:

    DEFINE StartDate = TO_DATE('2016-06-21');
    DEFINE EndDate   = TO_DATE('2016-06-30');
    
    SELECT
        *
    FROM
        MyTable
    WHERE
        DateField BETWEEN &StartDate and &EndDate;
    

    Note the ampersands where the substitutions are to be performed within the query.

    This worked for me in Toad for Oracle when using any of these functions: `Execute as script` or `Execute via Toad script runner` or `Execute via SQL*Plus`. However, if you try running with the `Execute/compile statement at caret` it returns an error message: "ORA-009000: invalid SQL statement".

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM