Results 1 to 4 of 4

Thread: cursor

  1. #1
    Join Date
    Mar 2010
    Posts
    14

    Unanswered: cursor

    Hi all,

    I have a difficult problem here (at least for me)

    I'm working with a RefCursor Procedure for filling a DataSet in ADO.NET. The problem is that the select query that fills the cursor works perfectly within SqlDeveloper, but when i fill my DataSet, it has 0 rows

    the procedure is this:
    Code:
    CREATE OR REPLACE PACKAGE FETCH_HISTORY AS 
    	TYPE t_cursor IS REF CURSOR ; 
    	Procedure open_join_cursor1 (mood_id_ IN NUMBER, io_cursor IN OUT t_cursor); 
    END FETCH_HISTORY;
    /
    CREATE OR REPLACE PACKAGE BODY FETCH_HISTORY AS
    Procedure open_join_cursor1 (mood_id_ IN NUMBER, io_cursor IN OUT t_cursor) 
    IS 
    	v_cursor t_cursor; 
    BEGIN 
    	IF mood_id_ is not null 
            THEN
                 OPEN v_cursor FOR
                 with random_tracks as(
                    select id track_id,row_number() over(order by dbms_random.random) nr
                    from tracks 
                    where id not in (select track_id from tracks_moods where mood_id = mood_id_)
                 ),
                  tracks2 as(
                  select track_id, null as loved
                  from random_tracks
                  where nr<=10
                 ),
                 withtags as(
                    select tag_id, loved, t.track_id
                    from tracks2 t
                    join tags_tracks tt on t.track_id=tt.track_id
                 )
                  select w1.tag_id as node1, w2.tag_id as node2, w1.track_id as track_id, w1.loved as loved
                  from withtags w1
                  join withtags w2 on w1.track_id = w2.track_id
                  where w1.tag_id < w2.tag_id;
            END IF;
    	io_cursor := v_cursor; 
    END open_join_cursor1; 
    END FETCH_HISTORY;
    /
    The above query gives the correct rows in SqlDeveloper, but not within the DataSet (0 rows)

    If I however drop the join in the select query, so this:

    Code:
    select w1.tag_id as node1, w1.track_id as track_id, w1.loved as loved
    from withtags w1;
    Then the dataset is filled correctly. So the problem must be that join.

    Any of you that has an idea about this, why doesn't it work with the join?


    Thanks in advance!
    Chielus

  2. #2
    Join Date
    Mar 2010
    Posts
    14
    appearently the problem is the first CTE. It has an order by dbms_random.random. That CTE however will be self-joined. Within SqlDeveloper that's no problem, the results of the CTE will be exact the same, but appearently when I execute the procedure with ADO.NET It doesn't give the same results, so the auto-join will give 0 rows

    how can i solve this problem? I want it to be random and self-joinable

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Use the sample clause which returns random data.

    select emp_nr from emp sample(1);


    Will return 1&#37; of the file randomily. This works in oracle 9i and above
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Mar 2010
    Posts
    14
    actually this thread continues into:

    http://www.dbforums.com/oracle/16564...-joinable.html

    i made the new thread because of the bad title and explanations on this one

Posting Permissions

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