Results 1 to 6 of 6

Thread: urgent help..

  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: urgent help..

    i need to pass a record returned by a cursor as input parameter to a function. i do not want to make the cursor declaration global. so how do i declare it in the function declaration.

    function f is
    cursor c1 is select * from temp;
    c1_rec c1%ROWTYPE;
    begin
    OPEN c1;
    loop
    fetch c1 into c1_rec;
    if (c1%notfound)
    then
    exit
    else
    f2(c1_rec);
    end if;
    end loop;
    end;

    function f2(p_rec IN ???????)
    begin
    ...
    end;

    what shd i write in place of ???????
    i do not want to make my cursor declaration global ..

    Thanks in Advance
    R

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Exactly what you used as an argument in the first function.
    "c1_rec" represents "select * from temp", right? Therefore, "?????" should be "temp%rowtype".
    Code:
    create or replace procedure f2 (p_rec in emp%rowtype) is
    begin
      null;
      dbms_output.put_line(p_rec.ename);
    end;
    /
    
    create or replace procedure f1 is
      cursor c1 is select * from emp;
    begin
      for c1r in c1 loop
        if c1%notfound then exit;
        else f2(c1r);
        end if;
      end loop;
    end;
    /
    
    execute f1;
    This example will show all the employees in the "emp" table.

    P.S. Do you really want to create a function? It requires a return value (your syntax doesn't have it).
    Last edited by Littlefoot; 11-23-04 at 17:10.

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    That would help me if I was selecting all the colums but I will be selecting few columns from the table. So in that case how should I declare it ?

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You WERE selecting all the columns in the example you provided. However, never mind that ... Why do you not want to use global variables? You could create a record and put it into the package and it'll work.

    But, if you really don't want to do it, perhaps you could split result you got by the cursor into several variables that will also be parameters in another procedure / function. For example:
    Code:
    CREATE OR REPLACE PROCEDURE f2 
      (p_no IN EMP.empno%TYPE, p_name IN EMP.ename%TYPE)
    IS
    BEGIN
      NULL;
      dbms_output.put_line(p_no ||' '|| p_name);
    END;
    /
    
    CREATE OR REPLACE PROCEDURE f1 IS
      CURSOR c1 IS SELECT empno, ename FROM EMP;
      l_no   EMP.empno%TYPE;
      l_name EMP.ename%TYPE;
    BEGIN
      FOR c1r IN c1 LOOP
        IF c1%NOTFOUND THEN 
    	   EXIT;
        ELSE 
    	   l_no   := c1r.empno;
    	   l_name := c1r.ename;
    	   f2(l_no, l_name);
        END IF;
      END LOOP;
    END;
    /
    
    EXECUTE f1;

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You could even simplify that, to
    Code:
    CREATE OR REPLACE PROCEDURE f1 AS
    	CURSOR c IS SELECT empno, ename FROM EMP;
    BEGIN
    	FOR r IN c LOOP
    		f2(r.empno, r.ename);
    	END LOOP;
    END;
    /

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So true! Thank you, William!

Posting Permissions

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