Re: Pro*c - changing selection criteria

From: Scott Urman <surman_at_wwsun10.us.oracle.com>
Date: 8 Aug 1994 16:35:27 GMT
Message-ID: <325msf$ju4_at_dcsun4.us.oracle.com>


In article <1994Aug5.200555.17787_at_relay.acadiau.ca>, 910430n_at_dragon.acadiau.ca (Timothy Newell) writes:
|> Hi - I'm relatively new to Pro*C, and am having some problems
|> when changing the selection criteria for my queries. Can you
|> use the same cursor over if you change its query, then re-
|> prepare and re-open it? It seems to work for me in some cases,
|> but not in all. Are there any guidelines as to what you can
|> safely do, or how to go about re-using cursors and stuff?
|> I've been through the manual, but haven't been able to find what
|> I'm looking for. Any help would be appreciated.
|>
|> Tim Newell
|> 910430n_at_dragon.acadiau.ca
|>

It's hard to say what may be going wrong in this particular case, but here are some pointers about using dynamic SQL:

  1. It's a good idea to make the strings from which you PREPARE varchar structures. That way you can be sure about the length of the strings by setting the .len member of the structure. The precompilers won't look for a trailing null unless you tell them.
  2. NEVER USE THE SAME NAME FOR TWO CURSORS OR TWO STATEMENTS IN THE SAME FILE. Cursor names and statement names are precompiler identifiers, and their scope is global to the file.
  3. If you reprepare a given cursor, you can execute a different query. Just be sure that Oracle knows the length of the new string (see point 1).
  4. The PREPARE is doing the parse. You may be able to just change the values of the bind variables, and avoid reparsing the query again, thus saving execution time.

That's about all I can think of for now. Hope this helps! Received on Mon Aug 08 1994 - 18:35:27 CEST

Original text of this message