Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    houston
    Posts
    22

    Unanswered: IN Operator in Oracle ref cursor

    guys, ive got a puzzle here.
    this ref cursor called from within a package works just fine until i try to pass multiple values for one of the parameters.
    if i cut out the sql select statment and hard code the input variables, it's just fine.
    the code is below but where the problem is is:
    AND di.interval_type IN (i_Interval)
    if i pass one interval only - it's fine, but include more than one, and nothing returns.
    is this something that shouldnt be done in a ref cursor??? do i have to keep the input params one to a customer???
    thanks for any help on this
    rik





    PROCEDURE Int_Occurences_Group(
    i_OrgOne IN ats_adm.empl_profile.org_lvl_1%TYPE,
    i_OrgTwo IN ats_adm.empl_profile.org_lvl_2%TYPE,
    i_dateFrom IN ats_adm.daily_interval.balance_date%TYPE,
    i_dateTo IN ats_adm.daily_interval.balance_date%TYPE,
    i_Interval IN ats_adm.daily_interval.interval_type%type,
    o_cursor OUT t_cursor)
    IS

    BEGIN
    OPEN o_cursor FOR
    SELECT ep.EMPL_ID, ep.LAST_NAME, ep.FIRST_NAME, di.interval_type, di.START_TIME, it.INTERVAL_DESC,
    di.BALANCE_DATE, di.EMPL_ID, ep.ORG_LVL_1,ep.ORG_LVL_2, ep.ORG_LVL_3, ep.ORG_LVL_4, ep.ORG_LVL_5, di.END_TIME, di.HOURS_ADJ/60, di.OVERTIME_2/60, di.DOUBLETIME_6/60
    FROM empl_profile ep,
    daily_interval di,
    interval_type it
    WHERE ep.EMPL_ID=di.EMPL_ID
    AND di.interval_type=it.interval_type (+)
    AND ep.org_lvl_1= UPPER(i_OrgOne)
    and ep.org_lvl_2 = UPPER(i_OrgTwo)
    AND di.BALANCE_DATE>= i_dateFrom
    AND di.BALANCE_DATE<= i_dateTo
    AND di.interval_type IN (i_Interval)
    ORDER BY ep.ORG_LVL_4, di.interval_type, ep.EMPL_ID;

    END Int_Occurences_Group;

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565

  3. #3
    Join Date
    Oct 2003
    Location
    houston
    Posts
    22

    table of type

    that makes perfect sence. i'll give it a shot. thanks for such a quick reply
    rik

  4. #4
    Join Date
    Oct 2003
    Location
    houston
    Posts
    22

    IN Operator in Oracle ref cursor

    im getting :

    PLS-00642: local collection types not allowed in SQL statements


    it's not allowing me to use that at all in any fashion

Posting Permissions

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