mark@ditmela.oz (Mark Blakey) (09/19/89)
We are currently using ORACLE's (V5) Pro*C embedded SQL product (V1.1). We're looking at implementing a particularly ugly selection filter using dynamic sql. The where clause will contain a logical combination of smaller filter items. Can anyone enlighten me on the following: 1. how long may a 'where' clause be (in characters)? 2. how many levels of nesting of logical expressions are permitted in the 'where' clause? 3. how deep may sub-queries be nested? Is any of this configurable? I cant find it in the manuals and ORACLE wont/cant tell me. Thanks in Advance. Mark Blakey
brad@cullsj.UUCP (Brad Might) (09/19/89)
Cannot find address for ditmela so I am posting. mark@ditmela.oz (Mark Blakey) writes: > We are currently using ORACLE's (V5) Pro*C embedded SQL > product (V1.1). We're looking at implementing a particularly ugly selection > filter using dynamic sql. The where clause will contain a logical combination > of smaller filter items. Can anyone enlighten me on the following: > > 1. how long may a 'where' clause be (in characters)? > > 2. how many levels of nesting of logical expressions are > permitted in the 'where' clause? > > 3. how deep may sub-queries be nested? > > Is any of this configurable? I cant find it in the manuals > and ORACLE wont/cant tell me. > > Thanks in Advance. > > Mark Blakey > I have done implementations of dynamic sql for several databases (Oracle, RDB, EDB (Cullinet/CA)). (And all in the C language except for RDB which req'd PL1). Basically, any SQL statement legal in SQLPLUS can be passed in as a string via dynamic SQL (perhaps variable substitution is excluded here)... therefore, test your queries interactively first to determine that you are using the correct SQL. Estimate your largest sql statement (not necessarily most complicated), and then create a statement this size to try dynamically. we currently use a buffer size of 3994 bytes (due to limitations in other software, not oracle). All dynamic sql's that I have seen are ugly to use. if you plan to use dynamic sql with more than one database (application may run over other rdb's), make your interface separate from the rest of your application. Create a data structure to hold the result of your query, rather than using Oracle's structure, as it will be completely different with your next db. Handling Numbers via dynamic sql is not too pleasant. -- ------------------------------------------------------------------- Brad Might UUCP: ...!ames!cullsj!brad Operating long distance from Toronto,Canada. Insert Disclaimer Here: Opinions expressed are Opinions expressed !
bsa@telotech.UUCP (Brandon S. Allbery) (09/26/89)
In article <693@cullsj.UUCP>, brad@cullsj (Brad Might) writes: +--------------- | Basically, any SQL statement legal in SQLPLUS can be | passed in as a string via dynamic SQL (perhaps variable | substitution is excluded here)... > . . . | Handling Numbers via dynamic sql is not too pleasant. +--------------- Why? Embedded SQL should include "binding": use a question mark in place of a value which may vary and place a pointer to the value in the bind structure for that question mark. You then have to call the input bind function before executing the SQL statement. (I do not give details because it can vary -- hey, ANSI, how about an embedded SQL API standard? -- and I don't remember how to do it for Oracle, although I *have* done so and it works fine.) Note that the embedded SQL API should include translation to the host language number format: if ESQL insists that you bind a number in its internal number format (e.g. base-100), complain loudly to your DBMS vendor. ++Brandon -- -=> Brandon S. Allbery @ telotech, inc. (I do not speak for telotech.) <=- Any comp.sources.misc postings sent to this address will be DISCARDED -- use allbery@uunet.UU.NET instead. My boss doesn't pay me to moderate newsgroups. ** allbery@NCoast.ORG ** uunet!hal.cwru.edu!ncoast!{allbery,telotech!bsa} **