Results 1 to 6 of 6

Thread: Cursor

  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: Cursor

    Hi

    I am using a cursor with a select statement from different tables.below is my cursor,

    OPEN c_Txt FOR

    SELECT PA.TEST_ID, EMP.EMP_TYP, EMP.EMP_FMLY_ID,EMP.EMP_SRGT_ID,EMP.EMP_TXT,EMP.FE B_CD,
    PQ.UOM_TYP,PQ.MIN_EMP_QTY,PQ.MAX_EMP_QTY,PQ.PER_UO M_QTY
    FROM EMP_TEST PA
    INNER JOIN EMP EMP
    ON (EMP.EMP_SRGT_ID = PA.EMP_SRGT_ID)
    AND (SYSDATE BETWEEN EMP.EMP_EFF_DT AND EMP.EMP_END_DT)
    LEFT OUTER JOIN STYLE STYLE
    ON (STYLE.STYLE_ID = EMP.STYLE_ID)
    AND (SYSDATE BETWEEN STYLE.STYLE_EFF_DT AND STYLE.STYLE_END_DT)
    LEFT OUTER JOIN EMP_FMLY PFMLY
    ON (EMP.EMP_FMLY_ID = PFMLY.EMP_FMLY_ID)
    AND (SYSDATE BETWEEN PFMLY.EMP_FMLY_EFF_DT AND PFMLY.EMP_FMLY_END_DT)
    INNER JOIN V_CATLG_EMP CP
    ON (EMP.EMP_SRGT_ID = CP.EMP_SRGT_ID)
    AND (CP.CATLG_ID = 20)
    AND (SYSDATE BETWEEN CP.CATLG_EMP_EFF_DT AND CP.CATLG_EMP_END_DT)
    INNER JOIN EMP_EMP_QTY PPQ
    ON (PPQ.EMP_SRGT_ID = EMP.EMP_SRGT_ID)
    AND (SYSDATE BETWEEN PPQ.EMP_EMP_QTY_EFF_DT AND PPQ.EMP_EMP_QTY_END_DT)
    INNER JOIN EMP_QTY PQ
    ON (PPQ.EMP_QTY_ID = PQ.EMP_QTY_ID)
    AND (SYSDATE BETWEEN PQ.EMP_QTY_EFF_DT AND PQ.EMP_QTY_END_DT)
    WHERE (PA.TEST_ID IN ('1','2'))
    AND (SYSDATE BETWEEN PA.EMP_TEST_EFF_DT AND PA.EMP_TEST_END_DT)
    ORDER BY PA.TEST_ID';

    This cursor declared as (c_Txt out ref_cursor).

    I need to fetch only EMP_SRGT_ID from the above cursor,

    I have used FETCH but it is expecting to fetch all the output from the cursor.

    can use only OPEN FOR with the cursor since the cursor has to be one of the out parameter of my procedure.

    Please help me to fetch only one output from a cursor.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you need only EMP_SRGT_ID, why do you select all those columns? Select only the one you are interested in.

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    Hi,
    Thanks for the reply... I need all the columns. The columns are used in the application when the cursor returned. But need to do some calculations in the procedure based on the EMP_SRGT_ID column value.

    Thanks

  4. #4
    Join Date
    Aug 2011
    Posts
    4
    hi
    Is there any update on this. Need urgent.
    Thanks

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Instead of an open, use a cursor loop. much easier to use and full access to all the objects from the select.

    for pnt in (select emp_cd,emp_id from emp order by employee_id) loop
    if pnt.emp_cd = 'ABC' then
    ...
    end if;
    end loop;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Aug 2011
    Posts
    4
    Hi,

    This works for me Thanks. But i am getting message like

    <message> Number of bound columns exceeds the number of result columns.</message> in the application.

    any idea on this?

Posting Permissions

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