How to use multiple cursor in stored procedure for merge query?

Question

I'm trying to insert data in a table from procedure, from below code it is inserting one row at a time even though more rows are available from the location I'm picking data. Please help me to correct this code so all the rows which is coming from the source can be insert at a time. Please confirm if any more clarification required.

create or replace procedure proc_updatepkts
is
    sitenotemp number(3);
    precardtemp number(20);
    cursor x1 is 
        select siteno 
        from sites;
    cursor x2 is 
        select precardsummaryid 
        from precardsummary;
begin
    open x1;
    open x2;
    loop 
        fetch x1 into sitenotemp;
        fetch x2 into precardtemp;
        merge into temptable a 
            using (sitenotemp as tosite
                , precardtemp  as recordid
                , pktdate from dual) b 
            on (a.pktdate = b.pktdate)
            when not matched then 
                insert (a.pktdate,a.tosite,a.recordid)
                values(b.pktdate,b.tosite,b.recordid);
        exit when x2%notfound;
        exit when x1%notfound;
    end loop;
    close x2;
    close x1;
    commit;
end;
/

I am trying to post same recordid for all the sites with the value I'm holding in first cursor. If there are 20 sites then 20 records should post for the same precardsummaryid. Now the extended condition is there can be more than one precardsummaryid so I've taken all the precardsummaryid in another cursor. So if 20 sites and 5 precardsummaryid then 100 records should pass in sendpkts table

Answer 1

You haven't defined any relationship between the records in the source tables. If that is a correct interpretation of your requirements you need a CROSS JOIN.

Replace all the code in your procedure body with one set INSERT statement:

create or replace procedure proc_updatepkts
is
begin
    insert   into sendpkts 
        (pktdate, tosite, recordid)
    select  sysdate as pktdate
          , s.siteno as tosite
          , p.precardsummaryid  as recordid
    from sites s
        cross join precardsummary p;
    commit;
end;

It's not always a good idea to have commits in stored procedures, but I've left this one in.