Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    29

    Exclamation Unanswered: Calling SP inside a Sp

    Hi all,
    I am an oracle 9 i user.
    I have one sp namely,DEV_SP_GET_STUD_TRANS_DET which accepts single parameter(studentID).
    I wanted to create another sp (DEV_SP_RUN_GETSTUD_TRANS_DET) to call the ,DEV_SP_GET_STUD_TRANS_DET under a loop. But unfortunately i dunno what is the syntax to call a sp inside another sp. can sumone help me.

    my code is like this :-


    CREATE OR REPLACE PROCEDURE "DEV_SP_RUN_GETSTUD_TRANS_DET"
    IS
    BEGIN

    DECLARE


    vStudentId VARCHAR2(20);


    CURSOR STUDENT_IN_LEDGER IS
    SELECT DISTINCT STUDENT_ID FROM TR_STUDENT_LEDGER;


    BEGIN


    OPEN STUDENT_IN_LEDGER;
    LOOP --**STUDENT_IN_LEDGER
    FETCH STUDENT_IN_LEDGER INTO vStudentId;
    EXIT WHEN STUDENT_IN_LEDGER%NOTFOUND;

    EXECUTE DEV_SP_GET_STUD_TRANS_DET (vStudentId);--<== this is where the error shown during compilation of sp

    END LOOP;
    COMMIT;
    CLOSE STUDENT_IN_LEDGER;



    END;


    END;
    Last edited by tommyboy1910; 05-14-07 at 07:26. Reason: typo error

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Just remove the EXECUTE. You only need the EXECUTE when you are in a SQL context; in a PLSQL block or a procedure, you don't need it.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Apr 2007
    Posts
    29

    Thumbs up

    Thanx for your help friend, it is working fine :d...i tried yesterday nite..sore for late reply ....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •