PLS-00394 and PLS-00225

Though i have two years of experience as a software engineer, i have never ever written a oracle procedure, function etc.... These things are handled by my onsite client. So i didn't get the chance to write those. Now-a-days i have find time to read. I use this time to read those things, As usual i would like to share the learning with you.

My first oracle block:

Assume that emp is the table which has two columns. 1. employee_id and 2. employee_name

SQL> declare
2 employee_id emp.EMPLOYEE_ID%Type;
3 cursor emp_cursor is select * from emp;
4 begin
5 open emp_cursor;
6 fetch emp_cursor into employee_id;
7 dbms_output.put_line(employee_id);
8 close emp_cursor;
9 end;
10 /

Good. I love to get the error, Then only i can learn too many things.

fetch emp_cursor into employee_id;
ERROR at line 6:ORA-06550: line 6, column 1:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored

Now we will analyse the code.

First thing is declaring a cursor ( cursor emp_cursor is select * from emp ) which fetch all the columns of the emp table.

Now i want to fetch the column into employee_id ( fetch emp_cursor into employee_id ). But here emp_cursor has two columns, the first column Employee_id of emp is assigned to employee_id but the Employee_name is not yet assigned to any value. This throws PLS-00394: wrong number of values in the INTO list of a FETCH statement

So PL/SQL Statement ignored.

This is the modified code for the above program,

SQL> declare
2 employee_id emp.EMPLOYEE_ID%Type;
3 cursor emp_cursor is select employee_id from emp;
4 begin
5 open emp_cursor;
6 fetch emp_cursor into employee_id;
7 dbms_output.put_line(employee_id);
8 close emp_cursor;
9 end;
10 /

Here i have selected only employee_id as cursor.

Note :
I got this PLS-00225: subprogram or cursor 'EMP_CURSOR' reference is out of scope error when i tried to call the fetch emp_cursor.employee_id into employee_id; Cursor is nothing more than a pointer to a piece of memory

To resolve this error we have to declare a variable which is same as cursor's rowtype

SQL> declare
2 employee_id emp.EMPLOYEE_ID%Type;
3 cursor emp_cursor is select employee_id from emp;
4 emp_val emp_cursor%ROWTYPE;
5 begin
6 open emp_cursor;
7 fetch emp_cursor into emp_val;
8 dbms_output.put_line(emp_val.employee_id);
9 close emp_cursor;
10 end;
11 /

By doing this we dont mind about how many columns will be fetched by cursor. If multiple columns are fetched then we would be able to reference them via emp_val variable.