Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    22

    Angry Unanswered: Problem with REF cursor

    Hi there !

    Please tell me what is wrong with my package and package body. I am using ref cursor.

    ---------

    CREATE OR REPLACE PACKAGE UPDATE_STYLE_DATA IS

    TYPE cnvdpt_cur_typ IS REF CURSOR
    RETURN conversion.cnvdpt@dmscu_link%ROWTYPE;

    PROCEDURE UPDATE_STYLE;

    END UPDATE_STYLE_DATA;




    CREATE OR REPLACE PACKAGE BODY UPDATE_STYLE_DATA AS

    PROCEDURE UPDATE_STYLE AS

    -- Declare a ref cursor variable
    cnvdpt_cv cnvdpt_cur_typ;

    -- Declare a counter
    l_count PLS_INTEGER := 0;

    BEGIN

    IF NOT cnvdpt_cv%ISOPEN THEN
    OPEN cnvdpt_cv FOR SELECT * FROM conversion.cnvdpt@dmscu_link;
    END IF;

    FOR cnvdpt_rec IN cnvdpt_cv LOOP
    EXIT WHEN cnvdpt_cv%NOTFOUND;
    UPDATE style
    SET class_code = cnvdpt_rec.cdndpt
    || LPAD(cnvdpt_rec.cdndpt,3,'0')
    || LPAD(cnvdpt_rec.cdncls,3,'0')
    WHERE class_code = cnvdpt_rec.cdocls;

    --increase the couter by 1
    l_count := l_count + 1;

    -- commit after every 20000 rows
    IF MOD(l_count, 20000) = 0 THEN
    COMMIT;
    END IF;
    END LOOP;

    END UPDATE_STYLE;

    END UPDATE_STYLE_DATA;

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    IF NOT cnvdpt_cv%ISOPEN THEN 
    does this line actually work??

    anyways, post the exact error you are getting and hopefully the linenumber too.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2004
    Posts
    22
    Package spec is created successfully but
    package body is not !

    Warning: Package Body created with compilation errors.

    SQL> show errors
    Errors for PACKAGE BODY UPDATE_STYLE_DATA:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    17/3 PL/SQL: Statement ignored
    17/21 PLS-00221: 'CNVDPT_CV' is not a procedure or is undefined

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I'm confused as to what you are attempting to do.

    You declare your refcursor in the same object that you call it in.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2004
    Posts
    22
    I got it ! Here is an example from reference manual. I was just trying to use a ref cursor instead of a simple cursor.

    -------------------------
    Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, the following cursor FOR loop is illegal:

    DECLARE
    CURSOR emp_cur IS SELECT * FROM emp; -- static cursor
    TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
    emp_cv EmpCurTyp; -- cursor variable
    BEGIN
    ...
    FOR emp_rec IN emp_cv LOOP ... -- illegal
    END;

Posting Permissions

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