| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: string manipulation in procedures
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
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
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 byusername', :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 - 07:40:32 CST
 |
 |