rewriting a stored procedure from PostGres to Oracle

Hi all,

I'm trying to run this same stored procedure in Oracle that works in Postgres.
I've been through the conversion manuals (ie RETURNS SETOF becomes RETURN, and replaced BOOLEAN with SMALLINT as Oracle doesn't support boolean?)

but the trouble comes in trying to return a whole row. I've seen examples that show how I could use a cursor to return the individually specified elements, but as my *real* table has dozens of columns, I wondered if I can return them all at once? - I'm pretty certain the problem is in the way I'm trying to return "type%ROWTYPE", but I can't make it work as it should.

below is a dummy table representation, along with the Postgres stored procedure that works, and my Oracle attempt that doesn't :-( (at least it finally parses now, but that simply means that "show errors" doesn't help me anymore... :-( )

kind regards,
ochaye

CREATE TABLE my_data (

    order_id character varying(50),
    trade_date date,
    polled smallint
);

insert into my_data values ('junk', SYSDATE, 0);

select * from my_data;

/* This is the Postgres implementation that works */
/*

CREATE OR REPLACE FUNCTION get_mydata () RETURNS SETOF my_data

    AS '
DECLARE
    _aRow my_data%ROWTYPE;
BEGIN
    FOR _aRow IN SELECT * FROM my_data where polled is null or polled = 0 LOOP
    RETURN NEXT _aRow;
    END LOOP;     UPDATE my_data SET polled = 1 WHERE polled IS NULL OR polled = 0;

    RETURN;
END;
'

    LANGUAGE plpgsql;
*/

/* and here is the Oracle version I've tried to produce, which doesn't...*/
CREATE OR REPLACE FUNCTION get_mydata RETURN my_data%ROWTYPE IS

    CURSOR aRow IS SELECT * FROM my_data WHERE polled IS NULL OR polled = 0;     unpolled_rows aRow%ROWTYPE;
BEGIN
    FOR thisRow IN aRow LOOP

        unpolled_rows := thisRow;
    END LOOP;     UPDATE my_data SET polled = 1 WHERE polled IS NULL OR polled = 0;     COMMIT;     RETURN unpolled_rows;
END;
/

show errors;

select get_mydata() from dual;

/* produces the following message:

select get_mydata() from dual

       *
ERROR at line 1:
ORA-00902: invalid datatype
*/ Received on Fri Feb 20 2004 - 20:28:03 CST