Counting arguments passed to SQL or PL/SQL?

Counting arguments passed to SQL or PL/SQL?

Post by Jeff Wilso » Wed, 27 Jun 2001 10:09:39



Have checked all the on-line FAQs but can't find the answer to this.

Have a PL/SQL script that takes optional arguments for switches and such.  I
know &1, &2, etc., represent these arguments, but is there a variable that
represents the number of arguments?

The problem I'm having is that if a variable is not passed in, then SQL asks
for it's value if it is encountered in the script.  I need some way of
knowing if a variable is set.

Thanks.

--
Jeff Wilson

Eugene, Oregon

(541) 684-8590

 
 
 

Counting arguments passed to SQL or PL/SQL?

Post by Jim Kenned » Wed, 27 Jun 2001 11:54:05


Could you set it to something impossible and test for that?
Jim

> Have checked all the on-line FAQs but can't find the answer to this.

> Have a PL/SQL script that takes optional arguments for switches and such.
I
> know &1, &2, etc., represent these arguments, but is there a variable that
> represents the number of arguments?

> The problem I'm having is that if a variable is not passed in, then SQL
asks
> for it's value if it is encountered in the script.  I need some way of
> knowing if a variable is set.

> Thanks.

> --
> Jeff Wilson

> Eugene, Oregon

> (541) 684-8590


 
 
 

Counting arguments passed to SQL or PL/SQL?

Post by Donna Alvor » Fri, 29 Jun 2001 21:44:10


Seems that the problem is with the parameterized SQL query.

When you tell  the query that there will be 5 parameters it MUST have them.
If it can not find them it will ask.
You need to deal with the argument count ambiguity.

For example if you list is a set of possible values of a single column
consider
... WHERE  your_col IN (?);
Take you set of possible values in the parameter list, place them in one
string, with proper quotes, and deliver a single parameter.

More details about 'switches and such' will help provide more example.

> Have checked all the on-line FAQs but can't find the answer to this.

> Have a PL/SQL script that takes optional arguments for switches and such.
I
> know &1, &2, etc., represent these arguments, but is there a variable that
> represents the number of arguments?

> The problem I'm having is that if a variable is not passed in, then SQL
asks
> for it's value if it is encountered in the script.  I need some way of
> knowing if a variable is set.

> Thanks.

> --
> Jeff Wilson

> Eugene, Oregon

> (541) 684-8590

 
 
 

Counting arguments passed to SQL or PL/SQL?

Post by Venki Ramachandra » Sat, 21 Jul 2001 06:48:53


I would use a dynamic sql anf form it based on the input parameters sent. I have
a similar procedure where about 20 input pparameters can be sent or some of them
can be null. Depending on the values sent I form the dynamic sql. It is working
and fast. Hope that helps.

-Venki


> Have checked all the on-line FAQs but can't find the answer to this.

> Have a PL/SQL script that takes optional arguments for switches and such.  I
> know &1, &2, etc., represent these arguments, but is there a variable that
> represents the number of arguments?

> The problem I'm having is that if a variable is not passed in, then SQL asks
> for it's value if it is encountered in the script.  I need some way of
> knowing if a variable is set.

> Thanks.

> --
> Jeff Wilson

> Eugene, Oregon

> (541) 684-8590