Home » SQL & PL/SQL » SQL & PL/SQL » Regarding tables index by binary_integer
Regarding tables index by binary_integer [message #185636] Wed, 02 August 2006 15:51 Go to next message
cpsinh@yahoo.com
Messages: 5
Registered: August 2006
Junior Member
Hello!

I have following thing to do.

Collect claim number based on certain criteria, Probably, I will
get around 1000 claims based on the criteria. As I need to get claims from two different tables and there shouldn't be a duplicate entry, I am using union set to get the claim number.

Now, for this claim, I need to get claimant names(first_name, last_name)

Also, it is possible that a claimant may have more than one claim associated with him

for example, if I am getting around 1000 claims from the above criteria, that 1000 claims may be divided among 50 or 60 claimants.

so, first I am selecting a claim_number and then passing that claim number as input to another SQL query(using cursor) to get claimant_names (first and last). As I am going to use these claimants names as input to another query in order to further processing, I need distinct claimant name.

I mean for example, As I am retrieving a claimant name based on a claim number, It is possible I may get the same claimant_name for different claim number.
So, what I am doing is storing all the claimant name for all claim numbers in PL/SQL composite variable table (index by binary_integer).

and then may be group the data according to claimant_first_name and last_name, however, using this approach is giving me an error that


PLS-00597: : _expression 'CL_TABLE' in the INTO list is of wrong

AT THE END, I HAVE MENTIONED MY ORACLE DATABASE VERSION

I know, it is long problem, but i will highly appreciate, if any body can provide me the option or solution to this problem

Thanks a lot

CPS.

THE CODE is like this :
-----------------------


Declare

l_claim_num number := 0;

--defining record

type cl_rec is record
(claim_number claim.claim_num%type,
claimant_fname claimant.claimant_first_name%type,
claimant_lname claimant.claimant_last_name%type
);
--defining table

type cl_table_type is table of cl_rec
index by binary_integer;
cl_table cl_table_type;

--defining cursor

cursor c_ret IS
select b.claim_num
from summarized_expenses s, bulk_payment_detail b
where s.bulk_pymt_summary_id_seq_num = b.bulk_pymt_summary_id_seq_num
and s.cust_id_seq_num = 217319
and s.bank_code >=630
union
select c.claim_num
from claim c, assignment a, claimant cl
where c.assignmt_id_seq_num = a.assignmt_id_seq_num
and c.claimant_id_seq_num = cl.claimant_id_seq_num
and c.claim_status_code || '' in ('R','O')
and a.cust_id_seq_num = 217319
and c.created_date > '1-JUL-06';

cursor col_name IS
select c.claim_num, cl.claimant_first_name, cl.claimant_last_name
from claimant cl, claim c
where c.claimant_id_seq_num = cl.claimant_id_seq_num
and c.claim_num = l_claim_num;

Begin
_/* first cursor (c_ret) retrieves the claim_num based on the criteria and stores in variable l_claim_num */

open c_ret;
loop
fetch c_ret into l_claim_num;

-- second cursor (c_ret) retrieves the claim_num, first_name, last_name and store in
--PL/SQL table for the claim_num that was retrieved earlier from cursor 1 and stored in --a varialbe

open col_name;
--loop

fetch col_name into cl_table;
--end loop;
for i in cl_table.first .. cl_table.last loop
dbms_output.put_line(cl_table(i).claimant_fname||' '||
cl_table(i).claimant_lname||' '||cl_table(i).claim_number);
end loop;
close col_name;
end loop;
close c_ret;
end ;


I am getting following comilation error
-----------------------------------------------------
Error report:
ORA-06550: line 40, column 33:
PLS-00597: _expression 'CL_TABLE' in the INTO list is of wrong type
ORA-06550: line 40, column 13:
PL/SQL: SQL Statement ignored


VERSION DETAILS
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for HPUX: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production


Re: Regarding tables index by binary_integer [message #185653 is a reply to message #185636] Wed, 02 August 2006 21:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are fetching into CL_TABLE, which is a collection. When you fetch a single row, it needs to be fetched into a RECORD, and then you can insert that record into a collection.

Have you considered adding the claimant lookup to the claim cursor? It would be heaps more efficient. Cursors within cursors are inefficient and should be avoided where possible.

Ross Leishman.
Re: Regarding tables index by binary_integer [message #185656 is a reply to message #185636] Wed, 02 August 2006 21:58 Go to previous message
cpsinh@yahoo.com
Messages: 5
Registered: August 2006
Junior Member
Hi Ross!

Thanks a lot, Highly appreciate your quick answere, I did exactly the same, I first passed the data to records and then to PL/SQL table.

I didn't get what you mean by claimant lookup to claim cursor, can you please give an example?

Thanks,

chandrapal
Previous Topic: Numeric data type conversion (Merged)
Next Topic: Re-Ordering a Table
Goto Forum:
  


Current Time: Wed Dec 07 12:21:39 CST 2016

Total time taken to generate the page: 0.06353 seconds