0 Replies Latest reply on Jan 17, 2013 4:35 PM by 973026

    optional commandline parameters when running from within SQL Developer


      What I'd like to do is write SQL scripts which will accept a variable number of command line
      parameters. The parameters which aren't specified would then either be null or have some
      default value.

      Other people seem to have wanted this too, as there are a few posts on the web about
      how to do this with sqlplus.

      The crux of the matter is that I have a solution to the above problem which works with sqlplus,
      but it doesn't work if you run the same script from inside SQL Developer.

      This bothers me, because I like SQL Developer, and i use it for all my oracle work, as far as possible.
      I don't want to have to use sqlplus for some tasks, and SQL developer for others. I think you know what
      i mean.

      Anyway, this is the solution for sqlplus :

      set termout off
      col p1 new_value 1
      col p2 new_value 2
      col p3 new_value 3
      select null p1, null p2, null p3 from dual where 1=2;
      select nvl('&1','def1') p1, nvl('&2','def2') p2, nvl('&3','def3') p3 from dual;
      set termout on
      prompt 1="&1"
      prompt 2="&2"
      prompt 3="&3"
      undef 1
      undef 2
      undef 3

      If you run this script from SQLplus, you get the following functionality :

      SQL> @test
      SQL> @test 333
      SQL> @test 333 444

      This is perfect - it's exactly how i want the script to work.

      BUT, when I run it from SQL developer, it prompts me for the value of &1

      So, from within SQL developer, I'm doing :


      SQL developer then responds 'Enter substituation variable &1 '

      It's presumably this line :
      select nvl('&1','def1') p1, nvl('&2','def2') p2, nvl('&3','def3') p3 from dual;

      Why? This is very frustrating to me. If I don't specify the command line variable, I don't want to be prompted
      for it. Is it a bug?

      Clearly there's a difference between SQLplus and SQL Developer. Does anyone know why it behaves in that

      And more importantly, is there a way to get the SQL scripts to have the behaviour I want from inside
      SQL Developer?


      Paul S