| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: string manipulation in procedures
Thanks once again Thomas, I surely must be bearing a solution now!
My procedure doesn't work though :o(
I made the changes as you suggested, but when I try and run the procedure from SQLPlus like this:
here's what happens:
SQL> exec get_cursor_limit('select * from emp',:mycurs,1,10); BEGIN get_cursor_limit('select * from emp',:mycurs,1,10); END;
*
ERROR at line 1:
ORA-00928: missing SELECT keyword ORA-06512: at "ULTSOC.GET_CURSOR_LIMIT", line 9 ORA-06512: at line 1
Do you know why this is?
My procedure looks like this now:
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
end get_cursor_limit;
/
thanks,
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 CorporationReceived on Mon Feb 28 2000 - 00:00:00 CST
 |
 |