conver anonymous procedure to store procedure

Dear friends,

I had a anoymous procedure for checking tablespace usage,
it can be run without problem. When I conver it to a store procedure, it have compilation error. Can anyone help me??

the anonymous procedure as follow (run on ora 8.1.6):

declare
name char(20);
mbytes char(20);
used char(20);
free char(20);
pct_used char(20);
cursor test_cur is
select a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
1048576 mbytes, round(((sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
round((sum(a.bytes)/count( distinct b.file_id ) /
1048576),2) free,
round(100 * ( (sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ))
-(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
test_val test_cur%rowtype;

begin
open test_cur;
dbms_output.put_line('Table Space ' ||' '||
'mbytes ' ||' '||
'used ' ||' '||
'free ' ||' '||
'pct_used ');
dbms_output.put_line('--------------------------------------------------------------------------------------------------');
loop
fetch test_cur into test_val;
exit when test_cur%notfound;
name := test_val.name;
mbytes := test_val.mbytes;
used := test_val.used;
free := test_val.free;
pct_used := test_val.pct_used;
dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
end loop;
close test_cur;
end;


The store proc (with error) as follow:

create procedure dsp_space
AS
name char(20);
mbytes char(20);
used char(20);
free char(20);
pct_used char(20);
cursor test_cur is
select a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
1048576 mbytes, round(((sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
round((sum(a.bytes)/count( distinct b.file_id ) /
1048576),2) free,
round(100 * ( (sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ))
-(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
test_val test_cur%rowtype;

begin
open test_cur;
dbms_output.put_line('Table Space ' ||' '||
'mbytes ' ||' '||
'used ' ||' '||
'free ' ||' '||
'pct_used ');
dbms_output.put_line('--------------------------------------------------------------------------------------------------');
loop
fetch test_cur into test_val;
exit when test_cur%notfound;
name := test_val.name;
mbytes := test_val.mbytes;
used := test_val.used;
free := test_val.free;
pct_used := test_val.pct_used;
dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
end loop;
close test_cur;
end;
/

What's the error ?

hi leungcwk
try it now it will be work
execute dsp_space

note : I used oracle8i 8.1.7

-----------------------------------------------------------------------
create or replace procedure dsp_space
is
name char(20);
mbytes char(20);
used char(20);
free char(20);
pct_used char(20);
cursor test_cur is
select a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
1048576 mbytes, round(((sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
round((sum(a.bytes)/count( distinct b.file_id ) /
1048576),2) free,
round(100 * ( (sum(b.bytes)/
count( distinct a.file_id||'.'||a.block_id ))
-(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
test_val test_cur%rowtype;
begin
open test_cur;
dbms_output.put_line('Table Space ' ||' '||
'mbytes ' ||' '||
'used ' ||' '||
'free ' ||' '||
'pct_used ');
dbms_output.put_line('--------------------------------------------------------------------------------------------------');
loop
fetch test_cur into test_val;
exit when test_cur%notfound;
name := test_val.name;
mbytes := test_val.mbytes;
used := test_val.used;
free := test_val.free;
pct_used := test_val.pct_used;
dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
end loop;
close test_cur;
end;
/


-----------------------------------------------------------------------

Thanks Mohammed.
However, I still get compilation errors.
There seems some problem on cursor declaration.
If you have any idea to fix the error, please feed back
to me. thanks



SQL> l
1 create or replace procedure dsp_space
2 is
3 name char(20);
4 mbytes char(20);
5 used char(20);
6 free char(20);
7 pct_used char(20);
8 cursor test_cur is
9 select a.tablespace_name name,
10 sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
11 1048576 mbytes, round(((sum(b.bytes)/
12 count( distinct a.file_id||'.'||a.block_id ) -
13 sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
14 round((sum(a.bytes)/count( distinct b.file_id ) /
15 1048576),2) free,
16 round(100 * ( (sum(b.bytes)/
17 count( distinct a.file_id||'.'||a.block_id ))
18 -(sum(a.bytes)/count( distinct b.file_id ) )) /
19 (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
20 pct_used
21 from sys.dba_free_space a, sys.dba_data_files b
22 where a.tablespace_name = b.tablespace_name
23 group by a.tablespace_name, b.tablespace_name;
24 test_val test_cur%rowtype;
25 begin
26 open test_cur;
27 dbms_output.put_line('Table Space ' ||' '||
28 'mbytes ' ||' '||
29 'used ' ||' '||
30 'free ' ||' '||
31 'pct_used ');
32 dbms_output.put_line('-----------------------------------------------------
---------------------------------------------');
33 loop
34 fetch test_cur into test_val;
35 exit when test_cur%notfound;
36 name := test_val.name;
37 mbytes := test_val.mbytes;
38 used := test_val.used;
39 free := test_val.free;
40 pct_used := test_val.pct_used;
41 dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '||
pct_used);
42 end loop;
43 close test_cur;
44* end;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE DSP_SPACE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/8 PLS-00341: declaration of cursor 'TEST_CUR' is incomplete or
malformed

9/1 PL/SQL: SQL Statement ignored
21/28 PLS-00201: identifier 'SYS.DBA_DATA_FILES' must be declared
24/10 PL/SQL: Item ignored
34/1 PL/SQL: SQL Statement ignored
34/21 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

36/1 PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
36/9 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

37/1 PL/SQL: Statement ignored
37/11 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

38/1 PL/SQL: Statement ignored
38/9 PLS-00320: the declaration of the type of this expression is
incomplete or malformed


LINE/COL ERROR
-------- -----------------------------------------------------------------
39/1 PL/SQL: Statement ignored
39/9 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

40/1 PL/SQL: Statement ignored
40/13 PLS-00320: the declaration of the type of this expression is
incomplete or malformed




-----------------------------------------------------------------------

[/B][/QUOTE]

[Edited by leungcwk on 03-19-2002 at 11:14 PM]

create or replace procedure dsp_space
is
name char(20);
mbytes char(20);
used char(20);
free char(20);
pct_used char(20);
cursor test_cur is
select a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) / 1048576 mbytes, round(((sum(b.bytes)/ count( distinct a.file_id||'.'||a.block_id )
- sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
round((sum(a.bytes)/count( distinct b.file_id ) / 1048576),2) free,
round(100 * ( (sum(b.bytes)/ count( distinct a.file_id||'.'||a.block_id ))
-(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
begin
dbms_output.put_line('Table Space ' ||' '|| 'mbytes ' ||' '|| 'used ' ||' '|| 'free ' ||' '|| 'pct_used ');
FOR test_rec in test_cur Loop
name := test_rec.name;
mbytes := test_rec.mbytes;
used := test_rec.used;
free := test_rec.free;
pct_used := test_rec.pct_used;
dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
End Loop;
end;
/

the problem has nothing to do with this code. it is with sys tables.

first grant select on dba_free_space and dba_data_files to the user that is going to execute the procedure.

connect sys/password

grant select on dba_free_space, dba_data_files to user;

and then compile your procedure.

- you cannot use sys tables with your schema procedure if you don't have priviledges to use them.


F.

Quote:

---

Originally posted by Mnemonical

the problem has nothing to do with this code. it is with sys tables.

first grant select on dba_free_space and dba_data_files to the user that is going to execute the procedure.

connect sys/password

grant select on dba_free_space, dba_data_files to user;

and then compile your procedure.

- you cannot use sys tables with your schema procedure if you don't have priviledges to use them.


F.

---

Thanks Mnemonical,

This is the solution! problem fixed, thank you.

Regards,
Kelvin