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
Messages: 5
Registered: August 2006
Junior Member

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


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


THE CODE is like this :


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
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;

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

open c_ret;
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;

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

Oracle9i Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for HPUX: Version - Production
NLSRTL Version - 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
Messages: 3727
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
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?


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

Current Time: Mon Mar 27 23:33:14 CDT 2017

Total time taken to generate the page: 0.14582 seconds