Home » SQL & PL/SQL » SQL & PL/SQL » Bulk collect for 8i (Oracle 8i)
Bulk collect for 8i [message #331288] Wed, 02 July 2008 22:09 Go to next message
petec
Messages: 11
Registered: July 2008
Junior Member
Hi,

The following brief and code has been supplied to me but I can't work out how to do it in 8i. Bulk collect doesn't work. Can someone please provide an alternative(cursor perhaps):

For each MIMS 4.3 file that contains a district code field in the primary key, group by the district code field in the primary key and provide counts.

****************************************************************

spool district_merging.txt
set serveroutput on

-- 1. Record counts by district code
declare
cursor cget_data is
select uic.table_name,uic.column_name
from user_constraints uc,user_indexes ui, user_ind_columns uic
where ui.index_name= uic.index_name
and ui.table_name = uc.table_name
and constraint_type='P'
and uniqueness = 'UNIQUE'
and column_name='DSTRCT_CODE';
TYPE t_dc_rec is record (dstrct_code varchar2(100), cnt number);
TYPE t_dc_t is table of t_dc_rec index by binary_integer;
r_dc t_dc_t;
begin
dbms_output.put_line('FILENO '||'DIST '||' RECORDS');
dbms_output.put_line('------ ---- ----------');

for cur in cget_data loop
r_dc.delete;
execute immediate ('select dstrct_code,count(*) from '||cur.table_name||' group by dstrct_code') bulk collect into r_dc;
if r_dc.count>0 then
for i in r_dc.first..r_dc.last loop
dbms_output.put_line(cur.table_name||' '||r_dc(i).dstrct_code||' '||r_dc(i).cnt);
end loop;
end if;
end loop;
end;
/

****************************************************************


Re: Bulk collect for 8i [message #331289 is a reply to message #331288] Wed, 02 July 2008 22:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/

You really should upgrade to a version of Oracle that has been supported this century
& preferably to V10 or V11 which are currently supported.
Re: Bulk collect for 8i [message #331294 is a reply to message #331288] Wed, 02 July 2008 23:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove PL/SQL code and do it with a SQL script.

Regards
Michel
Re: Bulk collect for 8i [message #331295 is a reply to message #331294] Wed, 02 July 2008 23:18 Go to previous messageGo to next message
petec
Messages: 11
Registered: July 2008
Junior Member
Thanks Michel,

I've tried all day but can't use a valiable table name in SQL*Plus. Is this what you mean by "SQL Script"?
Re: Bulk collect for 8i [message #331301 is a reply to message #331288] Wed, 02 July 2008 23:34 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Can someone please provide an alternative(cursor perhaps):
You are right, you shall use cursor with dynamic SQL instead. Just have a look at http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/10_dynam.htm#10961.
Re: Bulk collect for 8i [message #331304 is a reply to message #331295] Wed, 02 July 2008 23:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've tried all day but can't use a valiable table name in SQL*Plus.

A script can generate a script and execute it.

Regards
Michel
Re: Bulk collect for 8i [message #331307 is a reply to message #331304] Wed, 02 July 2008 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Wed, 02 July 2008 21:38
Quote:
I've tried all day but can't use a valiable table name in SQL*Plus.

A script can generate a script and execute it.

Regards
Michel



Such can be an insurmountable obstacle for those who are challenged to just spell S-Q-L.
Re: Bulk collect for 8i [message #331315 is a reply to message #331307] Thu, 03 July 2008 00:04 Go to previous message
petec
Messages: 11
Registered: July 2008
Junior Member
Thanks everyone,

Would like to do it in PL/SQL but time is limited. I'll do a script to generate the sql needed, and try to work out the REF CURSOR at a later date.
Previous Topic: Date compare
Next Topic: query with rank function
Goto Forum:
  


Current Time: Sat Dec 10 16:40:07 CST 2016

Total time taken to generate the page: 0.10251 seconds