Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Question Unanswered: for loop in cursor

    Hi

    I have a dynamic query which are taking input values from procedure dynamically. i want to take the whole data into a cursor and used tat cursor in for loop.so tat i can work on particular row but it is giving me error PLS-00221: 'C_10' is not a procedure or is undefined when i m using





    vsql:= 'select COUNT(CASE WHEN T1.SER=''I'' THEN T1.CLIENTID ELSE NULL END) as "checked",
    from t_ps T1 where T1.SERREQID IS NOT NULL';

    IF VADDRESSTYPE IS NOT NULL AND VADDRESSTYPE<> '*' THEN

    vsql:=vsql || 'AND T1.ADDRESSTYPE='''||VADDRESSTYPE||'';

    END IF;

    IF VFROMDATE IS NOT NULL THEN
    vsql:=vsql ||' AND trunc(T1.MODIFIEDDATE) BETWEEN TRUNC('''||VFROMDATE||''') AND TRUNC('''||VTODATE||''')''';
    END IF;

    open C_10 for vsql


    "for i in C_10 loop
    end loop"

    here c_10 is a refcursor .


    how can i used it ??
    Last edited by VAIBHAV174U; 11-12-08 at 07:20.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Please post more of the code - at the moment we have to guess what "C_10" actually is...

  3. #3
    Join Date
    Nov 2008
    Posts
    26
    Hi

    I have a dynamic query which are taking input values from procedure dynamically. i want to take the whole data into a cursor and used tat cursor in for loop.so tat i can work on particular row but it is giving me error PLS-00221: 'C_10' is not a procedure or is undefined when i m using





    vsql:= 'select COUNT(CASE WHEN T1.SER=''I'' THEN T1.CLIENTID ELSE NULL END) as "checked",
    from t_ps T1 where T1.SERREQID IS NOT NULL';

    IF VADDRESSTYPE IS NOT NULL AND VADDRESSTYPE<> '*' THEN

    vsql:=vsql || 'AND T1.ADDRESSTYPE='''||VADDRESSTYPE||'';

    END IF;

    IF VFROMDATE IS NOT NULL THEN
    vsql:=vsql ||' AND trunc(T1.MODIFIEDDATE) BETWEEN TRUNC('''||VFROMDATE||''') AND TRUNC('''||VTODATE||''')''';
    END IF;

    open C_10 for vsql


    "for i in C_10 loop
    end loop"

    here c_10 is a refcursor .


    how can i used it ??

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Right, now I see your problem (even though I still can't see the type of variable c_10). You can't use a cursor FOR loop with a ref cursor, you need to do it like this example:

    Code:
      1  declare
      2     v_sql varchar2(2000);
      3     c_10  sys_refcursor;
      4     r_emp emp%rowtype;
      5  begin
      6     v_sql := 'select * from emp';
      7     open c_10 for v_sql;
      8     loop
      9        fetch c_10 into r_emp;
     10        exit when c_10%notfound;
     11        dbms_output.put_line(r_emp.ename);
     12     end loop;
     13* end;
    SQL> /
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    FORD
    MILLER
    PARAG
    AVINASH
    BINNSY
    OPERATOR
    
    PL/SQL procedure successfully completed.

Posting Permissions

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