0

I am writing a procedure to migrate two tables into different tables. This procedure will have two cursors. One to fetch the parent records and then second cursor to fetch the details for each parent record. The parent record is a table with the following columns --

ID || YYYYMMDD || submitdate.

The combination of ID and YYYYMMDD gives the unique id. So now I want to migrate this to another table in the order it was created. I can order this table by submitdate, but I need to fetch 1000 records at a time. How can I achieve this?

  • 2
    Why do you need to migrate data "in the order it was created"? Since rows in a heap table are inherently unordered, that sounds suspicious. Any chance that you are hoping to cause queries that don't have an order by to return data in the "right" order? – Justin Cave Dec 8 '15 at 9:10
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.