asked on

Oracle SQl query

INSERT INTO temp_mobile_donors_au
select distinct d.donor_id
from donations@pdon.world d,
blood_drives b,
ds_malaria_travel_history h
where b.site_code = 'NA19'
and d.coll_date between trunc(add_months(sysdate,-24))
and trunc(sysdate)
and d.drive_id = 'DRV'||b.drive_id
and h.donor_id = d.donor_id;
------------------------------
This query is used to print reports. I am inserting donors and then loop to print the donor sheet for each donor. For this particular case I have 105 donors. My question is ----is there a way to take a count when inserted and see if count is > 50 then insert those donors after count 50 into another table?

Sean Stuber

which 50 go to the first table - how do you identify them? what is the second table? same structure?

why do you want to split the data?

If I'm misunderstanding please post 10 rows of sample data and split after 5 rather than 50 so we can see expected results

anumoses

ASKER

DONOR_ID
DN20136333
DN20115444
DN20103843
DN20094707
DN20091105
--------------------------------
DONOR_ID
DN20224886
DN20222305
DN20219807
DN20170256
DN20153671
------------------

The reason is in my reports the donor sheet is sent directly to the printer. I am using closewindow since the report goed to the printer.
v_url_c||'&destype=PRINTER&desname=cmproom&closewindow=yes'||v_conn_strg_c;
In this particular case, I had 105 donors. Only 85 printed though all the 105 got inserted in the table. So I thought the i-explorer runs the report and closes the window. Some of the users have not enough memory and their computer freezes. So I thought if more than 50 donors then insert into 2nd table where the scructure is the same. Its just the donor_id I have in the table.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

Very brilliantly said.

Before I use this statement
v_url_c := 'http://'||v_rep_server_c||'report=r_ds_travel_history1¶m_donor_id='||:control.param_donor_id;

how can I grab single cursor with 50 rows at a time? Can you help me here?

anumoses

ASKER

I got this example

declare
Type vr_Rec Is Record (
DESCRICAO VARCHAR2(100),
QTDE NUMBER(2) );
Type vt_Rec Is table of vr_Rec Index By Pls_Integer;
va_rec vt_Rec;
vc_Cursor SYS_REFCURSOR;
begin
Open vc_Cursor For 'SELECT ''x'' DESCRICAO, 1 QTDE FROM DUAL UNION ALL '||
'SELECT ''y'' DESCRICAO, 2 QTDE FROM DUAL';
Loop
Fetch vc_Cursor Bulk Collect Into va_rec Limit 20000;
Exit When va_rec.Count = 0;
If va_rec.Count >0 Then
For/*all*/ i In va_rec.first .. va_rec.last
Loop
dbms_output.put_line( va_rec(i).DESCRICAO || ' - ' || va_rec(i).QTDE );
End Loop;
End If;
End Loop;
Close vc_Cursor;
end;

Output
======
x - 1
y - 2
--------------------Can I use the same logic?

anumoses

ASKER

bulk collect cannot be used in oracle forms to print reports

anumoses

ASKER

thanks