Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!wuarchive!gem.mps.ohio-state.edu!apple!voder!cullsj!brad
From: brad@cullsj.UUCP (Brad Might)
Newsgroups: comp.databases
Subject: ORACLE: PRO*C: dynamic sql specifications sought
Message-ID: <693@cullsj.UUCP>
Date: 19 Sep 89 16:27:43 GMT
Organization: Computer Associates, San Jose.
Lines: 56


	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 !