CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
nikhil
nikhil

Reputation: 9385

Compilation error when creating an elementary plsql procedure

I'm just learning plsql and I'm having problems running simple sql queries in procedures. I wanted to write a procedure that displays all the records from a table.

create or replace procedure display_all_students
as
    begin
        dbms_output.put_line('Listing all the student records');
        select * from student;
    end;

I get this as a result : Warning: Procedure created with compilation errors.

What am I missing, from what I understand is that plsql is an extension to sql is there some other way of achieving this instead?

Updated code, I'm still facing the same problem. Is there a way by which we can debug these errors one by one?

-- procedure to display the table
create or replace procedure display_all_students
as
  -- declarations
  cursor cur_student is
    select * from student;
  student_record student%rowtype;
 begin
    dbms_output.put_line('Listing all the student records');
    for student_record in cur_student
    loop
      dbms_output.put_line(student_record);
    end loop;
  end;

Upvotes: 0

Views: 931

Answers (2)

Sodved
Sodved

Reputation: 8588

You cannot just select * from student;. That doesn't mean anything, what does it do with the returned data?

Instead you need to either create a cursor which selects ... from student or SELECT columns INTO variables FROM student;.

So for you problem you need to create a cursor which selects from student and then loop through it and output each row. For example:

create or replace procedure display_all_students
as
    CURSOR cur_student IS
    SELECT student_id, first_name, last_name FROM student;    
begin
    dbms_output.put_line('Listing all the student records');
    FOR rec IN cur_student
    LOOP
        dbms_output.put_line( 'ID[' || rec.student_id || '] Name: '
            || rec.first_name || ' ' rec.last_name);
    END LOOP;
end;

The same thing written the long way is probably better to learn as a beginner as it teaches you the various aspects of cursors etc. This is the same code written the long way.

create or replace procedure display_all_students
as
    CURSOR cur_student IS
    SELECT student_id, first_name, last_name FROM student;    
    -- Host Variable to store cursor result.
    rec cur_student%ROWTYPE;
begin
    dbms_output.put_line('Listing all the student records');
    OPEN cur_student;
    LOOP
        FETCH cur_student INTO rec;
        EXIT WHEN cur_student%NOTFOUND;
        dbms_output.put_line( 'ID[' || rec.student_id || '] Name: '
            || rec.first_name || ' ' rec.last_name);
    END LOOP;
    CLOSE cur_student;
end;

The FOR variable IN cursor syntax takes care of some things for you:

  • You don't have to decalre the Host Variable
  • You don't have to OPEN the cursor
  • You don't have to check when you've reached the end of the data
  • You don't have to CLOSE the cursor

The difference is syntax only. The actual execution and performance of both is almost identical.

Upvotes: 3

William
William

Reputation: 121

Bear in mind that your PLSQL is running on the server; dbms_output doesn't write text to your screen, it dumps it to a table on the server that your client knows it's supposed to look at after control is returned. Just a select on its own would pull data back and have nowhere to put it (conceptually) and thus the compiler complains.

Typically you would want to do something with the data - store it somewhere, iterate over it, use it to update something else, etc..

e.g.:

---
for i in (select * from student)
loop
   dbms_output.put_line(i.name);
end loop;
---

Upvotes: 2

Related Questions