Re: stored procedures & parameters

In article <3ae51a0f$1_1_at_news.bizonline.net>, Ian L <.NOSPAM> writes:
>I'm new to Oracle and not at all sure how to do the following:
>
>I want to pass in various parameters to a stored procedure and use them as
>search criteria in a select statement (so far so good), the problem is that
>I may not want to use all the parameters depending on the data being passed
>in. For example if I pass in a zero value to one parameter then I do not
>want to include that section of the where clause. Can I dynamically build up
>the select statement in a stored procedure?
>
>Many thanks for your help in advance.
>
>Ian
>
>

If you have version 8.1+ look up the pl/sql command 'execute immediate'. For versions 8 and lower look up the dbms_sql package.

If you have a fixed number of known possible queries you could also potentially use a loosely typed/defined reference cursor and just execute the correct SQL string based on the input.

• Mark D Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net