Dynamic SQL Problems
Date: Sat, 10 Jul 1999 06:31:00 GMT
Message-ID: <3786E8A4.7B3B4E23_at_Boeing.com>
Oracle 7.3.3 - 8.0.5 has some serious limitations in the implementation of dynamic SQL. I have an enterprise application that is required to use the DBMS_SQL package to execute stored procedures, be they functions, procedures, or packages does not matter. The reason for using DBMS_SQL is because the application does not know what it will be calling, nor the content of what it will be executing (it uses a subset of PL/SQL as a scripting language to model business rule and the sort.) So say it needs to call a procedure with parameters of various PL/SQL datatypes. One goes about it by opening a cursor, forming the SQL statement on the fly, parsing the statement, binding the variables with respect to the host environment (VB6 here), executing the statement, retrieving the values passed back (if any), and finally closing the [Quoted] cursor. The binding step is where the house of cards fall apart for Oracle. The BIND_VARIABLE procedure is overloaded for a variety of datatypes, including number, char, varchar2,...., but alas nothing for boolean. Since the DBMS_SQL package deals with the PL/SQL domain, not the SQL domain, it would be expected that all of the supported PL/SQL datatypes would be covered here. So could a boolean be casted into one the others? After perusing the STANDARD package specification, it appears that Oracle is using an undocumented means of declaring the boolean datatype as a collection of two constants, TRUE and FALSE, neither of whose declaration or definition can be found in any of the unwrapped other package specifications. More likely than not, as with much of everything else that deals with the STANDARD package, all of that is buried under the hood in the form of hard-coded (binary) logic in some DLL. So what's an application to do when it cannot call stored procedures having boolean parameters? Well, for IN-bound parameters, no binding is needed. While the SQL statement is being built on the fly, simply concatenate TRUE or FALSE into it in place of something like :x. But this is of no comfort for parameters declared IN OUT or OUT. These require binding and cannot be handled in kind. Without a workaround, the stored procedure must resort to declaring logicals as char(1) for example and passing 'T' or 'F'. Of course this implies that in the procedure body one cannot test "if x then" -- instead, one must do something like "if x = 'T' then". This is unacceptable, especially in the case of using PL/SQL as a scripting language. A user of my application should not be forced to tweak his or her standards to mitigate lack of thought on Oracle's part. Does anyone have any ideas?
Alas a second gripe about DBMS_SQL. It calls DBMS_SYS_SQL which is fully wrapped. Here another oddity occurs... Same scenario, passing parameters to a procedure. What if a null value is passed for a IN-bound number, varchar2, or date variable? Just what you would expect, it uses the null in the procedure just like any other value. But for a char datatype, the ugliness of distinguishing between a null and a zero-length string surfaces. Passing a null fails with a bad value exception. Passing a '' works fine. I tried to make excuses for why Oracle would have implemented it this way... store fixed strings differently, specifying a string length is a contract between Oracle and the user that an actual value is on its way (not null),... etc. None of these makes sense. It's a pain because it forces you to have another procedure call the one you want to call and to pass NVL(x,'') rather than x as one would like.
[Quoted] Both of these took significant time to overcome, and the lack of documentation openly (freely!) available to developers on Oracle's part is truly frustrating.
Best Regards. Received on Sat Jul 10 1999 - 08:31:00 CEST