| RELEASE 8.1.7.4 ISSUE [message #63873] |
Thu, 18 November 2004 11:09  |
PAUL SIEGEL
Messages: 2
Registered: November 2004
|
Junior Member |
|
|
Just upgraded to 8.1.7.4 release (with security patch) A stored procedure that has an out variable defined as char(1) is returning 1000 of characters. It should return 1 character.
I am upgrading from 8.1.6.3
ie.
(column table1.ind is defined as char(1) )
create or replace procedure test_null (
in_name in table1.name%type,
out_ind out table1.ind%type )
is
x_null_ind char(1);
begin
if in_name is null then
x_null_ind := 'Y';
else
x_null_ind := 'N';
endif;
out_ind :=x_null_ind;
end;
out_ind looks like 'Y '
prior release looked like 'Y'
|
|
|
|
| Re: RELEASE 8.1.7.4 ISSUE [message #63876 is a reply to message #63873] |
Thu, 18 November 2004 20:30  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You cannot define the length of an out-parameter, only its datatype:
ONTW>create or replace procedure tst
2 ( p_in in varchar2(10)
3 , p_out out varchar2(1)
4 ) as
5 begin
6 null;
7 end;
8 /
Warning: Procedure created with compilation errors.
ONTW>show errors
Errors for PROCEDURE TST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/20 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.
3/21 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.
ONTW>create or replace procedure tst
2 ( p_in in varchar2
3 , p_out out varchar2
4 ) as
5 begin
6 null;
7 end;
8 /
Procedure created.
So, the %type only uses the DATATYPE of the column (date, number, char, varchar2) and NOT the size.
hth
|
|
|
|
| Re: RELEASE 8.1.7.4 ISSUE [message #63879 is a reply to message #63876] |
Fri, 19 November 2004 10:54 |
PAUL SIEGEL
Messages: 2
Registered: November 2004
|
Junior Member |
|
|
Version 8.1.6.3 lets you define an OUT variable using the %TYPE function. Release 8.1.7.4 is not so forgiving. I've changed my code to define my OUT variable as varchar2 instead of referencing a column in a table with the %TYPE command.
I beleive this is a bug in release 8.1.7.4. However, since this is the last supported release for this version, I don't believe Oracle will offer a patch. Actually, I am not sure if Oracle feels this is a bug anyways. I do however.
|
|
|
|
| Re: RELEASE 8.1.7.4 ISSUE [message #63881 is a reply to message #63879] |
Sat, 20 November 2004 02:00 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Don't have an 8.1.7.4 db at hand, but this happens in 10G:
SCOTT>create or replace procedure tst (p_in in varchar2, p_out out char)
2 is
3 begin
4 p_out := p_in;
5 end;
6 /
Procedure created.
SCOTT>declare
2 l_param varchar2(4000);
3 begin
4 tst('Y', l_param);
5 dbms_output.put_line(length(l_param));
6 tst('Yo', l_param);
7 dbms_output.put_line(length(l_param));
8 end;
9 /
4000
4000
PL/SQL procedure successfully completed.
SCOTT>declare
2 l_param varchar2(1000);
3 begin
4 tst('Y', l_param);
5 dbms_output.put_line(length(l_param));
6 tst('Yo', l_param);
7 dbms_output.put_line(length(l_param));
8 end;
9 /
1000
1000
PL/SQL procedure successfully completed.
<b>varchar2 out-parameter instead of char:</b>
SCOTT>create or replace procedure tst (p_in in varchar2, p_out out varchar2)
2 is
3 begin
4 p_out := p_in;
5 end;
6 /
Procedure created.
SCOTT>declare
2 l_param varchar2(4000);
3 begin
4 tst('Y', l_param);
5 dbms_output.put_line(length(l_param));
6 tst('Yo', l_param);
7 dbms_output.put_line(length(l_param));
8 end;
9 /
1
2
PL/SQL procedure successfully completed.
You see ? The char-output parameter has no size of its own. The size of it depends on the size of l_param you 'catch' it in!
Makes more sense now ?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
