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!

Error in Creating Stored procedure

Discussion in 'General' started by sarvagya, Jul 10, 2012.

  1. sarvagya

    sarvagya Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Code (Text):

    create or replace
    PROCEDURE GetRECORDS(
    p_Records OUT sys_refcursor,
    p_Roll IN student.Roll%type :=NULL)
    IS
    BEGIN
    [B]SELECT * FROM student WHERE student.Roll=NVL(p_Roll,student.Roll)[/B]
    ORDER BY student.roll;
    END;
     
    ERROR is dere in the bolded line. Correct me where I am wrong!!
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    I dont understand what is
    .

    What are you trying to do.
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi sarvagya,

    Here you need to write into clause for select statement in that procedure.

    Code (Text):


    CREATE OR REPLACE
    PROCEDURE GETRECORDS(
    P_RECORDS OUT SYS_REFCURSOR,
    P_ROLL IN STUDENT.ROLL%TYPE :=NULL)
    IS
    L_ROLLNO NUMBER;
    L_SNAME VARCHAR2(100);
    BEGIN
    SELECT ROLL,SNAME INTO L_ROLLNO,L_SNAME FROM STUDENT WHERE STUDENT.ROLL=NVL(P_ROLL,STUDENT.ROLL)
    ORDER BY STUDENT.ROLL;
    END;

    procedure getrecords successfully compiled.
     
    You need to declare variables for the columns you need to get from select query and then you need to use into clause for your select query.

    Go through the procedure steps through below link.

    Create Procedure
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Kiran,

    That was just declaration of input variable with null as default value and this variable is assigned from roll column type of student table.
     
    kiran.marla likes this.
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Yeah you are correct. Thanks for correcting me .
     
    Bharat likes this.
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This query will work nicely only if the where clause of select statement returns single row. In case P_ROLL is NULL you could be in touble. The equalent statement will be as follows.

    Code (SQL):
    SELECT ROLL,SNAME INTO L_ROLLNO,L_SNAME FROM STUDENT WHERE STUDENT.ROLL=  STUDENT.ROLL
    ORDER BY STUDENT.ROLL;
    This will lead to "ORA-01422: exact fetch returns more than requested number of rows"

    In such cases, you can you SELECT ..... BULK COLLECT INTO . It all depends on you business logic.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Btw,

    Normal SELECT .. INTO clasue will give you "NO_DATA_FOUND" error in case where clause of select statement returns no records.
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi sarvagya,

    This is the perfect one which gives output if it contains that roll number what you have passed while running that procedure and it gives error message when you pass wrong roll number.

    Code (Text):

    PROCEDURE GETRECORDS(
    P_RECORDS OUT SYS_REFCURSOR,
    P_ROLL IN STUDENT.ROLL%TYPE :=NULL)
    IS
    L_ROLLNO NUMBER;
    L_SNAME VARCHAR2(100);
    CURSOR CUR_SEL(P_ROLL2 STUDENT.ROLL%TYPE) IS
    SELECT ROLL,SNAME FROM STUDENT WHERE STUDENT.ROLL = P_ROLL2;
    BEGIN
    FOR I IN CUR_SEL(P_ROLL) LOOP
    L_ROLLNO  := I.ROLL;
    L_SNAME  := I.SNAME;
    END LOOP;

    IF L_ROLLNO IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE(L_ROLLNO||','||L_SNAME);
    ELSE
    DBMS_OUTPUT.PUT_LINE('NO RECORD FOUND WITH THAT ROLL NUMBER');
    END IF;
    END;
    /

    Procedure getrecords successfully compiled
     
    To run the above procedure do as follows:


    Code (Text):

    DECLARE
    P_REF SYS_REFCURSOR;
    P_IN STUDENT.ROLL%TYPE:=2;
    BEGIN
    GETRECORDS(P_REF,P_IN);
    END;

    This gives output if it contains roll number 2. If it does not contains roll number as 2 then it gives error message as defined in the above procedure.

     
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Well.

    @ OP: What is the purpose of SysRefcursor in your code? Do you really want to give the result set as output ? Then have a look at Ref Cursors
     
  10. sarvagya

    sarvagya Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    HI Raj, you are Right I want to return result set as output. In my previuod code, i was missing open p_records dats y error is dere.
    Its done. Thanks you all.
    Code (Text):

    create or replace
    PROCEDURE GetRECORDS(
    p_Records OUT sys_refcursor,
    p_Roll IN student.Roll%type :=NULL)
    IS
    BEGIN
    open p_Records for
    SELECT * FROM student WHERE student.Roll=NVL(p_Roll,student.Roll)
    ORDER BY student.roll;
    END;
     
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Thanks for shring your working code.
     
  12. miteshaegis

    miteshaegis Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    You have to use order bay clause than you can not retrieve all field of table.

    Thanks!
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I have no idea what you mean by that statement; the procedure has an order by and CAN return all fields of the table as well as all matching rows.