I don't have an 8.0.3 version of Oracle but the following is the result from 8.1.7:
SQL> create table wb (c1 char(8));
Table created.
SQL> insert into wb values('abcd');
1 row created.
SQL> insert into wb values('abcde');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from wb where c1 = 'abcd';
C1 -------- abcd
1 row selected.
SQL> create or replace procedure wbp as
2 v_c1 char(8);
3 l_c1 varchar2(8) := 'abcd';
4 l_c2 varchar2(8) := 'abcd ';
5 begin
6 begin
7 select c1 into v_c1 from wb where c1 = 'abcd';
8 dbms_output.put_line('literal: '||v_c1);
9 exception
10 when no_data_found then null;
11 end;
12 begin
13 select c1 into v_c1 from wb where c1 = l_c1;
14 dbms_output.put_line('case1: '||v_c1);
15 exception
16 when no_data_found then null;
17 end;
18 begin
19 select c1 into v_c1 from wb where c1 = l_c2;
20 dbms_output.put_line('case2: '||v_c1);
21 exception
22 when no_data_found then null;
23 end;
24* end;
25 /Procedure created.
SQL> exec wbp; literal: abcd case2: abcd
PL/SQL procedure successfully completed.
The literal worked. So did the the varchar2 string padded to match the length of the char column.
Now this is the result if run on 9.2:
SQL> create table wb (c1 char(8));
Table created.
SQL> insert into wb values('abcd');
1 row created.
SQL> insert into wb values('abcde');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from wb where c1 = 'abcd';
C1 -------- abcd
1 row selected.
SQL> create or replace procedure wbp as
2 v_c1 char(8);
3 l_c1 varchar2(8) := 'abcd';
4 l_c2 varchar2(8) := 'abcd ';
5 begin
6 begin
7 select c1 into v_c1 from wb where c1 = 'abcd';
8 dbms_output.put_line('literal: '||v_c1);
9 exception
10 when no_data_found then null;
11 end;
12 begin
13 select c1 into v_c1 from wb where c1 = l_c1;
14 dbms_output.put_line('case1: '||v_c1);
15 exception
16 when no_data_found then null;
17 end;
18 begin
19 select c1 into v_c1 from wb where c1 = l_c2;
20 dbms_output.put_line('case2: '||v_c1);
21 exception
22 when no_data_found then null;
23 end;
24* end;
25 /Procedure created.
SQL> exec wbp; literal: abcd case1: abcd
PL/SQL procedure successfully completed.
The literal still works the same, but now the unpadded string finds the row, but the padded one doesn't.
Moral: It pays to test when upgrading software.
At 08:14 AM 9/10/2003 -0800, you wrote:
Yet I do not understand why it returns a row in SQLPlus and it does not in a stored procedure... The problem is now fixed, but I'd like to know the reason it won't work leaving it as it was. It also fails if I put a string instead of a variable in the stored procedure.
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
