Home » SQL & PL/SQL » SQL & PL/SQL » Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument (Oracle 9i)
Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412102] Tue, 07 July 2009 15:52 Go to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
CREATE OR REPLACE TYPE PIPACK."OBJFCST"
AS OBJECT (JAN NUMBER(18,2),FEB NUMBER(18,2),MAR NUMBER(18,2),APR NUMBER(18,2),MAY NUMBER(18,2),JUN NUMBER(18,2),
JUL NUMBER(18,2),AUG NUMBER(18,2),SEP NUMBER(18,2),OCT NUMBER(18,2),NOV NUMBER(18,2), DEC NUMBER(18,2),
Q1 NUMBER(18,2), Q2 NUMBER(18,2), Q3 NUMBER(18,2),Q4 NUMBER(18,2), TOTAL NUMBER(18,2));
/

Type created.


CREATE OR REPLACE TYPE PIPACK.dept_vend_array
AS table OF OBJDEPTVENDOR

/

Type created.

create or replace type pipack.wholesaler
as object (dept number,
supp number,
channel varchar2(20),
basis varchar2(6),
cy_actuals objfcst,
ly_actuals objfcst,
fcst_object objfcst
)
/

Type created.

CREATE OR REPLACE TYPE PIPACK.wholesaler_array
as table of pipack.wholesaler

/

Type created.


1 create or replace PROCEDURE VPF_Load_Total_Fcst_wholesaler (i_dept_ven_list IN dept_vend_array
2 i_fcst_yr in number,
3 o_wholesaler OUT wholesaler_array,
4 o_comments out varchar2,
5 o_err_code out number,
6 o_err_msg out varchar2
7 ) is
8 c_chnl_cd department_vendor_list.chnl_cd%type;
9 l_vnd_typ varchar2(1);
10 l_chnl_cd varchar2(10);
11 l_basis_typ_cd department_vendor_list.basis_typ_cd%type;
12 cursor chnl_cursor(i_dept number,
13 i_vendor number)
14 is
15 select distinct chnl_cd, basis_typ_cd
16 from pipack.department_vendor_list
17 where dept_nbr = i_dept
18 and supplier_nbr = i_vendor
19 and fscl_yr = i_fcst_yr
20 and mstr_supplier_flg = 'N';
21 o_cy_actuals objfcst;
22 o_ly_actuals objfcst;
23 o_fcst_object objfcst;
24 o_saved_fcst_flg varchar2(1);
25 o_saved_pipackid number;
26 o_err_code number;
27 o_err_msg varchar2(2000);
28 j integer :=0;
29 k integer :=0;
30 begin
31 o_wholesaler := wholesaler(0,0,0,0,objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
32 objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
33 objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
34 );
35 for i in i_dept_ven_list.first..i_dept_ven_list.last
36 loop
37 j:= j+1;
38 o_wholesaler(j).dept := i_dept_ven_list(i).dept;
39 o_wholesaler(j).supp := i_dept_ven_list(i).vendor;
40 for k in chnl_cursor(i_dept_ven_list(i).dept,i_dept_ven_list(i).vendor)
41 LOOP
42 l:= l+1;
43 l_chnl_cd := k.chnl_cd;
44 l_basis_typ_cd := k.basis_typ_cd;
45 select decode(l_chnl_cd,'R','RETAIL','D','DELIVERY') into c_chnl_cd from dual;
46 o_wholesaler(l).channel := c_chnl_cd;
47 o_wholesaler(l).basis := l_basis_typ_cd;
48 Od_Vpf_Totalfcst_Package.Vpf_Load_Total_Actuals (
49 i_dept_ven_list(i).dept,
50 i_dept_ven_list(i).vendor,
51 i_FCST_YR,
52 l_basis_typ_cd,
53 c_chnl_cd,
54 'N',
55 o_cy_actuals,
56 o_ly_actuals,
57 o_fcst_object,
58 o_comments,
59 o_saved_fcst_flg,
60 o_saved_pipackid,
61 o_err_code,
62 o_err_msg
63 );
64 end loop;
65 end loop;
66* end VPF_Load_Total_Fcst_wholesaler;
67 /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE VPF_LOAD_TOTAL_FCST_WHOLESALER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted


I get this compilation the error says it at line I don't think there is any error over there. I would appreciate any thoughts and help.

Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412103 is a reply to message #412102] Tue, 07 July 2009 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I get this compilation the error says it at line I don't think there is any error over there
Either you are mistaken or you've found a bug.
I give big odds it is the former.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412105 is a reply to message #412103] Tue, 07 July 2009 16:08 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
BlackSwan, I didn't understand your response.
All I am trying to say is that the compilation error points to the error at line 1. But the error appears to be somewhere else.
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412106 is a reply to message #412105] Tue, 07 July 2009 16:15 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Sumitting the formatted version for proper Legible

CREATE OR REPLACE TYPE PIPACK."OBJFCST" 
AS OBJECT (JAN NUMBER(18,2),FEB NUMBER(18,2),MAR NUMBER(18,2),APR NUMBER(18,2),MAY NUMBER(18,2),JUN NUMBER(18,2),
JUL NUMBER(18,2),AUG NUMBER(18,2),SEP NUMBER(18,2),OCT NUMBER(18,2),NOV NUMBER(18,2), DEC NUMBER(18,2),
Q1 NUMBER(18,2), Q2 NUMBER(18,2), Q3 NUMBER(18,2),Q4 NUMBER(18,2), TOTAL NUMBER(18,2));
/

Type created.


CREATE OR REPLACE TYPE PIPACK.dept_vend_array 
AS table OF OBJDEPTVENDOR

/

Type created.

