Hello,
>
> 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 ||
I think you made a mistake here ...
from ( ' || my_sql || ' ) a
^^^ ^^^
> ) a
> ) b
> where b.r between :min_row and :max_row' using offset, (offset+rows);
>
> end get_cursor_limit;
> /
>
> thanks,
>
> - Kev
>
> Thomas Kyte wrote:
>
> > A copy of this was sent to kev <kevin.porter_at_fast.no>
> > (if that email address didn't require changing)
> > On Mon, 28 Feb 2000 11:01:10 +0000, you 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;
> > >/
> > >-------------------------------------------------
> > >
> >
> > You need to dynamically open the ref cursor -- something that is only possible
> > in Oracle8i, release 8.1 and later. Prior that that -- you will not be able to
> > do this with a ref cursor and would have to use DBMS_SQL instead (and could not
> > use an order by in the My_Sql query). In Oracle8i, release 8.1, this would look
> > like:
> >
> > ops$tkyte_at_8i> create or replace package types
> > 2 as
> > 3 type cursorType is ref cursor;
> > 4 end;
> > 5 /
> > Package created.
> >
> > ops$tkyte_at_8i> create or replace procedure get_cursor_limit(my_sql in varchar2,
> > 2 my_curs in out types.cursorType,
> > 3 offset in number,
> > 4 rows in number)
> > 5 is
> > 6 begin
> > 7
> > 8 open my_curs for
> > 9 'select *
> > 10 from ( select a.*, rownum r
> > 11 from ( ' || my_sql || ' ) a
> > 12 ) b
> > 13 where b.r between :min_row and :max_row' using offset, (rows+offset);
> > 14
> > 15 end get_cursor_limit;
> > 16 /
> >
> > Procedure created.
> >
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i> variable x refcursor
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i> exec get_cursor_limit( 'select * from all_users order by
> > username', :x, 100, 15 );
> >
> > PL/SQL procedure successfully completed.
> >
> > ops$tkyte_at_8i> print x
> >
> > USERNAME USER_ID CREATED R
> > ------------------------------ ---------- --------- ----------
> > MSEATON 3364 24-JUN-99 100
> > MSJONES 3366 24-JUN-99 101
> > MSSMITH 3367 24-JUN-99 102
> > NAMES 895 24-JUN-99 103
> > NATO_GAT 411 24-JUN-99 104
> > NEWS1 8656 24-JUN-99 105
> > NEWS2 8662 24-JUN-99 106
> > NEWS3 8663 24-JUN-99 107
> > NE_GEHS 1582 24-JUN-99 108
> > NLA 932 24-JUN-99 109
> > O8TRAIN 1496 24-JUN-99 110
> > OAS_PUBLIC 40 29-APR-99 111
> > OCAFE 4489 24-JUN-99 112
> > OCITEST 6560 24-JUN-99 113
> > OEM 35 26-APR-99 114
> > OGS 414 24-JUN-99 115
> >
> > 16 rows selected.
> >
> > ops$tkyte_at_8i>
> >
> > >
> > >...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
> > >
> >
> > --
> > See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> > Current article is "Part I of V, Autonomous Transactions" updated June 21'st
> >
> > Thomas Kyte tkyte_at_us.oracle.com
> > Oracle Service Industries Reston, VA USA
> >
> > Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Mon Feb 28 2000 - 08:12:59 CST
