3 Replies Latest reply on Sep 1, 2016 7:42 PM by thesqlguyatl

    Query Variables in SQL Developer

    thesqlguyatl

      What is the proper way to use variables with query-based values in a query in SQL Developer?  Here is what I want to do:

       

      1. declare variable, e.g., v_date
      2. assign variable value based on query, e.g., (select last_day(sysdate) from dual)
      3. use variable in query

       

      I have been a long-time Toad user and the method appears to be different from what I am use to.  I have tried to find this info online and tried several variations, but for some reason I am having trouble.  Any help is appreciated.

        • 1. Re: Query Variables in SQL Developer
          thatJeffSmith-Oracle

          SQL*Plus rules apply.

           

          In this case, substitution variables.

           

          bind_var.png

           

           

          SQL> column LAST_NAME NEW_VALUE LOCAL_NAME

          SQL> select LAST_NAME from EMPLOYEES FETCH first 1 rows only;

           

           

          LAST_NAME              

          -------------------------

          Abel                    

           

           

          SQL> DEFINE LOCAL_NAME

          DEFINE LOCAL_NAME =  "Abel" (CHAR)

          SQL> select '&local_name' from dual;

          old:select '&local_name' from dual

          new:select 'Abel' from dual

           

           

          'ABE

          ----

          Abel

          • 2. Re: Query Variables in SQL Developer
            Gary Graham-Oracle

            To get an idea of how you might approach this via scripting in the worksheet, the following code...

            var nvar number;

            exec :nvar := 10;

            select :nvar from dual;

            exec select count(*) into :nvar from dual;

            print :nvar;

            produces this when executed via F9 (Run Script)...

            SQL> var nvar number;

            SQL> exec :nvar := 10;

             

            PL/SQL procedure successfully completed.

             

            SQL> select :nvar from dual;

             

                 :NVAR

            ----------

                    10

             

            SQL> exec select count(*) into :nvar from dual;

             

            PL/SQL procedure successfully completed.

             

            SQL> print :nvar;

             

                  NVAR

            ----------

                     1

            The problem with this scripting approach in the worksheet in general, however, is that not all data types are supported....

            SQL> var dvar date;

             

            Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |

            VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |

            NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |

            BINARY_FLOAT | BINARY_DOUBLE ] ]

            where n is a positive integer

            So rather than run into an obstacle with some unsupported data type, the better approach is to create a function / procedure / package via the Code Editor and use standard PL/SQL.

            1 person found this helpful
            • 3. Re: Query Variables in SQL Developer
              thesqlguyatl

              This is what I was looking for!