5 Replies Latest reply: Apr 25, 2013 1:23 PM by sb92075 RSS

    procedure compilation error

    972227
      Hello guys, I wrote the following pl/sql block
      declare 
          cursor c1 is select sequence_owner, sequence_name from dba_sequences where sequence_owner like 'TEST1';
          p1_sequence_owner dba_sequences.sequence_owner%TYPE;
          p1_sequence_name dba_sequences.sequence_name%TYPE;
      begin
          open c1;
              loop
                   fetch c1 into p1_sequence_owner, p1_sequence_name;
                   exit when c1%NOTFOUND;
                   dbms_output.put_line('grant select on '||p1_sequence_owner||'.'||p1_sequence_name||' to DW_READ_TEST1;');
              end loop;
          close c1;
      end;
      when I execute the above pl/sql, it works fine, I get the correct out put.

      The problem I faced is when I tried to create a procedure by inserting the above pl/sql as follows.
      create or replace procedure grant_procedure is 
            cursor c1 is select sequence_owner, sequence_name from dba_sequences where sequence_owner like 'TEST1';
            p1_sequence_owner dba_sequences.sequence_owner%TYPE;
            p1_sequence_name dba_sequences.sequence_name%TYPE;
      begin
            open c1;
                  loop
                       fetch c1 into p1_sequence_owner, p1_sequence_name;
                       exit when c1%NOTFOUND ;
                       dbms_output.put_line('grant select on '||p1_sequence_owner||'.'||p1_sequence_name||' to DW_READ_TEST1;');
                  end loop;
            close c1;
      end;
      when I try to create the above procedure, i get *"Warning: compiled but with compilation errors"*

      I don't know where I am going wrong. And toad is not showing which line is causing the error. Can some one please tell me what I am doing wrong?

      Thanks
        • 1. Re: procedure compilation error
          sb92075
          SHOW ERROR
          • 2. Re: procedure compilation error
            972227
            *"Warning: compiled but with compilation errors"*
            That is the error, I am not getting any thing but that in the output screen of Toad

            Edited by: 969224 on Apr 25, 2013 11:16 AM
            • 3. Re: procedure compilation error
              sb92075
              969224 wrote:
              *"Warning: compiled but with compilation errors"*
              That is the error, I am not getting any thing but that in the output screen
              you can lead some folk to knowledge, but you can't make them think.
              SQL> create or replace procedure grant_procedure is 
                    cursor c1 is select sequence_owner, sequence_name from dba_sequences where sequence_owner like 'TEST1';
                    p1_sequence_owner dba_sequences.sequence_owner%TYPE;
                    p1_sequence_name dba_sequences.sequence_name%TYPE;
              begin
                    open c1;
                          loop
                               fetch c1 into p1_sequence_owner, p1_sequence_name;
                               exit when c1%NOTFOUND ;
                               dbms_output.put_line('grant select on '||p1_sequence_owner||'.'||p1_sequence_name||' to DW_READ_TEST1;');
                          end loop;
                    close c1;
              end;  2    3    4    5    6    7    8    9   10   11   12   13  
               14  /
              
              Warning: Procedure created with compilation errors.
              
              SQL> show error
              Errors for PROCEDURE GRANT_PROCEDURE:
              
              LINE/COL ERROR
              -------- -----------------------------------------------------------------
              2/20     PL/SQL: SQL Statement ignored
              2/62     PL/SQL: ORA-00942: table or view does not exist
              3/25     PL/SQL: Item ignored
              3/25     PLS-00201: identifier 'DBA_SEQUENCES' must be declared
              4/24     PL/SQL: Item ignored
              4/24     PLS-00201: identifier 'DBA_SEQUENCES' must be declared
              8/18     PL/SQL: SQL Statement ignored
              8/32     PLS-00320: the declaration of the type of this expression is
                       incomplete or malformed
              
              10/18    PL/SQL: Statement ignored
              
              LINE/COL ERROR
              -------- -----------------------------------------------------------------
              10/59    PLS-00320: the declaration of the type of this expression is
                       incomplete or malformed
              
              SQL> 
              • 4. Re: procedure compilation error
                972227
                thanks sb92075, I was logged in as user system, and the table dba_sequences is owned by user sys. The create procedure works now.
                one more question, how do we find the error in Toad?
                • 5. Re: procedure compilation error
                  sb92075
                  SQL> desc all_errors
                   Name                                      Null?    Type
                   ----------------------------------------- -------- ----------------------------
                   OWNER                                     NOT NULL VARCHAR2(30)
                   NAME                                      NOT NULL VARCHAR2(30)
                   TYPE                                               VARCHAR2(12)
                   SEQUENCE                                  NOT NULL NUMBER
                   LINE                                      NOT NULL NUMBER
                   POSITION                                  NOT NULL NUMBER
                   TEXT                                      NOT NULL VARCHAR2(4000)
                   ATTRIBUTE                                          VARCHAR2(9)
                   MESSAGE_NUMBER                                     NUMBER