Re: string manipulation in procedures

Hello

  I liked this idea for doing batch gets from the stored procedure. Earlier I was using a cursor variable defined globally in a package and then using DBMS_SQL package to attach this cursor variable to do fetch in batches depending upon the batch size and leaving this cursor open if it still had records left. Everything worked fine but I had to use Dynamic Sql packages even when it was not required.  I tested this method and it works fine for small tables , but for large tables it seems to be very slow. I tried running query on all_users table which had approx 500 rows , it worked fine but on a table with 200000 records it just sleeps for a while.  Is there any other way to do gets in batches.  The scenario is that I have to call a stored procedure from a JAVA Code and the resultset is huge so it has to be called in batches.

 Regards

• Anand In article <38BA8464.871E44D0_at_fast.no>, kev <kevin.porter_at_fast.no> wrote:
  > 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)))
  >
  > - Kev
  >
  > >
  > > > ) 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
  >
  >
  

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 01 2000 - 00:00:00 CST