Is there a parm limit for SQL and can blanks be "ignored"?

[MCubitt]

I am passing upto 30 parameters to an SQL script, althought I have not tested 30, is this accepted by Oracle?

Also, where a parameter is not passed, the script will prompt for it, is it possible to default these to blank or null?

Thanks





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

Martin,

I presume from your posting when you say "I am passing up to 30 parameters to an SQL script..." you are using SQL*Plus's mechanism for "literal replacement definitions", (&1, &2, et cetera). (They are not "parameters", per se, unless you are passing values to a PL/SQL function or procedure, and even then, we refer to them as arguments.)

Although there is probably some high-end limit to the number of "literal replacement definitions" that SQL*Plus allows, I believe that 30 will be within SQL*Plus's supported number.

Keeping in mind that using this mechanism, SQL*Plus is just using the "literal replacement definitions" as literals in the SQL script, when you are ignoring one or more of the replacement values, you would use whatever value SQL*Plus would accept as valid syntax if you were literally coding a missing value. Therefore, you should use either two successive single quotes ('') or the term NULL.

Let us know of your results,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:48 (10Aug04) UTC (aka "GMT" and "Zulu"), 10:48 (10Aug04) Mountain Time)

 

[MCubitt]

Santa,

Thanks for the clarification.

In fact, the MSDOS file (used because the tech users will be comfortable editing it!) has:

sqlplus username/password@DBNAME  @".\makecopy.sql" "smithj" "jonesj" "Jeff Jones" "smithj" "gatesg" "Gareth Gates" "davisl" "smithp" "Pauline Smith"

and the SQL does in fact make varaiables out of these..

var uCopyFromUserID varchar2(20)
var   uCopyToUserID varchar2(20)
var uCopyToFullName varchar2(30)

var uCopyFromUserID00 varchar2(20)
var   uCopyToUserID00 varchar2(20)
var uCopyToFullName00 varchar2(30)

var uCopyFromUserID01 varchar2(20)
var   uCopyToUserID01 varchar2(20)
var uCopyToFullName01 varchar2(30)

var uCopyFromUserID02 varchar2(20)
var   uCopyToUserID02 varchar2(20)
var uCopyToFullName02 varchar2(30)
.
.
.

EXEC :uCopyFromUserID00 := UPPER('&1')
EXEC   :uCopyToUserID00 := UPPER('&2')
EXEC :uCopyToFullName00 :=      ('&3')

EXEC :uCopyFromUserID01 := UPPER('&4')
EXEC   :uCopyToUserID01 := UPPER('&5')
EXEC :uCopyToFullName01 :=      ('&6')

EXEC :uCopyFromUserID02 := UPPER('&7')
EXEC   :uCopyToUserID02 := UPPER('&8')
EXEC :uCopyToFullName02 :=      ('&9')

EXEC :uCopyFromUserID03 := UPPER('&10')
EXEC   :uCopyToUserID03 := UPPER('&11')
EXEC :uCopyToFullName03 :=      ('&12')
.
.
.

spool file.txt

EXEC :uCopyFromUserID := :uCopyFromUserID00
EXEC   :uCopyToUserID :=   :uCopyToUserID00
EXEC :uCopyToFullName := :uCopyToFullName00
select... where id=:uCopyFromUserID and id != null

EXEC :uCopyFromUserID := :uCopyFromUserID01
EXEC   :uCopyToUserID :=   :uCopyToUserID01
EXEC :uCopyToFullName := :uCopyToFullName01
select... where id=:uCopyFromUserID and id != null

EXEC :uCopyFromUserID := :uCopyFromUserID02
EXEC   :uCopyToUserID :=   :uCopyToUserID02
EXEC :uCopyToFullName := :uCopyToFullName02
select... where id=:uCopyFromUserID and id != null
.
.
.

spool off

Of course, when I run this I am prompted "Enter a value for 7" but I want these to be nulled!

Thanks



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

Martin,

So what happens if you use two successive double quotes ("") to represent your NULL in the string of replacement values?

Mufasa
(aka Dave of Sandy, Utah, USA @ 09:39 (11Aug04) UTC (aka "GMT" and "Zulu"), 02:39 (11Aug04) Mountain Time)

 

[MCubitt]

Well, I tested that and it successfully ignores those values. That does mean that the user has to fill out a set of ""'s but I guess it is no hardship.

The alternative is that when prompted for a value of 7, etc, the user just hits Enter.

It's just a shame SQLPlus cannot say "if no value passed to me, then make the argument null"

Thanks anyway.



There's no need for sarcastic replies, we've not all been this sad for that long!