Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
why the loop cannot pick the next record?
Posted on 2011-02-28
I am debugging a package. I don't understand why the loop cannot pick up the next record, I have record something like below
inventory_id url
1234 http://test1.html
1234 http://test2.html
1234 http://test3.html
I want to know if lopping for next record will not pick up the next record of the inventory_id is the same?
I have the oracle program something like below
....
BEGIN
v_inventory_item_id := p_dev_prods.FIRST;
WHILE v_inventory_item_id IS NOT NULL
LOOP
DECLARE
l_url VARCHAR2 (3000);
...........
BEGIN
l_url :=........
......
v_inventory_item_id := p_dev_prods.NEXT (v_inventory_item_id);
END;
END LOOP;
......
I don't understand why the output url only show http://test1.html
inventory_id url
1234 http://test1.html
1234 http://test2.html
1234 http://test3.html
I want to know if lopping for next record will not pick up the next record of the inventory_id is the same?
I have the oracle program something like below
....
BEGIN
v_inventory_item_id := p_dev_prods.FIRST;
WHILE v_inventory_item_id IS NOT NULL
LOOP
DECLARE
l_url VARCHAR2 (3000);
...........
BEGIN
l_url :=........
......
v_inventory_item_id := p_dev_prods.NEXT (v_inventory_item_id);
END;
END LOOP;
......
I don't understand why the output url only show http://test1.html
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
17-
17
35 Comments
Active today
Post your code that populates the list. It's possible you have an empty item in the list.
Active today
What datatype is p_dev_prods? I assume its a PL/SQL table but before I can post an example, I want to verify it is a table or Varray.
Active today
I need the base type so I can produce you an example of what I mean.
Now, what is product_tab defines as?
I need the pase types.
Now, what is product_tab defines as?
I need the pase types.
Active today
I don't find any for product_tab defined as. I search around the package, I don't find it. I do see some procedures has the following like
p_standard_products IN OUT product_tab,
p_standard_products IN OUT product_tab,
Active today
I might have been mistaken. In trying to come up with a test that shows what I was thinking, I cam acorss a code example that seems to mirror what you posted:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#BABFJJIH
The sample is in the code window below.
For me to troubleshoot what is happening in your code, I'll need to see the actual code to include the type definition, how it's populated and the full loop code that isn't working.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#BABFJJIH
The sample is in the code window below.
For me to troubleshoot what is happening in your code, I'll need to see the actual code to include the type definition, how it's populated and the full loop code that isn't working.
Example 5-31 Using FIRST and LAST With a Collection
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST);
DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST);
-- When the subscripts are consecutive starting at 1,
-- it's simple to loop through them.
FOR i IN n.FIRST .. n.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i));
END LOOP;
n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps or the collection might be uninitialized,
-- the loop logic is more extensive. We start at the first element, and
-- keep looking for the next element until there are no more.
IF n IS NOT NULL THEN
counter := n.FIRST;
WHILE counter IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE('Element #' || counter || ' = ' || n(counter));
counter := n.NEXT(counter);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('N is null, nothing to do.');
END IF;
END;
/
Active 2 days ago
Hi,
For me the code that you have shown is fine. WHILE ... IS NOT NULL ... NEXT is the right way to loop through a collection. the problem is elsewhere. did you caount the loops to see if it loops through the 3 elements ? Do you have an exception block that catches some errors ?
Regards,
Franck.
For me the code that you have shown is fine. WHILE ... IS NOT NULL ... NEXT is the right way to loop through a collection. the problem is elsewhere. did you caount the loops to see if it loops through the 3 elements ? Do you have an exception block that catches some errors ?
Regards,
Franck.
Active today
I have triple checked and confirmed that I am mistaken. I cannot create a 'gap' be deleting one of the roes in the middle.
If this is happening then there is a bug in your version.
Add a .COUNT to verify you have three rows:
....
dbms_output.put_line('Coun
BEGIN
v_inventory_item_id := p_dev_prods.FIRST;
...
If this is happening then there is a bug in your version.
Add a .COUNT to verify you have three rows:
....
dbms_output.put_line('Coun
BEGIN
v_inventory_item_id := p_dev_prods.FIRST;
...
Active today
I think my code is following this logic
counter := n.FIRST;
WHILE counter IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE('Elem
counter := n.NEXT(counter);
END LOOP;
v_inventory_item_id := p_dev_prods.FIRST;
WHILE v_inventory_item_id IS NOT NULL
LOOP
v_inventory_item_id := p_dev_prods.NEXT (v_inventory_item_id);
END LOOP;
What is wrong with my code?
Also, I make up 3 records, actually there is a list for it, may be 2 records, maybe 10 records...etc
counter := n.FIRST;
WHILE counter IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE('Elem
counter := n.NEXT(counter);
END LOOP;
v_inventory_item_id := p_dev_prods.FIRST;
WHILE v_inventory_item_id IS NOT NULL
LOOP
v_inventory_item_id := p_dev_prods.NEXT (v_inventory_item_id);
END LOOP;
What is wrong with my code?
Also, I make up 3 records, actually there is a list for it, may be 2 records, maybe 10 records...etc
Active today
We understand that part of your code. You've posted that before.
You still haven't said 'what' v_inventory_item_id is and how it is defined.
You haven't provided any of the suggest tracking. The suggested 'counter' method was meant to define a number variable and increment it inside the loop:
...
counter number := 0;
begin
...
while...loop
counter := counter+1;
end loop;
dbms_output.put)line('Coun
I would suggest just out-putting the collection COUNT from the example http:#a35007841
You still haven't said 'what' v_inventory_item_id is and how it is defined.
You haven't provided any of the suggest tracking. The suggested 'counter' method was meant to define a number variable and increment it inside the loop:
...
counter number := 0;
begin
...
while...loop
counter := counter+1;
end loop;
dbms_output.put)line('Coun
I would suggest just out-putting the collection COUNT from the example http:#a35007841
Active today
here is what I find
TYPE product_tab IS TABLE OF xx_license_interfaces_pkg.
INDEX BY BINARY_INTEGER;
v_inventory_item_id NUMBER;
TYPE product_tab IS TABLE OF xx_license_interfaces_pkg.
INDEX BY BINARY_INTEGER;
v_inventory_item_id NUMBER;
Active today
TYPE LICENSE_PRODUCT_TYPE IS RECORD(
BASE_VERSION xx_licensed_products.BASE_
LICENSED_PRODUCTS_ID xx_licensed_products.LICEN
LICENSE_ID xx_licensed_products.LICEN
.......
.........
.........
);
xx_licensed_products is a base table, all of those like base-version, licensed_products_id... are its fields
BASE_VERSION xx_licensed_products.BASE_
LICENSED_PRODUCTS_ID xx_licensed_products.LICEN
LICENSE_ID xx_licensed_products.LICEN
.......
.........
.........
);
xx_licensed_products is a base table, all of those like base-version, licensed_products_id... are its fields
Active today
Thanks for that.
Now did you try either the collection.count method suggested above to verify the collections holds what you think it holds?
Now did you try either the collection.count method suggested above to verify the collections holds what you think it holds?
Active today
I add the counter to the code but, I don't know how to apply your numlist to the code, can you provide the sample based on my example again?
Active today
counter number := 0;
BEGIN
v_inventory_item_id := p_dev_prods.FIRST;
WHILE v_inventory_item_id IS NOT NULL
LOOP
counter := counter+1;
v_out :=.....
......
end loop;
......
BEGIN
v_inventory_item_id := p_dev_prods.FIRST;
WHILE v_inventory_item_id IS NOT NULL
LOOP
counter := counter+1;
v_out :=.....
......
end loop;
......
Active today
Not the counter. On mobile right now and cannot reference a link above but here's the example:
Add a .COUNT to verify you have three rows:
....
dbms_output.put_line('Coun
BEGIN
v_inventory_item_id := p_dev_prods.FIRST;
...
Add a .COUNT to verify you have three rows:
....
dbms_output.put_line('Coun
BEGIN
v_inventory_item_id := p_dev_prods.FIRST;
...
Active today
after adding this line
dbms_output.put_line('Coun
I have the following error
Error(1730,21): PLS-00103: Encountered the symbol "." when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char time timestamp interval date binary national character nchar The symbol "<an identifier>" was substituted for "." to continue.
I don't know if my package can accept dbms_output.put_line, but I have a debug procedure I can output the p_dev_prods.count to a table
dbms_output.put_line('Coun
I have the following error
Error(1730,21): PLS-00103: Encountered the symbol "." when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char time timestamp interval date binary national character nchar The symbol "<an identifier>" was substituted for "." to continue.
I don't know if my package can accept dbms_output.put_line, but I have a debug procedure I can output the p_dev_prods.count to a table
Active today
Dmbs_output is a standard package. Not sure why it is causing an error.
If you can capture the output of the .count, that's all I'm after so have at it.
If the count shows 4 or more objects and the loop only shows 3, we have a problem and we'll go from there.
If you can capture the output of the .count, that's all I'm after so have at it.
If the count shows 4 or more objects and the loop only shows 3, we have a problem and we'll go from there.
Active today
if I use my debug procedure output to t able, i have code like
p_debug_log('Count1: ' || p_dev_prods.count);
or
p_debug_log('Count1: ' || to_char(p_dev_prods.count)
I have this error
Error(1730,21): PLS-00103: Encountered the symbol "(" when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char time timestamp interval date binary national character nchar The symbol "<an identifier>" was substituted for "(" to continue.
Active today
I can't help debug your other procedure code and why it doesn't accept those values.
I'll try to set up a simple test using your types tomorrow. I'm sure I cannot reproduce the loop error of your original question.
The docs state that is is basically impossible for a collection to have gaps and the loop code you posted should work.
So the answer to this question is one of two things:
1- you don't have as many objects in the collection as you think you do
Or
2- it's a bug and you need to contact Oracle Support.
If you can't get the output of the collection.count before you enter the loop, there is no way to figure out which.
I'll try to set up a simple test using your types tomorrow. I'm sure I cannot reproduce the loop error of your original question.
The docs state that is is basically impossible for a collection to have gaps and the loop code you posted should work.
So the answer to this question is one of two things:
1- you don't have as many objects in the collection as you think you do
Or
2- it's a bug and you need to contact Oracle Support.
If you can't get the output of the collection.count before you enter the loop, there is no way to figure out which.
Active today
8i is very old and anything is possible. The bad news is Oracle will not support you. The worse news, I doubt any Experts here have any 8i left where they can test this for you.
Active today
Never used either of those tools so I cannot help with syntax specific issues with them.
A quick Google shows SQL Developer can handle dbms_output:
http://forums.oracle.com/forums/thread.jspa?threadID=687127
At this point I cannot tell if the issue is with the 8i database or the tools. Can you connect directly to the database server and try from sqlplus?
A quick Google shows SQL Developer can handle dbms_output:
http://forums.oracle.com/forums/thread.jspa?threadID=687127
At this point I cannot tell if the issue is with the 8i database or the tools. Can you connect directly to the database server and try from sqlplus?
Active today
this code work
begin
dbms_output.enable;
dbms_output.put_line('Coun
end;
how do I catch the output, where to see the output of o_char(p_dev_prods.count?
begin
dbms_output.enable;
dbms_output.put_line('Coun
end;
how do I catch the output, where to see the output of o_char(p_dev_prods.count?
Featured Post
Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime? I'm talking things like:
* Dropping columns
* Shrinking allocated space
* Removing chained blocks and restoring the PCTFREE
* Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux.
Script may need to be modified depending on OS-installation.
Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 12 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
626 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.