How do I use variables in Oracle SQL Developer

Question

Below is an example of using variables in SQL Server 2000   DECLARE  EmpIDVar INT  SET  EmpIDVar   1234  SELECT   FROM Employees WHERE EmployeeID    EmpIDVar   I want to do the exact same thing in Oracle using SQL Developer without additional complexity   It seems like a very simple thing to do  but I can t find a simple solution   How can I do it

User · Answer

You can read up elsewhere on substitution variables; they're quite handy in SQL Developer. But I have fits trying to use bind variables in SQL Developer. This is what I do:

SET SERVEROUTPUT ON
declare
  v_testnum number;
  v_teststring varchar2(1000);

begin
   v_testnum := 2;
   DBMS_OUTPUT.put_line('v_testnum is now ' || v_testnum);

   SELECT 36,'hello world'
   INTO v_testnum, v_teststring
   from dual;

   DBMS_OUTPUT.put_line('v_testnum is now ' || v_testnum);
   DBMS_OUTPUT.put_line('v_teststring is ' || v_teststring);
end;

SET SERVEROUTPUT ON makes it so text can be printed to the script output console.

I believe what we're doing here is officially called PL/SQL. We have left the pure SQL land and are using a different engine in Oracle. You see the SELECT above? In PL/SQL you always have to SELECT ... INTO either variable or a refcursor. You can't just SELECT and return a result set in PL/SQL.

User · Answer

Use the next query   DECLARE    EmpIDVar INT   BEGIN   EmpIDVar    1234     SELECT     FROM Employees   WHERE EmployeeID   EmpIDVar  END

User · Answer

Simple answer NO   However you can achieve something similar by running the following version using bind variables   SELECT   FROM Employees WHERE EmployeeID    EmpIDVar    Once you run the query above in SQL Developer you will be prompted to enter value for the bind variable EmployeeID

User · Answer

I am using the SQL-Developer in Version 3 2  The other stuff didn t work for me  but this did   define value1    sysdate   SELECT  amp  amp value1 from dual    Also it s the slickest way presented here  yet    If you omit the  define -part you ll be prompted for that value

User · Answer

Ok I know this a bit of a hack but this is a way to use a variable in a simple query  not a script   WITH     emplVar AS      SELECT 1234 AS id FROM dual  SELECT       FROM     employees      emplVar WHERE     EmployId emplVar id    You get to run it everywhere

User · Answer

Try this it will work  it s better create a procedure  if procedure is not possible you can use this script   with param AS  SELECT 1234 empid FROM dual   SELECT     FROM Employees  param   WHERE EmployeeID   param empid  END

User · Answer

In SQL Plus  you can do something very similar  SQL gt  variable v emp id number  SQL gt  select 1234 into  v emp id from dual         1234 ----------       1234  SQL gt  select     2    from emp   3   where empno    v emp id   no rows selected   In SQL Developer  if you run a statement that has any number of bind variables  prefixed with a colon   you ll be prompted to enter values   As Alex points out  you can also do something similar using the  Run Script  function  F5  with the alternate EXEC syntax Alex suggests does   variable v count number  variable v emp id number  exec  v emp id    1234  exec select count 1  into  v count from emp  select     from emp  where empno    v emp id exec print  v count

User · Answer

I think that the Easiest way in your case is    DEFINE EmpIDVar   1234   SELECT   FROM Employees WHERE EmployeeID    amp EmpIDVar   For the string values it will be like    DEFINE EmpIDVar    1234    SELECT   FROM Employees WHERE EmployeeID     amp EmpIDVar

User · Answer

In sql developer define properties by default  ON   If it is  OFF  any case  use below steps     set define on  define batchNo  123   update TABLE NAME SET IND1    Y   IND2    Y  WHERE BATCH NO     amp batchNo

User · Answer

There are two types of variable in SQL-plus  substitution and bind   This is substitution  substitution variables can replace SQL Plus command options or other hard-coded text    define a   1  select  amp a from dual  undefine a    This is bind  bind variables store data values for SQL and PL SQL statements executed in the RDBMS  they can hold single values or complete result sets    var x number  exec  x    10  select  x from dual  exec select count    into  x from dual  exec print x    SQL Developer supports substitution variables  but when you execute a query with bind  var syntax you are prompted for the binding  in a dialog box    Reference    http   www oracle com technetwork testcontent sub-var-087723 html SQL Plus Substitution Variables  Christopher Jones  2004   UPDATE substitution variables are a bit tricky to use  look   define phone     38097666666   select  amp phone from dual  -- plus is stripped as it is a number select   amp phone  from dual  -- plus is preserved as it is a string

[sql] How do I use variables in Oracle SQL Developer?

Examples related to sql

Examples related to oracle

Examples related to variables

Examples related to declare