Home » SQL & PL/SQL » SQL & PL/SQL » problem with a simple PL/SQL Procedure
problem with a simple PL/SQL Procedure [message #36374] Thu, 22 November 2001 03:25 Go to next message
chamoun
Messages: 1
Registered: November 2001
Junior Member
Hi,

I have a PL/SQL bloc which run before I rewrite this bloc as procedure.

Now when I compile this procedure there are errors. Could someone help me to find what the matter.

Thank you.

CREATE OR REPLACE PROCEDURE ADD_DATAFILE is
tbs_name VARCHAR2(30);
MAX_FREE NUMBER;
Cursor N1 is Select tablespace_name TBS,owner,segment_name SEG_NAME,segment_type SEG_TYPE,
next_extent NEXT_EXT from dba_segments
where segment_type in ('TABLE','INDEX','CLUSTER','ROLLBACK')
and owner not in ('SYS','SYSTEM')
order by 1,4;
BEGIN
dbms_output.enable;
dbms_output.put_line(RPAD('TABLESPACE_NAME',15) ||' '||
RPAD('OWNER',12) ||' '||
RPAD('SEG_NAME',30) ||' '||
RPAD('TYPE',8) ||' '||
RPAD('NEXT_EXTENT',12) ||' '||
RPAD('MAX_FREE',12));
dbms_output.put_line(RPAD('-',15,'-') ||' '||
RPAD('-',12,'-') ||' '||
RPAD('-',30,'-') ||' '||
RPAD('-',8,'-') ||' '||
RPAD('-',12,'-') ||' '||
RPAD('-',12,'-'));
FOR a_rec in N1
LOOP
tbs_name:=a_rec.TBS;
Select max(bytes) into MAX_FREE from dba_free_space
where tablespace_name=tbs_name;

IF (MAX_FREE < a_rec.NEXT_EXT) THEN
dbms_output.put_line(RPAD(a_rec.TBS,15) ||' '||
RPAD(a_rec.owner,12) ||' '||
RPAD(a_rec.SEG_NAME,30) ||' '||
RPAD(a_rec.SEG_TYPE,8) ||' '||
RPAD(a_rec.NEXT_EXT,12) ||' '||
RPAD(MAX_FREE,12));
END IF;
END LOOP;
END;
/
show err

----------------------------------------------------------------------
Re: problem with a simple PL/SQL Procedure [message #36378 is a reply to message #36374] Thu, 22 November 2001 06:34 Go to previous message
Hans
Messages: 42
Registered: September 2000
Member
direct grants are needed now..

connect system/manager
 
CREATE OR REPLACE PROCEDURE ADD_DATAFILE
is
   tbs_name VARCHAR2(30);
   
   MAX_FREE NUMBER;
   
   Cursor N1 is 
      Select 
         tablespace_name TBS,
         owner,segment_name SEG_NAME,
         segment_type SEG_TYPE,
         next_extent NEXT_EXT 
      from dba_segments
      where segment_type in ('TABLE','INDEX','CLUSTER','ROLLBACK')
      and owner not in ('SYS','SYSTEM')
      order by 1,4;
BEGIN
   dbms_output.enable;
   dbms_output.put_line(   
                        RPAD('TABLESPACE_NAME',15) ||' '||
                        RPAD('OWNER',12) ||' '||
                        RPAD('SEG_NAME',30) ||' '||
                        RPAD('TYPE',8) ||' '||
                        RPAD('NEXT_EXTENT',12) ||' '||
                        RPAD('MAX_FREE',12)
                        );
                           
   dbms_output.put_line(
                        RPAD('-',15,'-') ||' '||
                        RPAD('-',12,'-') ||' '||
                        RPAD('-',30,'-') ||' '||
                        RPAD('-',8,'-') ||' '||
                        RPAD('-',12,'-') ||' '||
                        RPAD('-',12,'-')
                        );
      
FOR a_rec in N1 LOOP
   tbs_name:=a_rec.TBS;
   
   Select max(bytes) into MAX_FREE from dba_free_space
      where tablespace_name=tbs_name;
 
   IF (MAX_FREE < a_rec.NEXT_EXT) THEN
      dbms_output.put_line(
                           RPAD(a_rec.TBS,15) ||' '||
                           RPAD(a_rec.owner,12) ||' '||
                           RPAD(a_rec.SEG_NAME,30) ||' '||
                           RPAD(a_rec.SEG_TYPE,8) ||' '||
                           RPAD(a_rec.NEXT_EXT,12) ||' '||
                           RPAD(MAX_FREE,12)
                           );
   END IF;
END LOOP;
 
END;
/
show err

<b> 
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/3      PL/SQL: SQL Statement ignored
13/8     PLS-00201: identifier 'SYS.DBA_SEGMENTS' must be declared
38/2     PL/SQL: Statement ignored
38/12    PLS-00364: loop index variable 'A_REC' use is invalid
40/2     PL/SQL: SQL Statement ignored
40/39    PLS-00201: identifier 'SYS.DBA_FREE_SPACE' must be declared
43/2     PL/SQL: Statement ignored
43/17    PLS-00364: loop index variable 'A_REC' use is invalid
</b> 
 
 
 
connect sys/change_on_install
 
grant select on DBA_SEGMENTS to system;
grant select on DBA_FREE_SPACE to system;
 
 
 
connect system/manager
 
CREATE OR REPLACE PROCEDURE ADD_DATAFILE
is
   tbs_name VARCHAR2(30);
   
   MAX_FREE NUMBER;
   
   Cursor N1 is 
      Select 
         tablespace_name TBS,
         owner,segment_name SEG_NAME,
         segment_type SEG_TYPE,
         next_extent NEXT_EXT 
      from dba_segments
      where segment_type in ('TABLE','INDEX','CLUSTER','ROLLBACK')
      and owner not in ('SYS','SYSTEM')
      order by 1,4;
BEGIN
   dbms_output.enable;
   dbms_output.put_line(   
                        RPAD('TABLESPACE_NAME',15) ||' '||
                        RPAD('OWNER',12) ||' '||
                        RPAD('SEG_NAME',30) ||' '||
                        RPAD('TYPE',8) ||' '||
                        RPAD('NEXT_EXTENT',12) ||' '||
                        RPAD('MAX_FREE',12)
                        );
                           
   dbms_output.put_line(
                        RPAD('-',15,'-') ||' '||
                        RPAD('-',12,'-') ||' '||
                        RPAD('-',30,'-') ||' '||
                        RPAD('-',8,'-') ||' '||
                        RPAD('-',12,'-') ||' '||
                        RPAD('-',12,'-')
                        );
      
FOR a_rec in N1 LOOP
   tbs_name:=a_rec.TBS;
   
   Select max(bytes) into MAX_FREE from dba_free_space
      where tablespace_name=tbs_name;
 
   IF (MAX_FREE < a_rec.NEXT_EXT) THEN
      dbms_output.put_line(
                           RPAD(a_rec.TBS,15) ||' '||
                           RPAD(a_rec.owner,12) ||' '||
                           RPAD(a_rec.SEG_NAME,30) ||' '||
                           RPAD(a_rec.SEG_TYPE,8) ||' '||
                           RPAD(a_rec.NEXT_EXT,12) ||' '||
                           RPAD(MAX_FREE,12)
                           );
   END IF;
END LOOP;
 
END;
/
show err
 
<b> 
Procedure created.
</b> 


----------------------------------------------------------------------
Previous Topic: Dynamic Select with parameters
Next Topic: Re: how to create a temp table in 7.3 ??
Goto Forum:
  


Current Time: Sat Apr 04 16:33:24 CDT 2020