look up refcursor. You can use that as an out parameter.
Docs should have an example.
Jim
"ochaye" <ochaye_at_nospam.invalid.net> wrote in message
news:4036c1c5$0$24624$fa0fcedb_at_lovejoy.zen.co.uk...
> 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 - 21:50:08 CST
