2 added 35 characters in body
source | link

First, I'll cover the SQL row-limiting side of things.

In Oracle 11.2.x and lower, you have to use rownum and a subquery, as it doesn't support the LIMIT or FETCH clauses:

select * from
(
  select *
  from yourtable
  order by submitdate ASC
)
where rownum <=1000;

In Oracle 12 you can use:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
FETCH FIRST 1000 ROWS ONLY;

... then, for the next 1k:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;

Sample table:

create table yourtable
(
  ID number,
  YYYYMMDD varchar(8),
  submitdate date
);

The PL/SQL:

CREATE OR REPLACE PROCEDURE process_records(offset IN NUMBER)
IS
  CNT number;
  ID yourtable.id%type;
  YYYYMMDD yourtable.yyyymmdd%type;
  SUBMITDATE yourtable.submitdate%type;
  ID_YYYYMMDD varchar(100); -- variable to hold the concatenated value for the 2nd cursor
  CURSOR yourcursor
  IS
    SELECT * 
    FROM (
      SELECT * 
      FROM yourtable
      ORDER BY submitdate ASC
    ) WHERE rownum > offset;
BEGIN
  CNT:=0;
  OPEN yourcursor;
  LOOP
    FETCH yourcursor into ID,YYYYMMDD,SUBMITDATE;
    CNT:=CNT+1;
    EXIT WHEN CNT=1000;
    ID_YYYYMMDD := ID||YYYYMMDD;
    --
    -- put your child cursor here
    --
  END LOOP;
END process_records;
/

The stored procedure takes an input, which is the start offset.

Create another cursor, and use your concatenated ID ID_YYYYMMDD in the WHERE clause.

Why do you need to process 1000 records at a time? It'll be slower iterating this way. Often you can achieve the same thing using one or two lone SQL statements, which end up way more efficient.

First, I'll cover the SQL row-limiting side of things.

In Oracle 11.2.x and lower, you have to use rownum and a subquery, as it doesn't support the LIMIT or FETCH clauses:

select * from
(
  select *
  from yourtable
  order by submitdate ASC
)
where rownum <=1000;

In Oracle 12 you can use:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
FETCH FIRST 1000 ROWS ONLY;

... then, for the next 1k:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;

Sample table:

create table yourtable
(
  ID number,
  YYYYMMDD varchar(8),
  submitdate date
);

The PL/SQL:

CREATE OR REPLACE PROCEDURE process_records(offset IN NUMBER)
IS
  CNT number;
  ID yourtable.id%type;
  YYYYMMDD yourtable.yyyymmdd%type;
  SUBMITDATE yourtable.submitdate%type;
  ID_YYYYMMDD varchar(100); -- variable to hold the concatenated value for the 2nd cursor
  CURSOR yourcursor
  IS
    SELECT * 
    FROM (
      SELECT * 
      FROM yourtable
    ) WHERE rownum > offset;
BEGIN
  CNT:=0;
  OPEN yourcursor;
  LOOP
    FETCH yourcursor into ID,YYYYMMDD,SUBMITDATE;
    CNT:=CNT+1;
    EXIT WHEN CNT=1000;
    ID_YYYYMMDD := ID||YYYYMMDD;
    --
    -- put your child cursor here
    --
  END LOOP;
END process_records;
/

The stored procedure takes an input, which is the start offset.

Create another cursor, and use your concatenated ID ID_YYYYMMDD in the WHERE clause.

Why do you need to process 1000 records at a time? It'll be slower iterating this way. Often you can achieve the same thing using one or two lone SQL statements, which end up way more efficient.

First, I'll cover the SQL row-limiting side of things.

In Oracle 11.2.x and lower, you have to use rownum and a subquery, as it doesn't support the LIMIT or FETCH clauses:

select * from
(
  select *
  from yourtable
  order by submitdate ASC
)
where rownum <=1000;

In Oracle 12 you can use:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
FETCH FIRST 1000 ROWS ONLY;

... then, for the next 1k:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;

Sample table:

create table yourtable
(
  ID number,
  YYYYMMDD varchar(8),
  submitdate date
);

The PL/SQL:

CREATE OR REPLACE PROCEDURE process_records(offset IN NUMBER)
IS
  CNT number;
  ID yourtable.id%type;
  YYYYMMDD yourtable.yyyymmdd%type;
  SUBMITDATE yourtable.submitdate%type;
  ID_YYYYMMDD varchar(100); -- variable to hold the concatenated value for the 2nd cursor
  CURSOR yourcursor
  IS
    SELECT * 
    FROM (
      SELECT * 
      FROM yourtable
      ORDER BY submitdate ASC
    ) WHERE rownum > offset;
BEGIN
  CNT:=0;
  OPEN yourcursor;
  LOOP
    FETCH yourcursor into ID,YYYYMMDD,SUBMITDATE;
    CNT:=CNT+1;
    EXIT WHEN CNT=1000;
    ID_YYYYMMDD := ID||YYYYMMDD;
    --
    -- put your child cursor here
    --
  END LOOP;
END process_records;
/

The stored procedure takes an input, which is the start offset.

Create another cursor, and use your concatenated ID ID_YYYYMMDD in the WHERE clause.

Why do you need to process 1000 records at a time? It'll be slower iterating this way. Often you can achieve the same thing using one or two lone SQL statements, which end up way more efficient.

1
source | link

First, I'll cover the SQL row-limiting side of things.

In Oracle 11.2.x and lower, you have to use rownum and a subquery, as it doesn't support the LIMIT or FETCH clauses:

select * from
(
  select *
  from yourtable
  order by submitdate ASC
)
where rownum <=1000;

In Oracle 12 you can use:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
FETCH FIRST 1000 ROWS ONLY;

... then, for the next 1k:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;

Sample table:

create table yourtable
(
  ID number,
  YYYYMMDD varchar(8),
  submitdate date
);

The PL/SQL:

CREATE OR REPLACE PROCEDURE process_records(offset IN NUMBER)
IS
  CNT number;
  ID yourtable.id%type;
  YYYYMMDD yourtable.yyyymmdd%type;
  SUBMITDATE yourtable.submitdate%type;
  ID_YYYYMMDD varchar(100); -- variable to hold the concatenated value for the 2nd cursor
  CURSOR yourcursor
  IS
    SELECT * 
    FROM (
      SELECT * 
      FROM yourtable
    ) WHERE rownum > offset;
BEGIN
  CNT:=0;
  OPEN yourcursor;
  LOOP
    FETCH yourcursor into ID,YYYYMMDD,SUBMITDATE;
    CNT:=CNT+1;
    EXIT WHEN CNT=1000;
    ID_YYYYMMDD := ID||YYYYMMDD;
    --
    -- put your child cursor here
    --
  END LOOP;
END process_records;
/

The stored procedure takes an input, which is the start offset.

Create another cursor, and use your concatenated ID ID_YYYYMMDD in the WHERE clause.

Why do you need to process 1000 records at a time? It'll be slower iterating this way. Often you can achieve the same thing using one or two lone SQL statements, which end up way more efficient.