Avatar of anumoses
anumosesFlag for United States of America 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?
Oracle Database

Avatar of undefined
Last Comment
anumoses

8/22/2022 - Mon
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
ASKER
anumoses

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
anumoses

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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
anumoses

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?
ASKER
anumoses

bulk collect cannot be used in oracle forms to print reports
ASKER
anumoses

thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.