Using bind vars, ref cursors and dynamic SQL

Hello,

I'm trying to return values from my database using ref cursors, dynamic SQL and bind variables, but I cannot seem to get the syntax right.

It looks like this currently:

/*

    rec_services is declared thusly: TYPE rec_services IS REF CURSOR; */

    FUNCTION my_function
    (pv_param1 IN VARCHAR2
    ,pv_param2 IN VARCHAR2
    )
    RETURN rec_services
    IS

        vr_service rec_services;
    BEGIN

        /*
            pv_param1 contains 'LIKE (..., ..., ...)'
        */
        EXECUTE IMMEDIATE


            'DECLARE '||

            'CURSOR cur_records IS '||

            'SELECT    ss.col1'||

            ',         ss.col2'||

            'FROM      my_view ss '||

            'WHERE     ss.col1 :b1 '||

            'AND       ss.col2 = :b2 '||

            'ORDER BY  ss.col1;'||

            'BEGIN '||

            'OPEN cur_records;'||

            'FETCH cur_records '||

            'INTO :b3'||

            ',    :b4;'||

            'CLOSE cur_records;'||

            'END;'

        USING IN pv_param1
        ,     IN pv_param2
        ,     OUT vr_service.col1
        ,     OUT vr_service.col2
        ;

        RETURN vr_service;

    END my_function;

The error I am getting is:

PLS-00487: Invalid reference to variable 'VR_SERVICE'

Thanks for any assistance,

Mark Received on Wed Jul 02 2003 - 09:01:23 CDT