create or replace type pipack.wholesaler 
as object (dept number,
supp number,
channel varchar2(20),
basis varchar2(6),
cy_actuals objfcst,
ly_actuals objfcst,
fcst_object objfcst
)
/

Type created.

CREATE OR REPLACE TYPE PIPACK.wholesaler_array 
as table of pipack.wholesaler 

/

Type created.


1 create or replace PROCEDURE VPF_Load_Total_Fcst_wholesaler (i_dept_ven_list IN dept_vend_array
2 i_fcst_yr in number,
3 o_wholesaler OUT wholesaler_array,
4 o_comments out varchar2,
5 o_err_code out number,
6 o_err_msg out varchar2
7 ) is
8 c_chnl_cd department_vendor_list.chnl_cd%type;
9 l_vnd_typ varchar2(1);
10 l_chnl_cd varchar2(10);
11 l_basis_typ_cd department_vendor_list.basis_typ_cd%type;
12 cursor chnl_cursor(i_dept number,
13 i_vendor number)
14 is
15 select distinct chnl_cd, basis_typ_cd
16 from pipack.department_vendor_list
17 where dept_nbr = i_dept
18 and supplier_nbr = i_vendor
19 and fscl_yr = i_fcst_yr
20 and mstr_supplier_flg = 'N';
21 o_cy_actuals objfcst;
22 o_ly_actuals objfcst;
23 o_fcst_object objfcst;
24 o_saved_fcst_flg varchar2(1);
25 o_saved_pipackid number;
26 o_err_code number;
27 o_err_msg varchar2(2000);
28 j integer :=0;
29 k integer :=0;
30 begin
31 o_wholesaler := wholesaler(0,0,0,0,objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
32 objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
33 objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
34 );
35 for i in i_dept_ven_list.first..i_dept_ven_list.last
36 loop
37 j:= j+1;
38 o_wholesaler(j).dept := i_dept_ven_list(i).dept;
39 o_wholesaler(j).supp := i_dept_ven_list(i).vendor;
40 for k in chnl_cursor(i_dept_ven_list(i).dept,i_dept_ven_list(i).vendor)
41 LOOP
42 l:= l+1;
43 l_chnl_cd := k.chnl_cd;
44 l_basis_typ_cd := k.basis_typ_cd;
45 select decode(l_chnl_cd,'R','RETAIL','D','DELIVERY') into c_chnl_cd from dual;
46 o_wholesaler(l).channel := c_chnl_cd;
47 o_wholesaler(l).basis := l_basis_typ_cd;
48 Od_Vpf_Totalfcst_Package.Vpf_Load_Total_Actuals (
49 i_dept_ven_list(i).dept,
50 i_dept_ven_list(i).vendor,
51 i_FCST_YR,
52 l_basis_typ_cd,
53 c_chnl_cd,
54 'N',
55 o_cy_actuals,
56 o_ly_actuals,
57 o_fcst_object,
58 o_comments,
59 o_saved_fcst_flg,
60 o_saved_pipackid,
61 o_err_code,
62 o_err_msg
63 );
64 end loop;
65 end loop;
66* end VPF_Load_Total_Fcst_wholesaler;
67 /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE VPF_LOAD_TOTAL_FCST_WHOLESALER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted


Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412107 is a reply to message #412102] Tue, 07 July 2009 16:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
error message try hard to indicate what & where a problem exists.

CREATE OR REPLACE TYPE OBJFCST
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


SQL> 
SQL> CREATE OR REPLACE TYPE dept_vend_array
  2  AS table OF OBJDEPTVENDOR
  3  /

Warning: Type created with compilation errors.



I get different results, but that may be due to I have V10.2.0.4
& no access to V9.

where/what is "OBJDEPTVENDOR"?
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412108 is a reply to message #412107] Tue, 07 July 2009 16:34 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Sorry I missed this object below. But the procedure will not compile as it uses other procedures and tables.

CREATE OR REPLACE TYPE PIPACK."OBJDEPTVENDOR"                                           AS OBJECT (dept NUMBER(4), vendor NUMBER(10));
/
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412109 is a reply to message #412102] Tue, 07 July 2009 16:40 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
you are missing a comma at end of LINE 1!
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412110 is a reply to message #412109] Tue, 07 July 2009 16:45 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
that is a typo I am not missing that in my code.
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412113 is a reply to message #412110] Tue, 07 July 2009 16:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That was a "typo"?

So why did you re-type all that instead of copy and pasting the actual code and the actual error?

There is no point in trying to figure out the error until you post the ACTUAL code and the ACTUAL error, not some made-up re-typed stuff.
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412116 is a reply to message #412113] Tue, 07 July 2009 17:12 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
It is not a made up code I have cpoied it from the original code I am working on. The comma might have got lot in copying or formatting. Sorry about that.
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412122 is a reply to message #412102] Tue, 07 July 2009 19:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
If you'd simply follow Posting Guidelines & SEARCH
http://www.orafaq.com/search?keys=%22PLS-00410%22
you would have found the answer to the SYNTAX error
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412128 is a reply to message #412122] Tue, 07 July 2009 21:07 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Thanks I found it.
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412129 is a reply to message #412128] Tue, 07 July 2009 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
chintu00 wrote on Tue, 07 July 2009 19:07
Thanks I found it.



You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/


If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412162 is a reply to message #412129] Wed, 08 July 2009 01:14 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.


That Guy again declared in the function body the arguments he passed
26 o_err_code number;
27 o_err_msg varchar2(2000);
Re: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument [message #412305 is a reply to message #412162] Wed, 08 July 2009 10:37 Go to previous message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
yes the error was because the out variables were declared again.
Previous Topic: Rename or switching the table name
Next Topic: Regular expression
Goto Forum:
  


Current Time: Sat Dec 03 01:15:09 CST 2016

Total time taken to generate the page: 0.26516 seconds