Solved

why the loop cannot pick the next record?

Posted on 2011-02-28
35
337 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:wasabi3689
  • 17
  • 17
35 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35002484
Post your code that populates the list.  It's possible you have an empty item in the list.
0
 

Author Comment

by:wasabi3689
ID: 35002578
how? give me example
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35002637
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.
0
 

Author Comment

by:wasabi3689
ID: 35002695
FUNCTION format_unlicensed_text (p_dev_prods IN OUT product_tab)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35002739
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.
0
 

Author Comment

by:wasabi3689
ID: 35002791
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,
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35002833
It is a type created external to the package.

From a sql prompt: desc product_tab
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 222 total points
ID: 35003347
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.



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

Open in new window

0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 28 total points
ID: 35007734
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.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 222 total points
ID: 35007841
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('Count: ' || to_char(p_dev_prods.count));
BEGIN
         v_inventory_item_id := p_dev_prods.FIRST;
...
0
 

Author Comment

by:wasabi3689
ID: 35009901
I think my code is following this logic

counter := n.FIRST;

WHILE counter IS NOT NULL
      LOOP
         DBMS_OUTPUT.PUT_LINE('Element #' || counter || ' = ' || n(counter));
         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
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 222 total points
ID: 35010089
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('Count: ' || to_char(counter));

I would suggest just out-putting the collection COUNT from the example http:#a35007841

0
 

Author Comment

by:wasabi3689
ID: 35012555
here is what I find

TYPE product_tab IS TABLE OF xx_license_interfaces_pkg.license_product_type
      INDEX BY BINARY_INTEGER;

v_inventory_item_id   NUMBER;

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35013067
Now what is the definition of:
xx_license_interfaces_pkg.license_product_type
0
 

Author Comment

by:wasabi3689
ID: 35013809
TYPE LICENSE_PRODUCT_TYPE IS RECORD(
        BASE_VERSION             xx_licensed_products.BASE_VERSION%TYPE,
        LICENSED_PRODUCTS_ID     xx_licensed_products.LICENSED_PRODUCTS_ID%TYPE,
        LICENSE_ID               xx_licensed_products.LICENSE_ID%TYPE,
      .......
    .........
   .........
);

 xx_licensed_products is a base table, all of those like base-version, licensed_products_id... are its fields
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35013820
Thanks for that.

Now did you try either the collection.count method suggested above to verify the collections holds what you think it holds?
0
 

Author Comment

by:wasabi3689
ID: 35013843
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:wasabi3689
ID: 35013847
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;
......
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 222 total points
ID: 35013848
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('Count: ' || to_char(p_dev_prods.count));
BEGIN
         v_inventory_item_id := p_dev_prods.FIRST;
...

0
 

Author Comment

by:wasabi3689
ID: 35013894
after adding this line

dbms_output.put_line('Count: ' || to_char(p_dev_prods.count));


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
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 222 total points
ID: 35013915
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.
0
 

Author Comment

by:wasabi3689
ID: 35013947

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.
0
 

Author Comment

by:wasabi3689
ID: 35013961
anyone knows why I experience this error?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 222 total points
ID: 35013969
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.
0
 

Author Comment

by:wasabi3689
ID: 35013979
i am using oracle 8i, is this an issue to make this as  issue?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 222 total points
ID: 35013997
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.
0
 

Author Comment

by:wasabi3689
ID: 35014007
even I use

dbms_output.put_line('Count1: ');

I have the same error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35014014
Are you using sqlplus or some other tool to do all this?
0
 

Author Comment

by:wasabi3689
ID: 35014021
no sqlplus
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35014040
And.... What tool are you using?
0
 

Author Comment

by:wasabi3689
ID: 35014086
I use oracle sql developer, PL/SQL developer, i experience the same error
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 222 total points
ID: 35017029
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?
0
 

Author Comment

by:wasabi3689
ID: 35018710
this code work

begin

dbms_output.enable;
dbms_output.put_line('Count2:'||to_char(p_dev_prods.count));

end;

how do I catch the output, where to see the output of o_char(p_dev_prods.count?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35018856
You'll need to refer to the documentation for the tool you are using since I've never used any of those tools.

The link I provided mentioned some 'output' window in SQL Developer.
0
 

Author Closing Comment

by:wasabi3689
ID: 35236183
The issue remained unresolved yet but all the info provided here are helpful
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now