Home » SQL & PL/SQL » SQL & PL/SQL » How to control the loop (oracle 10g)
How to control the loop [message #439973] Wed, 20 January 2010 07:14 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
My problem is loop diidn't control by exit when statement. My program is..
SQL> DECLARE
  2  V_XML XMLTYPE;
  3  V_XML_IND XMLTYPE;
  4  V_NODE VARCHAR2(25);
  5  V_PART1 VARCHAR2(2000) ;
  6  V_PART2 VARCHAR2(2000) ;
  7  V_CNT NUMBER :=1;
  8  
  9  BEGIN
 10   select repository into v_xml from ddi_repos_t where object_id = 7;
 11   select '/ROWSET/ROW[' into v_part1 from dual;
 12  select ']' into v_part2 from dual;
 13  
 14   LOOP
 15   
 16  SELECT xml, NODES  
 17  INTO V_XML_IND, V_NODE 
 18  FROM 
 19  (select t2.column_value.getrootelement() nodes , xml
 20    from (select extract (V_XML,V_Part1 || v_cnt || V_Part2) xml FROM DUAL ) t, 
 21    table(xmlsequence(t.xml.extract('//node()'))) t2 )
 22    WHERE ROWNUM =1 AND NODES NOT IN ('ROWSET','ROW') ;
 23    EXIT WHEN V_XML_IND IS NULL;
 24    DBMS_OUTPUT.PUT_LINE(V_NODE);
 25    V_CNT := V_CNT + 1;
 26    END LOOP;
 27    
 28    END;
 29  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 16

Here my problem is even my loop run upto 100 times, it throws no data found error. But here i want to exit when v_xml_ind or v_node is null.
regards,
Madhavi.
Re: How to control the loop [message #439976 is a reply to message #439973] Wed, 20 January 2010 07:39 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Is there any oracle xml function used to get the count of child nodes of first level? For example, i have i root node with name rowset. Under ROWSET i have 10 sub nodes with name ROW. Under each row i have many (n) child nodes. I want to display the count of 1st level child nodes (here 10).
Is there any solution please?
This could be the solution for above loop problem.
Regards,
Madhavi.
Re: How to control the loop [message #439977 is a reply to message #439973] Wed, 20 January 2010 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
EXIT WHEN SQL%NOTFOUND;

Regards
Michel
Re: How to control the loop [message #439981 is a reply to message #439973] Wed, 20 January 2010 07:51 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Machel,
I tried with SQL%NOTFOUND but got same error.
Regards,
Madhavi.
Re: How to control the loop [message #439986 is a reply to message #439981] Wed, 20 January 2010 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare d varchar2(10);
  2  begin
  3    loop 
  4      begin
  5        select dummy into d from dual where 1 = 0;
  6      exception when no_data_found then exit;
  7      end;
  8    end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Regards
Michel
Re: How to control the loop [message #440064 is a reply to message #439973] Wed, 20 January 2010 21:56 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
It works fine. Thank you.
Regards,
Madhavi.

[Updated on: Wed, 20 January 2010 21:56]

Report message to a moderator

Previous Topic: Return entire records matching aggregated condition?
Next Topic: Pipe Delimitted file in CSV format
Goto Forum:
  


Current Time: Wed Sep 28 10:52:17 CDT 2016

Total time taken to generate the page: 0.10502 seconds