Re: SQL Restrictions?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 Sep 1998 15:57:00 GMT
Message-ID: <3613b0e7.12759276@192.86.155.100>

A copy of this was sent to "Nick Lehane" <LehaneN_at_logica.com> (if that email address didn't require changing) On 28 Sep 1998 14:07:52 GMT, you wrote:

>Hi everyone,
>
>I have a question regarding possible limitations of PL/SQL.
>
>I am trying to execute the following statement:
>
>INSERT INTO Temp_Replies
> SELECT i.formats, f.decode, 'D'
> FROM inc_mrc_rep_format i, format_decode f
> WHERE i.inc = :Global.INC
> AND i.mrc = :Global.MRC
> AND i.criticality = :Global.Criticality
> AND i.mode_code = :Global.Mode_code
> AND f.formats (+) = i.formats;
>COMMIT;
>
>Basically this looks up a format value in one table and uses it to obtain a
>decode value from the format_decode table.
>
>The format of the format (!) a character string enclosed in brackets.
>
>When I pass a specific value to the formats line eg AND f.formats = '(XA)'
>then I get a decode value.
>

I'm assuming that when you use a BIND VARIABLE you are getting different results? (there really wasn't a question in this posting...)

Could it be that you have the base tables built with 'CHAR' datatypes which use blank padded character strings and your bind variables are either

• the wrong size
• of the varchar type which won't compare to CHAR types unless it is exactly the right size.

For example, consider:

SQL> create table t1 ( data char(5) );

Table created.

SQL>
SQL> insert into t1 values ( '(XA)' );

1 row created.

SQL>
SQL> select * from t1 where data = '(XA)';

DATA

---

(XA)

SQL> 
SQL> variable bindvar varchar2(5)
SQL> exec :bindvar := '(XA)'

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t1 where data = :bindvar;

no rows selected

SQL> select * from t1 where data = rpad(:bindvar,5);

DATA

---

(XA)

see, when we use bindvar without rpad'ing it out, the comparision fails '(XA)' <> '(XA) '

character string constants are automagically blank padded out for you...

>Could it be that the parentheses are affecting the comparison? And if so
>could anybody suggest a way of bypassing this?
>
>Many thanks,
>
>Nick Lehane
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Sep 28 1998 - 10:57:00 CDT