1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Warning: Procedure created with compilation errors

Discussion in 'SQL PL/SQL' started by Puru, Nov 8, 2010.

  1. Puru

    Puru Forum Advisor

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    1 CREATE OR REPLACE PROCEDURE employee_details
    2 IS
    3 CURSOR emp_cur IS
    4 SELECT empno,ename,sal FROM emp;
    5 emp_rec emp_cur%rowtype;
    6 BEGIN
    7 FOR emp_rec in sales_cur
    8 LOOP
    9 dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
    10 || ' ' ||emp_cur.salary);
    11 END LOOP;
    12* END;
    SQL> /

    Warning: Procedure created with compilation errors.

    I have created a procedure, but Iam getting compliation errors. How to solve this....
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: Procedures

    You created emp_cur cursor and using sales_cur which is not created. here is the procedure.
    If you are using FOR loop , no need to declare variables.

    CREATE OR REPLACE PROCEDURE employee_details
    IS
    CURSOR sales_cur IS
    SELECT empno,ename,sal FROM emp;
    BEGIN
    FOR emp_rec in sales_cur
    LOOP
    dbms_output.put_line(emp_rec.first_name || ' ' ||emp_rec.last_name
    || ' ' ||emp_rec.salary);
    END LOOP;
    END;
     
  3. Puru

    Puru Forum Advisor

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Thank u, I got the answer. In the mean time, let me know why no need to declare variables, if you are using FOR loop.......
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    That is the functionality provided by Oracle Corp.
    For will take care of Declaring the variable, opening the cursor, fetching the data into variable and closing the cursor.
    It is very easy to use compared to other loops.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Use the tools provided by Oracle, such as 'show errors':

    Code (SQL):
     
    SQL> CREATE OR REPLACE PROCEDURE employee_details
      2  IS
      3   CURSOR emp_cur IS
      4   SELECT empno,ename,sal FROM emp;
      5   emp_rec emp_cur%rowtype;
      6   BEGIN
      7   FOR emp_rec IN sales_cur
      8   LOOP
      9   dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name || ' ' ||emp_cur.salary);
     10   END LOOP;
     11  END;
     12  /
    Warning: PROCEDURE created WITH compilation errors.
    SQL>
    SQL> SHOW errors
    Errors FOR PROCEDURE EMPLOYEE_DETAILS:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    7/2      PL/SQL: Statement ignored
    7/17     PLS-00201: identifier 'SALES_CUR' must be declared
    SQL>

     
    This is your first coding error. Others also exist, such as using emp_cur in the dbms_output statement where emp_rec should be used and using non-existent column names (first_name, last_name, salary) rather than those you selected in the cursor (empno, ename, sal). The declaration of emp_rec, as already noted, will be ignored as the FOR loop construct declares the loop record variable automatically. The fully corrected procedure is shown below:

    Code (SQL):
     
    SQL> CREATE OR REPLACE PROCEDURE employee_details
      2  IS
      3   CURSOR emp_cur IS
      4   SELECT empno,ename,sal FROM emp;
      5   BEGIN
      6   FOR emp_rec IN emp_cur
      7   LOOP
      8   dbms_output.put_line(emp_rec.empno || ' ' ||emp_rec.ename || ' ' ||emp_rec.sal);
      9   END LOOP;
     10  END;
     11  /
    PROCEDURE created.
    SQL>
    SQL> SHOW errors
    No errors.
    SQL>