Home » SQL & PL/SQL » SQL & PL/SQL » problem with use of array
problem with use of array [message #274049] Sat, 13 October 2007 01:55 Go to next message
amrutha2004
Messages: 8
Registered: September 2007
Junior Member
I have a cursor which has 8 columns.I open the cursor in the FOR LOOP and then i have a IF condition in it. Within the IF condition, I retrieve only one of the column and want to put it into an array.
So for every row of the cursor, i check the IF condition and if it is true, i want to go on adding the array with my values till the cursor ends.
Then i want to take the array in my FOR loop and feed it into a procedure which processes it one by one and put it into another array which again is fed as an input into another procedure. This last procedure will give the final output string.

As the package is large, i thought better not post it here.
If it is necessary, then i can post it.
Re: problem with use of array [message #274050 is a reply to message #274049] Sat, 13 October 2007 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the relevant parts and don't forget to read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Also post your Oracle version (4 decimals).

If you can't extract a short piece of code, create a test case to explain what you want, what you tried and why it fails.

Regards
Michel
Re: problem with use of array [message #274060 is a reply to message #274050] Sat, 13 October 2007 05:17 Go to previous messageGo to next message
amrutha2004
Messages: 8
Registered: September 2007
Junior Member
<variable declaration>

L_NUM_LOAN1 NUMBER(13,2) := (0.00);
L_NUM_LOAN2 NUMBER(13,2) := (0.00);

CURSOR A1 is
...........;
CURSOR c1 is
SELECT EMP.AMOUNT DEDUCTION ,emp.element_id.......
FROM XXOR_EMPLOYEE_INFO_V EMP;

BEGIN
FOR A2 IN A1
FOR c2 IN c1
loop
exit when c1%notfound;
IF (.....)
THEN
if (L_NUM_LOAN1 = 0.00)THEN
L_NUM_LOAN1 := c1.deduction;
ELSE L_NUM_LOAN2 := c1.deduction;
END IF;
END IF;
END LOOP;

IF L_NUM_LOAN1 <> 0.00
THEN
<call_procedure1>
END IF;

IF L_NUM_LOAN2 <> 0.00
THEN
<call_procedure1>
END IF;

END LOOP;
end; /

The issue here is there is a limit of only 2 'loan' elements. But what if there are many 'loan' elements (depending on cursor A1)? So ARRAY should be used here to allocate memory for those many number of 'loan' elements depending on cursor A1.
I used 'Varray' and then 'pl/sql table', but somehow i didnt get the result. Please help me out.
(The .... represents so and so...or some condition...)
Re: problem with use of array [message #274062 is a reply to message #274060] Sat, 13 October 2007 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read what I posted, you don't have an answer.

Regards
Michel
Re: problem with use of array [message #274072 is a reply to message #274049] Sat, 13 October 2007 07:26 Go to previous messageGo to next message
amrutha2004
Messages: 8
Registered: September 2007
Junior Member
TYPE loan_table IS TABLE OF VARCHAR2(15);
a_num_loan loan_table;

cursor c1 is
SELECT EMP.AMOUNT DEDUCTION ,emp.element_id
FROM XXOR_EMPLOYEE_INFO_V EMP

BEGIN
for A2 in A1

FOR c2 IN c1
loop
exit when c1%notfound;

IF (.....)
THEN
counter := counter +1;
a_num_loan(counter) := EMPLOYEE_DETAILS_REC.DEDUCTION;
END LOOP;

IF a_NUM_LOAN.EXISTS
THEN
for counter in a_num_loan.first..a_num_loan.last loop
<call_procedure1>
END IF;

END LOOP;
counter := 0;
end; /

I have the answer to my earlier question.But the problem is presenting it in my program.I tried using pl/sql table functionality. But it gives errors such as:
-->wrong kind of arguments in the program.
-->error in declaration.
-->error in the line 'a_NUM_LOAN.EXISTS'.

Hope I have used the correct declaration and 'counter' functionality for the 'FOR LOOP'.Hope you got my exact problem.
If the message is too lengthy,shall I upload my package? Please Advice.
Re: problem with use of array [message #274074 is a reply to message #274072] Sat, 13 October 2007 08:20 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FORMAT

ORACLE VERSION

Regards
Michel

[Updated on: Sat, 13 October 2007 08:20]

Report message to a moderator

Previous Topic: oracle 10 new features and flashback query
Next Topic: Compare Table and Snapshot
Goto Forum:
  


Current Time: Sun Dec 04 20:40:10 CST 2016

Total time taken to generate the page: 0.07186 seconds