Home » SQL & PL/SQL » SQL & PL/SQL » Problem with the function
Problem with the function [message #189814] Mon, 28 August 2006 00:02 Go to next message
dbhargava
Messages: 4
Registered: April 2006
Location: India
Junior Member

Hi All,

I am able to create the below function without error but its not fulfilling my purpose as its not returning any value. Any kind of help is appreciated,

SQL> CREATE OR REPLACE FUNCTION SPACE
2 RETURN NUMBER IS
3 LvTsName varchar2(60);
4 LvTotalSize number;
5 LvFreeSpace number;
6 LvFreePnt number;
7 begin
8 for i in (select * from dba_tablespaces)
9 loop
10 begin
11 select a.tablespace_name, round(a.bytes/1024/1024) TOTAL_SIZE,
12 round(sum(b.bytes/1024/1024)) FREE_SPACE,
13 round((( sum(b.bytes/1024/1024)) / (a.bytes/1024/1024))*100,2) FREE_PERCENT
14 into LvTsName,LvTotalSize,LvFreeSpace,LvFreePnt
15 from sys.dba_data_files a, sys.dba_free_space b
16 where b.tablespace_name=i.tablespace_name
17 and a.file_id = b.file_id
18 group by a.tablespace_name, a.bytes
19 order by a.tablespace_name;
20 if LvFreePnt < 70 then
21 RETURN(i.tablespace_name || ': ' || LvFreeSpace);
22 end if;
23 exception
24 when others then null;
25 end;
26 END LOOP;
27 end space;
28 /

Function created.

SQL> select space from dual;
select space from dual
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "SYS.SPACE", line 24

thanks,
Devesh
Re: Problem with the function [message #189815 is a reply to message #189814] Mon, 28 August 2006 00:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

when others then null;

There is your #1 bug.
Furthermore, if there is no LvFreePnt < 70, there is no value returned.

Why the loop(s)? Why not select 'having xxxx (free_percent) < 70'?
Re: Problem with the function [message #189827 is a reply to message #189815] Mon, 28 August 2006 00:51 Go to previous messageGo to next message
dbhargava
Messages: 4
Registered: April 2006
Location: India
Junior Member

Hi There,

I have corrected this to
exception
when others then
RETURN null;

Loops are there as we have many tablespaces. Even I have tried removing the loop and putting the tabelspace name but no luck.
Re: Problem with the function [message #189830 is a reply to message #189827] Mon, 28 August 2006 00:54 Go to previous messageGo to next message
dbhargava
Messages: 4
Registered: April 2006
Location: India
Junior Member

Frank,

Its working fine now. I changed RETURN NUMBER IS to RETURN char IS

CREATE OR REPLACE FUNCTION SPACE
RETURN char IS
LvTsName varchar2(60);
LvTotalSize number;
LvFreeSpace number;
LvFreePnt number;
begin
for i in (select * from dba_tablespaces)
loop
begin
select a.tablespace_name, round(a.bytes/1024/1024) TOTAL_SIZE,
round(sum(b.bytes/1024/1024)) FREE_SPACE,
round((( sum(b.bytes/1024/1024)) / (a.bytes/1024/1024))*100,2) FREE_PERCENT into LvTsName,LvTotalSize,LvFreeSpace,LvFreePnt
from dba_data_files a, dba_free_space b
where b.tablespace_name=i.tablespace_name
and a.file_id = b.file_id
group by a.tablespace_name, a.bytes
order by a.tablespace_name;
if LvFreePnt < 20 then
RETURN(LvTsName|| ': ' || LvFreePnt);
end if;
exception
when others then
RETURN null;
end;
END LOOP;
end space;

Thanks,
Devesh
Re: Problem with the function [message #189844 is a reply to message #189830] Mon, 28 August 2006 01:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You still have a bug.
Did you actually read my previous post, or just the first line?

Besides that, why do you handle the when others? Why not let it propagate?
Re: Problem with the function [message #189852 is a reply to message #189844] Mon, 28 August 2006 02:10 Go to previous message
dbhargava
Messages: 4
Registered: April 2006
Location: India
Junior Member

Frank,

You are correct! Still there is some bug. I tried your option but its not returning any value. CHK

QL> ed
rote file afiedt.buf

1 CREATE OR REPLACE FUNCTION SPACE
2 RETURN char IS
3 LvTsName varchar2(60);
4 LvTotalSize number;
5 LvFreeSpace number;
6 LvFreePnt number;
7 begin
8 for i in (select * from dba_tablespaces)
9 loop
10 begin
11 select a.tablespace_name, round(a.bytes/1024/1024) TOTAL_SIZE,
12 round(sum(b.bytes/1024/1024)) FREE_SPACE,
13 round((( sum(b.bytes/1024/1024)) / (a.bytes/1024/1024))*100,2) FREE_PERCENT into LvTsName,LvTotalSize,LvFreeSpace,LvFree
nt
14 from dba_data_files a, dba_free_space b
15 where a.file_id = b.file_id and a.tablespace_name not in('SYSTEM','UNDO','DRSYS')
16 group by a.tablespace_name, a.bytes
17 having round((( sum(b.bytes/1024/1024)) / (a.bytes/1024/1024))*100,2)
18 in (select min(round((( sum(b.bytes/1024/1024)) / (a.bytes/1024/1024))*100,2)) FREE_PERCENT
19 from dba_data_files a, dba_free_space b
20 where a.file_id = b.file_id and b.tablespace_name=i.tablespace_name and a.tablespace_name not in('SYSTEM','UNDO','DRS
S')
21 group by a.tablespace_name, a.bytes);
22 RETURN(LvTsName|| ': ' || LvFreePnt);
23 end;
24 END LOOP;
25* end space;
QL> /

unction created.

QL> select space from dual;

PACE
-------------------------------------------------------------------------------


QL>
Previous Topic: insert data for timestamp with time zone
Next Topic: SubQuery
Goto Forum:
  


Current Time: Thu Dec 08 02:27:14 CST 2016

Total time taken to generate the page: 0.10276 seconds