Re: string manipulation in procedures

On Mon, 28 Feb 2000 11:01:10 +0000, kev <kevin.porter_at_fast.no> wrote:

>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,
>
>- Kev
>
>

What you want is dynamic sql. AFAIK you can't just combine a REF CURSOR with dynamic sql. You could do something similar with dynamic sql, however your procedure is going to return a cursor handle only, fetching and looping needs to be done separately.

Hth,

Sybrand Bakker, Oracle DBA

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Feb 28 2000 - 06:55:08 CST