CREATE OR REPLACE PROCEDURE PROC AS
CURSOR C1 AS SELECT * FROM EMP;
BEGIN
 FOR R IN C1
 LOOP
     DBMS_OUTPUT.PUT_LINE(R.empno);
     ......
     ......
 END LOOP;
END;

 SELECT * FROM ALL_OBJECTS 

SQL> CREATE OR REPLACE PROCEDURE SP_RB
  2  AS 
  3  BEGIN
  4  NULL;
  5  END;
  6  /

Procedure created.

Elapsed: 00:00:00.56
SQL> SELECT OBJECT_NAME , OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_NAME = 'SP_RB';

OBJECT_NAME                                                        OBJECT_TYPE
-----------------------------------------------------------------  -------------------
SP_RB                                                              PROCEDURE

Elapsed: 00:00:00.78
SQL>   DECLARE 
  2    v_temp     NUMBER := 0 ;
  3    BEGIN
  4    SELECT 1 INTO  v_temp FROM dual where EXISTS 
  5                        ( SELECT  1 FROM  
  6                                 USER_OBJECTS 
  7                          WHERE 
  8                                OBJECT_NAME = UPPER('SP_RB')
  9                            AND OBJECT_TYPE = 'PROCEDURE'
 10                         );
 11                         
 12    IF (v_temp = 1) THEN
 13      BEGIN
 14        DBMS_OUTPUT.PUT_LINE ('PROCEDURE EXISTS') ;
 15      END;
 16    END IF;
 17    EXCEPTION
 18      WHEN NO_DATA_FOUND THEN
 19        v_temp := 0 ;
 20    END ;
 21  /
PROCEDURE EXISTS

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53

SQL> CREATE PROCEDURE SP_RB
  2   AS 
  3   BEGIN
  4   NULL;
  5   END;
  6   /
CREATE PROCEDURE SP_RB
                 *
ERROR at line 1:
ORA-00955: name is already used by an existing object


Elapsed: 00:00:00.75

  1  CREATE OR REPLACE PROCEDURE XX_proc_sample AS
  2     v_temp     NUMBER := 0 ;
  3        BEGIN
  4        SELECT 1 INTO  v_temp FROM dual where EXISTS
  5                            ( SELECT  1 FROM
  6                                     ALL_OBJECTS
  7                              WHERE
  8                                    OBJECT_NAME = UPPER('SP_RB')
  9                                AND OBJECT_TYPE = 'TABLE'
 10                            );
 11       IF (v_temp = 1) THEN
 12        DECLARE
 13             CURSOR lcu_c1 IS SELECT * FROM SP_RB;
 14        BEGIN
 15                 FOR R IN lcu_c1
 16     LOOP
 17             DBMS_OUTPUT.PUT_LINE(R.ID);
 18     END LOOP;
 19        END;
 20      END IF;
 21      EXCEPTION
 22        WHEN NO_DATA_FOUND THEN
 23          v_temp := 0 ;
 24*     END ;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show err;
Errors for PROCEDURE XX_PROC_SAMPLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/22    PL/SQL: SQL Statement ignored
13/36    PL/SQL: ORA-00942: table or view does not exist
17/3     PL/SQL: Statement ignored
17/24    PLS-00364: loop index variable 'R' use is invalid

CREATE OR REPLACE PROCEDURE XX_proc_sample AS
     v_temp     NUMBER := 0 ;
     v_objectname VARCHAR2(20):='TMP_TBL1';
     TYPE CURSORTYPE IS REF CURSOR;
     vCursor          CURSORTYPE;
     COL1  VARCHAR2(100);
  BEGIN
      SELECT 1 INTO  v_temp FROM dual where EXISTS
                           ( SELECT  1 FROM
                                    ALL_OBJECTS
                             WHERE
                                 OBJECT_NAME = v_objectname
                                AND OBJECT_TYPE = 'TABLE'
                           );
     IF (v_temp = 1) THEN
     
           OPEN vCursor FOR 'SELECT * FROM ' || v_objectname ;
           
        BEGIN
             
             LOOP
                
                FETCH vcursor into   COL1  ;
              
                EXIT WHEN vCursor%NOTFOUND;
                
               DBMS_OUTPUT.PUT_LINE(COL1);
               
             END LOOP;
       END;
     END IF;
     
     
      EXCEPTION
       WHEN NO_DATA_FOUND THEN
         v_temp := 0 ;
 END ;