syntax for calling a procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: syntax for calling a procedure

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    I am trying to see if I can use something like the following code sample from the Oracle PL/SQL documentation, but I can't figure out the syntax for how to execute this procedure...what would the first parameter look like when calling it? Here it is:

    CREATE PACKAGE emp_data AS
    TYPE GenericCurTyp IS REF CURSOR;
    TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice NUMBER);
    END emp_data;

    CREATE PACKAGE BODY emp_data AS
    PROCEDURE open_emp_cv
    (emp_cv IN OUT EmpCurTyp,
    choice NUMBER) IS
    BEGIN
    IF choice = 1 THEN
    OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
    ELSIF choice = 2 THEN
    OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
    ELSIF choice = 3 THEN
    OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
    END IF;
    END open_emp_cv;
    END emp_data;

    Could you call this directly from SQL*PLus, like:
    exec emp_data.open_emp_cv(????,2); ??

    Or would you have to have already selected a row (or multiple rows) from emp and call it from there???

    Thanks in advance



  2. #2
    Join Date
    Jan 2001
    Posts
    153
    Hi

    U have 2 parameters to pass for the procedure and the one is the REF CURSOR..U declare a strong cursor

    declare
    var1 empdata.GenericCurTyp;

    begin

    --call the procedure here with the first parameter as var1;
    after the execute u open the parameter v1 using
    open <> for <> and get the data..............

    end;


    Vijay.s

Posting Permissions

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



Click Here to Expand Forum to Full Width