I have a PL/SQL procedure withOUT
parameters. I wanted the OUT parameter values to be written also in case of exception. I found out this very helpful post. Code in answer commented with "Example 4 -- OUT NOCOPY parameter value is changed if an exception was raised" should solved my problem. I checked this one:
declare
x number := 1;
procedure p(x out NOCOPY number)
as
begin
x := 2;
raise program_error;
end p;
begin
p(x);
exception
when program_error then
dbms_output.put_line
(
case
when x is null then 'null'
else to_char(x)
end
);
end;
/
Above code works as expected. DBMS output is: 2. But my real case was not working like that. The data type of actual parameter was different than the data type of the formal parameter. It was not very obvious because I used TYPE attribute in actual parameter declaration. I used:
x number := 1;
procedure p(x out NOCOPY number)
as
begin
x := 2;
raise program_error;
end p;
begin
p(x);
exception
when program_error then
dbms_output.put_line
(
case
when x is null then 'null'
else to_char(x)
end
);
end;
/
Above code works as expected. DBMS output is: 2. But my real case was not working like that. The data type of actual parameter was different than the data type of the formal parameter. It was not very obvious because I used TYPE attribute in actual parameter declaration. I used:
x table.col%TYPE := 1;
It turned out table.col%TYPE is VARCHAR2. My equivalent of above code was:
declare
x varchar(1) := 1;
procedure p(x out NOCOPY number)
as
begin
x := 2;
raise program_error;
end p;
begin
p(x);
exception
when program_error then
dbms_output.put_line
(
case
when x is null then 'null'
else to_char(x)
end
);
end;
/
DBMS output is: 1
DBMS output is: 1
This is unexpected. Here we can find explanation.
The compiler ignores NOCOPY
in these cases:
The actual parameter must be implicitly converted to the data type of the formal parameter.
....
Ni komentarjev:
Objavite komentar