8 Replies Latest reply on Jan 26, 2017 9:24 PM by 1002330

    SQL Developer 4.1.5.21 Defining variables

    1002330

      In SQL Developer 3.2 and above (some) versions I was able to define multiple variables on a single line. However, in 4.1.5, when I run the statement, the database/memory ONLY captures the first variable and the rest are ignored. Below is an example of what I am talking about:

       

      In 3.2 - I could define/initialize multiple sourcefile's like this:

      define sourcefile = 'jan.txt', 'feb.txt', 'mar.txt';

       

      However, in 4.1.5 the above statement does not work. I have to define sourcefiles in different lines AND run them individually...which is a major pain.

      define sourcefile = 'jan.txt';

      define sourcefile = 'feb.txt';

      define sourcefile = 'mar.txt';

       

      I really appreciate the help. Thanks!!

        • 1. Re: SQL Developer 4.1.5.21 Defining variables
          Gaz in Oz

          Surround the define variable value in double quotes:

          define sourcefile = "'jan.txt', 'feb.txt', 'mar.txt'";
          • 2. Re: SQL Developer 4.1.5.21 Defining variables

            Post a COMPLETE example of what you are talking about and the full version you say it works on and also doesn't work on.

            In 3.2 - I could define/initialize multiple sourcefile's like this:

            define sourcefile = 'jan.txt', 'feb.txt', 'mar.txt';

            The DEFINE only defines a single variable and that single variable only has a single value.

             

            So we need to be able to reproduce what you are talking about.

            • 3. Re: SQL Developer 4.1.5.21 Defining variables
              thatJeffSmith-Oracle

              does that work in SQL*Plus? If so, it would be a bug.

              • 4. Re: SQL Developer 4.1.5.21 Defining variables
                1002330

                @rp0428 - Thanks for the response. I have added the full version of sql developer in my post on which this works. I dont know what else you want me to add. You CAN define multiple "sourcefiles" not multiple variables. At least you could do that in SQL dev 3.2.

                 

                thatJeffSmith-Oracle - Thanks for the response. Your blog is most helpful. Unfortunately I dont have access to sql plus or know how to use it. Is there a work around? Or do I have to go back to version 3.2? Thanks!

                • 5. Re: SQL Developer 4.1.5.21 Defining variables
                  Gaz in Oz

                  In SQL Developer 3.2.20.10, using your define command from your post, here's one way to show a complete, reproducible example:

                   

                  In 3.2 - I could define/initialize multiple sourcefile's like this:

                  define sourcefile = 'jan.txt', 'feb.txt', 'mar.txt';

                  In sqldeveloper 3.2.20.10 "[Worksheet]" run the following:

                  define sourcefile = 'jan.txt', 'feb.txt', 'mar.txt';

                  /* ...and display the sourcefile variable */

                  define sourcefile;

                  sqldeveloper "[Script Output]"

                  DEFINE SOURCEFILE =  "jan.txt', 'feb.txt', 'mar.txt" (CHAR)

                  The first thing I note is the leading and trailing single quotes are trimmed.

                   

                  In SQL Developer 4.2.0.16.356, (and in 4.1.2.20) running the same example as above

                  [Script Output]

                  DEFINE SOURCEFILE =  jan.txt  (CHAR)

                  This is consistent with how sqlplus behaves.

                   

                  To properly define the variable in your post, as stated in reply 1,

                  Surround the define variable value in double quotes:

                  [Worksheet]

                  define sourcefile = "'jan.txt', 'feb.txt', 'mar.txt'";

                  /* ...and display the sourcefile variable */

                  define sourcefile;

                  [Script Output]

                  DEFINE SOURCEFILE    = "'jan.txt', 'feb.txt', 'mar.txt'" (CHAR)

                  Note this is ALSO consistent with how sqlplus behaves and also note the leading and trailing single quotes are preserved,... AND this approach gives a consistent result across sqlplus and the three versions of sqldeveloper I tried.

                   

                  Cheers,

                   

                  Gaz.

                  1 person found this helpful
                  • 6. Re: SQL Developer 4.1.5.21 Defining variables
                    1002330

                    Gaz - Thank you for taking the time and replying to my question. I forgot to mention in my original post that when I try to define/initialize these sourcefiles as I used to do it in sql developer 3.2, the script runs fine....BUT its kinda fooling you....because when I DO run my query, it ONLY pulls the data from the first sourcefile in that array.

                     

                    for ex - In 4.1. this statement runs fine without any errors. "define sourcefile = 'jan.txt', 'feb.txt', 'mar.txt';

                    The way I am running the above statement is simply by highlighting the whole line and pressing F9. Thats the exact way I used to do in 3.2.

                    Then I run my query below which is calling the sourcefiles in my WHERE statement like this - bla.source_file in ('&SOURCEFILE');

                    The data/output I get is ONLY from the file 'jan.txt'...it completely ignores the 'feb.txt' and 'mar.txt' files!!

                     

                    I tried doing it your way, however, I get the error "ORA-00907: missing right parenthesis" on the line where I am calling the sourcefile ->  bla.source_file in ('&SOURCEFILE'). Thanks again!

                    • 7. Re: SQL Developer 4.1.5.21 Defining variables
                      Gary Graham-Oracle

                      The key point here is that SQL Developer worksheet (and SQLcl, which shares common code) have become more consistent with SQL*Plus over the course of 4.1/4.2 development.  Your latest error when doing as Gaz says

                      SQL Error: ORA-00907: missing right parenthesis

                      is because your syntax should be

                      in (&sourcefile.)

                      rather than

                      in ('&sourcefile')

                      In terms of SQL*Plus, this is what I see...

                      Connected to:

                      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

                       

                      SQL> def sourcefile = 'jan.txt', 'feb.txt', 'mar.txt';

                      SQL> def sourcefile

                      DEFINE SOURCEFILE      = "jan.txt" (CHAR)

                      SQL> def sourcefile = ''jan.txt', 'feb.txt', 'mar.txt'';

                      SQL> def sourcefile

                      DEFINE SOURCEFILE      = "" (CHAR)

                      SQL> def sourcefile = "'jan.txt', 'feb.txt', 'mar.txt'";

                      SQL> def sourcefile

                      DEFINE SOURCEFILE      = "'jan.txt', 'feb.txt', 'mar.txt'" (CHAR)

                      SQL> select 1 from dual where 'feb.txt' in (&sourcefile.);

                      old   1: select 1 from dual where 'feb.txt' in (&sourcefile.)

                      new   1: select 1 from dual where 'feb.txt' in ('jan.txt', 'feb.txt', 'mar.txt')

                       

                               1

                      ----------

                               1

                       

                      SQL> select 1 from dual where 'xxx.txt' in (&sourcefile.);

                      old   1: select 1 from dual where 'xxx.txt' in (&sourcefile.)

                      new   1: select 1 from dual where 'xxx.txt' in ('jan.txt', 'feb.txt', 'mar.txt')

                       

                      no rows selected

                       

                      SQL> quit

                      Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

                      while a 4.1.5 worksheet with this code...

                      def sourcefile = "'jan.txt', 'feb.txt', 'mar.txt'";

                      def sourcefile

                      select 1 from dual where 'feb.txt' in (&sourcefile.);

                      select 1 from dual where 'xxx.txt' in (&sourcefile.);

                      produces this in the Script Output tab...

                      DEFINE SOURCEFILE =  "'jan.txt', 'feb.txt', 'mar.txt'" (CHAR)

                      old:select 1 from dual where 'feb.txt' in (&sourcefile.)

                      new:select 1 from dual where 'feb.txt' in ('jan.txt', 'feb.txt', 'mar.txt')

                               1

                      ----------

                               1

                       

                      old:select 1 from dual where 'xxx.txt' in (&sourcefile.)

                      new:select 1 from dual where 'xxx.txt' in ('jan.txt', 'feb.txt', 'mar.txt')

                       

                      no rows selected

                      1 person found this helpful
                      • 8. Re: SQL Developer 4.1.5.21 Defining variables
                        1002330

                        Thank you Gary & Gaz.....thank you all who replied. It looks like the change you guys recommended has done the trick. Unfortunately I was not able to check Gary AND Gaz's reply as correct answer. But for someone who will be looking at this thread in the future....the correct answer is the combination of both of their responses.....