Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: subselect a cursor passed into PL/SQL

    Hi-

    I'm having trouble with a PL/SQL function that receives a cursor as a parameter from which I want to do a join. The reason I want to do this is that the cursor represents an arbitrary set of queries that gives me a filtered set of employees that I want to return aggregate information about in a uniform way. I use this function on a number of different sets. The function is something like this (simplified here):

    create or replace package types
    as
    type cursorType is ref cursor;
    end;
    /

    create or replace function myFunction( p_employees in types.cursorType)
    return types.cursorType
    as
    v_return_cursor types.cursorType;

    begin

    open v_return_cursor
    for select emp.name, sum(evt.event)
    from p_employees emp
    right join events evt
    on emp.name = evt.perpetrator
    group by emp.name;

    return v_return_cursor;

    end;
    /

    the error i receive is:

    PL/SQL: ORA-00942: table or view does not exist

    for the line with the reference to the cursor.

    Is it not possible to do use a cursor as if it was a subselect?

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    You do not select from a cursor you fetch (or some such thing). Check out http://download-west.oracle.com/docs...6_ora.htm#1449 or other Oracle doc on cursors
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    Thanks-

    I'm familiar with fetching, but the issue is that I'm trying to join the result set that I passed in as a parameter with another table; the results of which I hope to return in a cursor. If I iterate through the cursor I passed in and fetch out its elements and then join them to the other table 1 row at a time, how would I insert them into the cursor I want to return?

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Yes, that would be a good question and I do not have a good answer.

    My brute force thinking would be a temp table and another cursor to access the temp and your other tables. But I am sure there is a more elegant way. I hope I find out to.
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    BTW, there have been several ref cursor questions over the last months. You might want to search the threads and see if an answer pops.
    NOTE: Please disregard the label "Senior Member".

  6. #6
    Join Date
    Dec 2003
    Posts
    14
    Thanks-

    I did do a quick search, but nothing came up that looked quite right.

    Your 'brute force' suggestion is where I've been heading, but it just doesn't seem like the most efficient way to do it (i hope!). It may have to be that way though- as I can't do the single row fetching even if I wanted to, as I'm dumping the results into aggregate functions.

Posting Permissions

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