create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section
from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above??
You're on 11g. You don't need to define variables or a cursor. Let Oracle do the heavy lifting for you.
create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER)
AS
BEGIN
select Name,Rollno
into outname,outroll
from emp;
END;
This will raise an error if EMP has more than one row. There are various ways of fixing that. For instance, using ROWNUM to artificially limit the result set. Or taking EMP_ID as an input parameter to select the desired record. It depends on whether this is a toy example or the start of a real world API.
"how to use ROWNUM in above eg?"
ROWNUM is a pseudo-column, and is covered in the documentation. However, for the sake of completeness, we can use it to restrict a result set like this:
select Name,Rollno
from emp
where rownum <= 1;
