output record sets with cursors
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: output record sets with cursors

  1. #1
    Join Date
    Feb 2003
    Posts
    6

    output record sets with cursors

    I have a dimela where my PL/SQL script does not return any rows. Please understand that I am not running this in SQL PLUS , so I cannot use DBMS_OUTPUT package and I cannot select into variables because I am actually migrating data to SQL SERVER from Oracle using DTS. Here is my script:

    DECLARE
    current_date Date;
    TYPE rst_refcur IS REF CURSOR;
    p_rec rst_refcur;
    BEGIN
    current_date := SYSDATE;
    OPEN p_rec FOR
    select a.wprptsta, a.wpitem, a.wpitmseg, a.mandisdv, a.conclass, a.wpwkmix, a.itsegman,
    a.wpitemno, a.contydot, a.geodist,a.wpitstat, a.localnam, a.locatnfm, a.locatnto, a.isghstdt,
    a.effdate, a.efftime, a.ffisyrcp, g.itmseg gis_itmseg from wpguser.wpgtbl80 a, wpguser.gis_wpgtbl80 g
    where a.geodist = '04'and a.isghstdt = current_date
    and a.wprptsta = g.wprptsta(+) and a.wpitem = g.wpitem(+) and a.wpitmseg =g.wpitmseg(+);
    END;

    This gives me a No data returned error, which I can see why, but since I am crossing platforms from my source (Oracle) and my destination (SQLServer) through DTS, I don't see how I can define it.
    Please help.Thanks. If anyone knows or has links on how to do this migration , please let me know.

    dev

  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510

    Re: output record sets with cursors

    Does the query return any rows if you run in sqlplus?
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  3. #3
    Join Date
    Feb 2003
    Posts
    6

    no rows returned in sql plus

    Thanks for the reply...no rows are returned, should there be some returned?

  4. #4
    Join Date
    Feb 2003
    Posts
    6
    ok...I understand cursors a bit better now...apparantly, you need a FETCH statement to return the query...so I have put this query together:

    DECLARE
    current_date Date;
    TYPE rst_refcur IS REF CURSOR;
    p_rec rst_refcur;
    wprptsta char(2);
    wpitem char(6);
    wpitmseg char(1);
    mandisdv char(2);
    conclass char(1);
    wpwkmix char(4);
    itsegman char(20);
    wpitemno char(7);
    contydot char(2);
    geodist char(2);
    wpitstat char(3);
    localnam char(25);
    locatnfm char(25);
    locatnto char(25);
    isghstdt date;
    effdate date;
    efftime char(8);
    ffisyrcp number(5);
    gis_itmseg char(1);
    BEGIN
    current_date := SYSDATE;
    OPEN p_rec FOR
    select a.wprptsta, a.wpitem, a.wpitmseg, a.mandisdv, a.conclass, a.wpwkmix, a.itsegman,
    a.wpitemno, a.contydot, a.geodist,a.wpitstat, a.localnam, a.locatnfm, a.locatnto, a.isghstdt,
    a.effdate, a.efftime, a.ffisyrcp, g.itmseg gis_itmseg from wpguser.wpgtbl80 a, wpguser.gis_wpgtbl80 g
    where a.geodist = '04' and rownum < 100
    and a.wprptsta = g.wprptsta(+) and a.wpitem = g.wpitem(+) and a.wpitmseg =g.wpitmseg(+);
    FETCH p_rec into wprptsta,wpitem,wpitmseg,mandisdv,conclass,wpwkmix,itsegman,
    wpitemno,contydot,geodist,wpitstat,localnam,locatnfm,locatnto,isghstdt,
    effdate,efftime,ffisyrcp,gis_itmseg;
    END;

    However, I am getting

    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 32

    where line 32 is the fetch line. I've read this error related to running out of stack face, so I have limited the query with rownum < 100, but I still get the mistake. Any ideas? I have a feeling that once I get this to work in SQL plus, it will also work in SQL Server's DTS, becuase I think they use the same runtime checking engine (SQL PLus and Oracle ODBC). Thanks all.

    gratefuly,

    dev

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi! I think you are making life hard for yourself. There are several ways of doing a cursor loops. The explicit type with OPEN, FETCH & CLOSE should normally be avoided unless (e.g.) you have a need to leave the cursor open between calls to a procedure.

    I suggest you look at this link for the simplest method http://www.csee.umbc.edu/help/oracle...5_ora.htm#1449

  6. #6
    Join Date
    Feb 2003
    Posts
    6
    Great documentation...the problem lies in that I need the rowset returned to DTS, and I laso need the Select statement to do a comparison with the system date.

    So I can have

    DECLARE
    CURSOR c1 IS
    SELECT a.wprptsta, a.wpitem, a.wpitmseg, a.mandisdv, a.conclass, a.wpwkmix, a.itsegman,
    a.wpitemno, a.contydot, a.geodist,a.wpitstat, a.localnam, a.locatnfm, a.locatnto, a.isghstdt,
    a.effdate, a.efftime, a.ffisyrcp, g.itmseg gis_itmseg from wpguser.wpgtbl80 a, wpguser.gis_wpgtbl80 g
    where a.geodist = '04'
    and a.wprptsta = g.wprptsta(+) and a.wpitem = g.wpitem(+) and a.wpitmseg =g.wpitmseg(+);
    BEGIN
    FOR c1_rec IN c1 LOOP
    return(c1_rec); <-- gives an error
    END LOOP;
    COMMIT;
    END;

    but in that for loop, I need the record set returned, and this wont work, also I will have to add a condition to the select to compare the system date...I'm not too sure about how to do that. Any ideas on either?

    regards,

    dev

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    RETURN is to give a value for a function. I suspect you need a procedure. How does DTS expect the data? All in one big array or a row at a time?

  8. #8
    Join Date
    Feb 2003
    Posts
    6

    getting closer

    ok...i got something to return the max date, which is just as good as using the system date for me...now I just need to return the row...I believe DTS expects rowsets

    DECLARE
    CURSOR c1 IS
    SELECT a.wprptsta, a.wpitem, a.wpitmseg, a.mandisdv, a.conclass, a.wpwkmix, a.itsegman,
    a.wpitemno, a.contydot, a.geodist,a.wpitstat, a.localnam, a.locatnfm, a.locatnto, a.isghstdt,
    a.effdate, a.efftime, a.ffisyrcp, g.itmseg gis_itmseg from wpguser.wpgtbl80 a, wpguser.gis_wpgtbl80 g,
    (Select max(isghstdt) max_date from wpguser.wpgtbl80
    ) maxresults
    where a.geodist = '04' and a.isghstdt = maxresults.max_date
    and a.wprptsta = g.wprptsta(+) and a.wpitem = g.wpitem(+) and a.wpitmseg =g.wpitmseg(+);
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Entering..');
    FOR c1_rec IN c1 LOOP
    --DBMS_OUTPUT.PUT_LINE('DATE' || c1_rec.isghstdt);
    END LOOP;
    END;


    Now you see I commented the PUTLINE() , but I need something in that for loop that actually returns that particular row.

Posting Permissions

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



Click Here to Expand Forum to Full Width