Re: Dynamic SQL Problems
Date: Sun, 11 Jul 1999 06:48:00 GMT
Message-ID: <37883530.4177617_at_news.u-net.com>
Hello Martin,
Yes, there are some quirks in Oracle. :-)
On Sat, 10 Jul 1999 06:31:00 GMT, Martin Douglas <Martin.Douglas_at_Boeing.com> wrote:
>... 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.
You cannot store boolean data types in the database either. What i would do is create another 'bind variable' procedure which converts the boolean type to, say, a character. i.e.
procedure bind_variable_boolean(c in integer, name in varchar2,
value in boolean) is
lCh char;
begin
lCh := 'T';
if not value then lCh := 'F'; end if; dbms_sql.bind_variable_char(c => c, name => name, value => lCh); end;
Obviously, a column_value procedure would be needed to retrieve the value and convert it back to a boolean. It would provide the advantage of being transparent to programmers not so familiar with Oracle.
A similar technique could be used to deal with the null problem on the character binding procedure.
However, you now have the problem of having to call procedures in a different package to use dynamic sql.
What i would do at this point is provide a 'wrapper' package to the DBMS_SQL package. Although this is a few hours of work, it is not as daft as it first appears. This allows you to 'customize' dynamic sql access to the way you work at your site and with your applications.
Just my thoughts on the situation, and yes, it shouldn't be necessary to do this.
>
>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.
>
The package is quite well commented in the source code of dbmssql.sql Also, it is covered in the book 'Oracle PL/SQL programming' from Oracle Press.
graham Received on Sun Jul 11 1999 - 08:48:00 CEST