Results 1 to 5 of 5

Thread: ref cursor

  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: ref cursor

    Hi,
    Below is the complete code and description about my problem:
    CREATE OR REPLACE PACKAGE package_ex IS
    TYPE ref_cursor IS REF CURSOR;
    PROCEDURE validate(i_name IN customer.Name%TYPE,
    i_dealer_number IN customer.dealer%TYPE,
    o_info_cur OUT ref_cursor) IS
    v_info_cur ref_cursor;
    v_cur ref_cursor;
    BEGIN
    OPEN v_info_cur FOR
    SELECT Name,
    sp_customer,
    dealer_name,
    po,
    shipto,
    shiptoname,
    shiptoaddr,
    channel,
    site
    FROM parent_view_new
    WHERE Name= i_name AND dealer_name=i_dealer_number
    AND rownum = 1;

    fetch v_info_cur INTO v_cur; => after this line, it catch exception

    IF(v_info_cur%ROWCOUNT<=0) THEN
    OPEN v_info_cur FOR
    SELECT Name,
    sp_customer,
    dealer_name,
    po,
    shipto,
    shiptoname,
    shiptoaddr,
    channel,
    site
    FROM parent_view
    WHERE Name= i_name AND dealer_name=i_dealer_number
    AND rownum = 1;
    END IF;
    o_info_cur := v_info_cur;

    EXCEPTION
    WHEN OTHERS THEN
    ---some text for exception----
    END validate;
    END;
    I want to execute the first select query from the "parent_view_new" view if it does not have data for the given input, second select query should execute.
    The problem is even there is data in the first select query, it always shows the rowcount as "0".And also after the "fetch" statement, the next line of execution passes to the "Exception" statement.
    Whats wrong in the above procedure? Please help..

    Thanks.
    Last edited by DurgaManikandan; 01-16-12 at 04:48. Reason: MISSED A LINE

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    So, for the start, do not try to "handle" exceptions in EXCEPTION WHEN OTHER clause (remove it preferably forever), as it just hides the exception reason - you would post it if you knew it, would not you?

    What is the definition of REF_CURSOR data type? It cannot be used for storing cursor and its result set at the same time. So, I doubt that the package body will even compile, so you would be able to "execute" it (whatever it exactly is).

    Maybe you may benefit from studying some examples on using cursors. Here is the one I found at first: http://www.oracle-base.com/articles/...Recordsets.php

    Or, better, read about it in PL/SQL User's Guide and Reference for your Oracle version. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

  3. #3
    Join Date
    Jan 2012
    Posts
    2

    ref cursor

    Hi,
    The exception I am getting is, ORA-24338:statement handle not executed. When I searched, this exception will occur "when fetching a cursor before executing the query" was the description given for this exception.
    before using the above mentioned way of using the cursor's rowcount, I used count(*) followed by the select query from parent_view_new but it took long time to execute this query, since the count will be more than 40thousand records.
    Then I used the cursor's fetch and rowcount to know whether the cursor has atleast 1 row but the rowcount is always 0(zero). When I execute the same query separately for the same input i found 2 rows.

    Thanks.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    sorry, from your posts I have no idea what exactly you did and what was the package content when you got that exception.
    Because of that ROWNUM=1 condition, each query returns at most one row, so I also do not understand "i found 2 rows" remark.
    Additionally, you are aware, that after FETCHing the row inside the procedure, it will not be available for the caller?
    Anyway, if it is your real requirement (return row(s) from PARENT_VIEW_NEW; if there are none, return row(s) from PARENT_VIEW) you should stop making those strange PL/SQL attempts and express this condition directly in SQL:
    Code:
    OPEN v_info_cur FOR
      SELECT ... FROM parent_view_new WHERE ... -- your first query
      union all
      SELECT ... FROM parent_view WHERE ... -- your second query>
        AND NOT EXISTS (
            SELECT ... FROM parent_view_new WHERE ... -- your first query
          )

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this for one cursor?
    (I'm not certain about place of "rownum = 1")
    Code:
    SELECT NVL(n.Name        , p.Name       ) Name
         , NVL(n.sp_customer , p.sp_customer) sp_customer
         , NVL(n.dealer_name , p.dealer_name) dealer_name
         , NVL(n.po          , p.po         ) po
         , NVL(n.shipto      , p.shipto     ) shipto
         , NVL(n.shiptoname  , p.shiptoname ) shiptoname
         , NVL(n.shiptoaddr  , p.shiptoaddr ) shiptoaddr
         , NVL(n.channel     , p.channel    ) channel
         , NVL(n.site        , p.site       ) site
     FROM  dual
     LEFT  OUTER JOIN
           parent_view_new n
       ON  n.Name        = i_name
       AND n.dealer_name = i_dealer_number
     LEFT  OUTER JOIN
           parent_view     p
       ON  n.Name       IS NULL
       AND p.Name        = i_name
       AND p.dealer_name = i_dealer_number
     WHERE rownum = 1
    ;

Posting Permissions

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