This discussion is archived
0 Replies Latest reply: Jan 17, 2013 8:35 AM by 973026 RSS

optional commandline parameters when running from within SQL Developer

973026 Newbie
Currently Being Moderated
Hello,

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
1="def1"
2="def2"
3="def3"
SQL> @test 333
1="333"
2="def2"
3="def3"
SQL> @test 333 444
1="333"
2="444"
3="def3"
SQL>

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 :

@test

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
way?

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

regards


Paul S

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points