Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: string manipulation in procedures

Re: string manipulation in procedures

From: kev <kevin.porter_at_fast.no>
Date: 2000/02/28
Message-ID: <38BA8464.871E44D0@fast.no>

Erika Grondzakova wrote:

> 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
> ^^^ ^^^
>

Yes, thanks for poitning that out. I think it works, but still testing :o)))

>
> > ) 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 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US