string manipulation in procedures

From: kev <kevin.porter_at_fast.no>
Date: Mon, 28 Feb 2000 11:01:10 +0000
Message-ID: <38BA5576.8AE5A5D7@fast.no>

Hi,

I have a procedure which takes in an SQL query and returns an open cursor for that query. I need to be able to put some SQL around this user-supplied SQL. I think I'm just not getting the syntax right, I can't find any examples of this in the manual.

The procedure I'm trying to create will return the 'nrows' rows from the 'offset'th row (ofsset and nrows are passed in as per MySQL's LIMIT command).

For example, if the SQL query "select * from emp" with an offset of 1 and an nrows of 10 is passed into the procedure I want it to return an open cursor for the query:

select * from
(

            select a.*, rownum r from
            (
                          select * from emp
             ) a

) b
where b.r between :offset and (:offset+:nrows);

Here's my latest effort:

create or replace package types
as

type cursorType is ref cursor;
end;
/

REM Proc: get_cursor
REM Purpose: Returns an open cursor for the supplied SQL query, with the

REM          result set being limited in range (as per MySQL's LIMIT
REM          command).

create or replace procedure get_cursor_limit
(my_sql in varchar2, my_curs out types.cursorType,
offset in number, rows in number)
is
begin
open my_curs for
select *
from ( select a.*, rownum r

        from ( my_sql ) a
        ) b

where b.r between :offset and (:rows+:offset);

end get_cursor_limit;
/

...and here's the errors I get:

SQL> show errors
Errors for PROCEDURE GET_CURSOR_LIMIT:

LINE/COL ERROR

9/62 PLS-00049: bad bind variable 'MY_SQL' 9/62 PLS-00103: Encountered the symbol "" when expecting one of the following:
( select

I've tried too many variations on the above code to post them all here, but I always get similar errors. How do I make this procedure work?

TIA,

Received on Mon Feb 28 2000 - 05:01:10 CST