Home » SQL & PL/SQL » SQL & PL/SQL » ora-30929 error inspite of having a connect by clause (oracle9i(9.0.1.1.1),XP(sp2))
ora-30929 error inspite of having a connect by clause [message #350741] Fri, 26 September 2008 01:57 Go to next message
pradmish
Messages: 4
Registered: September 2008
Location: India
Junior Member
IS sql statement containing a connect by .. order siblings by clause not allowed work as a cursor in a database procedure/package ?

I am getting ora-30929 error.

The procedure I am trying to create is :

create or replace procedure explode_bom (pdrgno IN VARCHAR2) IS
cursor c1 is
select level,COMP_DRG_NO, DESCR,UNIT_QTY,UNIT_CODE,POSN
from BOM
start with ASM_DRG_NO = pdrgno connect by ASM_DRG_NO = prior COMP_DRG_NO order siblings by posn;
begin
.....
.....
.....

end;
Re: ora-30929 error inspite of having a connect by clause [message #350743 is a reply to message #350741] Fri, 26 September 2008 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove all useless lines to make a test case, use SQL*PLus and copy and paste your session showing the error.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: ora-30929 error inspite of having a connect by clause [message #350747 is a reply to message #350741] Fri, 26 September 2008 02:15 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Welcome to Orafaq!
Please read Orafaq Forum Guide before posting.

Your code must be formatted!
Re: ora-30929 error inspite of having a connect by clause [message #350760 is a reply to message #350741] Fri, 26 September 2008 04:08 Go to previous messageGo to next message
pradmish
Messages: 4
Registered: September 2008
Location: India
Junior Member
./fa/5094/0/

It works fine as a select statement in SQL prompt with appropriate value for the root.

Why it is showing error in a cursor ?

I am using Oracle9i(9.0.1.1.1) on XP(SP2).
  • Attachment: SQLTEXT.JPG
    (Size: 56.29KB, Downloaded 700 times)
Re: ora-30929 error inspite of having a connect by clause [message #350770 is a reply to message #350760] Fri, 26 September 2008 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good now copy and paste the same thing as TEXT then I can copy and paste in SQL*PLus to reproduce it in my version.

Regards
Michel
icon1.gif  Re: ora-30929 error inspite of having a connect by clause [message #350791 is a reply to message #350741] Fri, 26 September 2008 06:33 Go to previous messageGo to next message
pradmish
Messages: 4
Registered: September 2008
Location: India
Junior Member
create or replace procedure bom_explode
	   (Pdrgno IN varchar2)
is	 
cursor bom_cur is
	   select level,comp_drg_no,asm_desc,unit_code,unit_qty,posn 
	   from bom
	   start with asm_drg_no = pdrgno
	   connect by asm_drg_no = prior comp_drg_no
	   order siblings by posn;
begin
	 for brec in bom_cur
	 loop
	 	 dbms_output.put_line(brec.level||', '||brec.comp_drg_no||', '||
		 		      brec.unit_qty||', '||brec.unit_code||', '||
		                      brec.posn);
	 end loop;
end;


This is the text.
Re: ora-30929 error inspite of having a connect by clause [message #350796 is a reply to message #350791] Fri, 26 September 2008 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace procedure bom_explode
  2      (Pdrgno IN varchar2)
  3  is  
  4  cursor bom_cur is
  5      select level,comp_drg_no,asm_desc,unit_code,unit_qty,posn 
  6      from bom
  7      start with asm_drg_no = pdrgno
  8      connect by asm_drg_no = prior comp_drg_no
  9      order siblings by posn;
 10  begin
 11    for brec in bom_cur
 12    loop
 13      dbms_output.put_line(brec.level||', '||brec.comp_drg_no||', '||
 14             brec.unit_qty||', '||brec.unit_code||', '||
 15                          brec.posn);
 16    end loop;
 17  end;
 18  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE BOM_EXPLODE:
LINE/COL
---------------------------------------------------------------------------------
ERROR
---------------------------------------------------------------------------------
5/5
PL/SQL: SQL Statement ignored
6/10
PL/SQL: ORA-00942: table or view does not exist
13/5
PL/SQL: Statement ignored
13/26
PLS-00364: loop index variable 'BREC' use is invalid

Maybe with BOM table I could do the same thing than you.

Regards
Michel
Re: ora-30929 error inspite of having a connect by clause [message #350894 is a reply to message #350741] Sat, 27 September 2008 00:07 Go to previous messageGo to next message
pradmish
Messages: 4
Registered: September 2008
Location: India
Junior Member
Thank you for your efforts.

The BOM table is
Create table BOM(
     asm_drg_no  varchar2(25),
     asm_descr   varchar2(70),
     comp_drg_no varchar2(25),
     unit_code   varchar2(3),
     unit_qty    number(7,3),
     posn        number(4));


asm_drg_no is parent of comp_drg_no. POSN is position number of component within an assembly (in drawings).

Regards.

PradMish
Re: ora-30929 error inspite of having a connect by clause [message #350896 is a reply to message #350894] Sat, 27 September 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> Create table BOM(
  2       asm_drg_no  varchar2(25),
  3       asm_descr   varchar2(70),
  4       comp_drg_no varchar2(25),
  5       unit_code   varchar2(3),
  6       unit_qty    number(7,3),
  7       posn        number(4));

Table created.

SQL> create or replace procedure bom_explode
  2      (Pdrgno IN varchar2)
  3  is  
  4  cursor bom_cur is
  5      select level,comp_drg_no,asm_desc,unit_code,unit_qty,posn 
  6      from bom
  7      start with asm_drg_no = pdrgno
  8      connect by asm_drg_no = prior comp_drg_no
  9      order siblings by posn;
 10  begin
 11    for brec in bom_cur
 12    loop
 13      dbms_output.put_line(brec.level||', '||brec.comp_drg_no||', '||
 14             brec.unit_qty||', '||brec.unit_code||', '||
 15                          brec.posn);
 16    end loop;
 17  end;
 18  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE BOM_EXPLODE:
LINE/COL
------------------------------------------------------------------------
ERROR
------------------------------------------------------------------------
5/5
PL/SQL: SQL Statement ignored
5/30
PL/SQL: ORA-00904: "ASM_DESC": invalid identifier
13/5
PL/SQL: Statement ignored
13/26
PLS-00364: loop index variable 'BREC' use is invalid
SQL> 5
  5*     select level,comp_drg_no,asm_desc,unit_code,unit_qty,posn
SQL> c:asm_desc:asm_descr
  5*     select level,comp_drg_no,asm_descr,unit_code,unit_qty,posn
SQL> /

Procedure created.

SQL> @v

Version Oracle : 9.2.0.8.0

So it seems this is a restriction in 9.0 which was the worst version that Oracle ever released.
I recommend you to upgrade.

Regards
Michel
Re: ora-30929 error inspite of having a connect by clause [message #350905 is a reply to message #350896] Sat, 27 September 2008 04:59 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Hello Mr.Michael,

I'm also faced this problem in Oracle 10g R1.

As per metalink suggestion we need disable oracle hidden parameter.

Metalink Doc ID: 430180.1

Babu
Re: ora-30929 error inspite of having a connect by clause [message #350920 is a reply to message #350905] Sat, 27 September 2008 11:58 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You misread Metalink note.
It says it only happens when the hidden parameter is set to a not default value. It should not happen with the default value.
(Maybe it is set by default to the wrong in 10.1 but I haven't this version which not quite good too... Hmmm, 10.2 optimizer is also full of bugs...)

Regards
Michel
Previous Topic: Inserting duplicate records
Next Topic: Need a Sql Statement for following scenario
Goto Forum:
  


Current Time: Fri Dec 09 21:34:49 CST 2016

Total time taken to generate the page: 0.08264 seconds