Home » SQL & PL/SQL » SQL & PL/SQL » merge statement PL/SQL: ORA-00933: SQL command not properly ended
merge statement PL/SQL: ORA-00933: SQL command not properly ended [message #237896] Wed, 16 May 2007 01:47 Go to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Following code is giving me 'PL/SQL: ORA-00933: SQL command not properly ended' error.
can any one help?
 Create or replace PROCEDURE pr_popu_item
 AS

 	 --- Declare constant Variables
	 l_date date :=sysdate;
  	c_Prgram_name CONSTANT VARCHAR2(255) := 'pr_popu_item';
	POPU_ITEM_DIM_ERR EXCEPTION;
    	PRAGMA EXCEPTION_INIT(POPU_ITEM_DIM_ERR, -24381);
begin
	MERGE INTO item_dim dest
  	USING (SELECT  
	 a.sku_cd      			     as item_key			--item key
	,a.rep_sku_cd           	 as rep_sku_cd    		-- rep sku code
	,a.sku_nm               	 as sku_name       		--sku name
	,a.sku_nm_kn            	 as sku_kn_name     		-- sku kn name
	,b.mkr_cd			         as maker_cd			--maker code
	,b.mkr_nm			         as maker_name			--maker name
	,b.mkr_nm_kn			     as maker_kn_name		--maker kn name
	,b.mkr_type  			     as add_maker_type		--add	maker type
	,b.year_crct_parent_mkr_cd 	 as parent_maker_code		--parent maker code
	,a.subclass_cd             	 as subclass_cd     		-- subclass code
	,c.subclass_nm		   	     as subclass_nm			--subclass name
	,c.subclass_nm_kn	   	     as subclass_kn_name		--subclass kn name
	,a.class_cd                	 as class_cd      		--class code
	,d.class_nm		   	         as class_nm			--class name
	,d.class_nm_kn		   	     as class_nm_kn			--class kn name
	,a.dept_cd                 	 as department_code     	--department code
	,e.dept_nm        	   	     as department_name		--department name
	,e.dept_nm_kn     	   	     as department_kn_name		--department kn name
	,e.sku_div_cd     		     as sku_division_code 		--sku division code
	,f.sku_div_nm     		     as sku_division_name 		--sku division name
	,f.sku_div_nm_kn  		     as sku_division_kn_name	--sku division kn name
	,a.sku_cls                   as sku_class         		--sku class
	,i.parent_tree_cd  		     as merchandising_section_code 	--merchandising section code
	,j.post_nm         		     as merchandising_section_name	--merchandising section name
	,a.jan_cd                    as jan_code         		--jan code
	,a.jan_cls                   as jan_class         		--jan class
	,a.nbpb_cls                  as pos_sku_name        	--pos sku name
	,a.lcns_cls                  as license_classification      --license classification
	,a.bsns_cond_flg_1 		     as planned_sale_class 		--planned sale class
	,z.item_nm         		     as planned_sale_class_name	--planned sale class name
	,a.latest_flg			     as latest_flag 		--latest flag
	,a.vald_st_date              as valid_start_date		--valid start date
	,a.vald_ed_date 		     as valid_end_date		--valid end date
	from  	 
	
		 ODS.wphdm_od_itm a
		,ODS.wphdm_oe_maker b
		,ODS.wphdm_od_subclass c
		,ODS.wphdm_od_dept_class d
		,ODS.wphdm_od_dept e
		,ODS.wphdm_od_itemdiv f
		,ODS.wphdm_oa_date_control h
		,ODS.wphdm_oa_name z -- to get planned sale class
		,ODS.wphdm_oa_gnr_tree_ent  i  -- to get merchandising code
		,ODS.wphdm_ob_department_basic j -- to get merchandising name
	where
	   (a.year_crct_mkr_cd = b.mkr_cd or a.newspaper_publisher_cd = b.mkr_cd) 
	  and a.dept_cd     = c.dept_cd
	  and a.class_cd    = c.class_cd
	  and a.subclass_cd = c.subclass_cd
	  and c.dept_cd  = d.dept_cd
	  and c.class_cd = d.class_cd
	  and d.dept_cd  = e.dept_cd
	  and e.sku_div_cd = f.sku_div_cd
	  and a.deny_flg = 0
	  and b.deny_flg = 0
	  and c.deny_flg = 0
	  and d.deny_flg = 0
	  and e.deny_flg = 0
	  and f.deny_flg = 0
	  and (h.today -1) between a.vald_st_date and a.vald_ed_date
	  and (h.today -1) between b.vald_st_date and b.vald_ed_date 
	  and (h.today -1) between c.vald_st_date and c.vald_ed_date 
	  and (h.today -1) between d.vald_st_date and d.vald_ed_date 
	  and (h.today -1) between e.vald_st_date and e.vald_ed_date 
	  and (h.today -1) between f.vald_st_date and f.vald_ed_date 
	  and h.company_cd = '000081' 
	  and h.system_cls = 'dw'
	  and z.use_cls ='108'
	  and z.nm_cd = a.bsns_cond_flg_1
	  and z.deny_flg = 0
	  and (h.today -1) between z.vald_st_date and z.vald_ed_date 
	  and a.dept_cd=i.child_tree_cd   
	  and i.parent_tree_cd=j.post_cd ) source
  	     ON (dest.rep_sku_code = source.rep_sku_cd
  	     	 and dest.first_valid_start_date = source.valid_start_date)
       WHEN MATCHED THEN
           UPDATE SET dest.sku_name 				= source.sku_name,
                      dest.sku_kn_name 				= source.sku_kn_name.
                      dest.maker_name				= source.maker_name,
                      dest.maker_kn_name			= source.maker_kn_name ,
                      dest.parent_maker_code    	= source.parent_maker_code,
                     -- dest.subclass_code			= source.subclass_cd,
                      dest.maker_code				= source.maker_cd,	
                      dest.subclass_name			= source.subclass_nm,
                      dest.subclass_kn_name			= source.subclass_kn_name,
                      dest.class_code				= source.class_cd ,
                      dest.class_name				= source.class_nm,
                      dest.class_kn_name			= source.class_nm_kn,	
                      dest.department_code			= source.department_code ,
                      dest.department_name			= source.department_name  ,
                      dest.department_kn_name			= source.department_kn_name,
                      dest.sku_division_code			= source.sku_division_code ,
                      dest.sku_division_name			= source.sku_division_name, 	
                      dest.sku_division_kn_name			= source.sku_division_kn_name ,
                      dest.merchandising_section_code		= source.merchandising_section_code,
                      dest.merchandising_section_name		= source.merchandising_section_name ,
                      dest.jan_code				= source.jan_code ,    
                      dest.jan_class				= source.jan_class, 
                      dest.pos_sku_name				= source.pos_sku_name, 
                      dest.license_classification		= source.license_classification	,
                      dest.planned_sale_class			= source.planned_sale_class ,	
                      dest.latest_flag				= source.latest_flag, 
                      dest.end_date				= l_date
                    						
       WHEN NOT MATCHED THEN					
            INSERT (dest.sku_name 					
            	    dest.sku_kn_name 				
            	    dest.maker_name				
            	    dest.maker_kn_name			
            	    dest.parent_maker_code			
            	    dest.parent_maker_name			
            	    dest.parent_maker_kn_name		
            	    dest.subclass_code			
            	    dest.maker_code				
            	    dest.subclass_name			
            	    dest.subclass_kn_name			
            	    dest.class_code				
            	    dest.class_name				
            	    dest.class_kn_name			
            	    dest.department_code			
            	    dest.department_name			
            	    dest.department_kn_name			
            	    dest.sku_division_code			
            	    dest.sku_division_name			
            	    dest.sku_division_kn_name			
            	    dest.gross_division_cd			
            	    dest.gross_division_name		
            	    dest.gross_division_kn_name		
            	    dest.merchandising_section_code		
            	    dest.merchandising_section_name		
            	    dest.merchandising_section_kn_name	
            	    dest.jan_code				
            	    dest.jan_class				
            	    dest.pos_sku_name				
            	    dest.item_category_flag			
            	    dest.license_classification		
            	    dest.planned_sale_class			
            	    dest.latest_flag				
            	    dest.end_date	)       
            
            VALUES (sku_name,	
            	    sku_kn_name.
            	    maker_name,
            	    maker_kn_name ,
            	    parent_maker_code,
            	    null,
            	    null,
            	    subclass_cd,
            	    maker_cd,	
            	    subclass_nm,
            	    subclass_kn_name,
            	    class_cd ,
            	    class_nm,
            	    class_nm_kn,	
            	    department_code ,
            	    department_name  ,
            	    department_kn_name,
            	    sku_division_code ,
            	    sku_division_name, 	
            	    sku_division_kn_name ,
            	    null,
            	    null,
            	    null,
            	    merchandising_section_code,
            	    merchandising_section_name ,
            	    null,
            	    jan_code ,    
            	    jan_class, 
            	    pos_sku_name, 
            	    null,
            	    license_classification	,
            	    planned_sale_class ,	
            	    latest_flag,
            	    l_date);
					     COMMIT; 
								
	EXCEPTION						
        WHEN POPU_ITEM_DIM_ERR THEN
          dbms_output.put_line(sqlcode||sqlerrm);    
	-- Call the procedure for drop partition of 14 months back as per Retention policy.

    WHEN OTHERS THEN
    dbms_output.put_line(sqlcode||sqlerrm);
    -- Do appropriate as per logging logic.
END PR_POPU_ITEM;
/



Re: merge statement PL/SQL: ORA-00933: SQL command not properly ended [message #237900 is a reply to message #237896] Wed, 16 May 2007 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think we are syntax analyzer.
Post a copy and paste of your execution in SQL*Plus with line numbers and full error message.

Regards
Michel
Re: merge statement PL/SQL: ORA-00933: SQL command not properly ended [message #237904 is a reply to message #237900] Wed, 16 May 2007 01:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Come on Michel, don't you see it? Wink

@janardhanam.k:
1. you are missing comma's in the insert part.
2. check whether you haven't made a typo (a "." instead of a ",") at the end of some lines.

MHE

[Updated on: Wed, 16 May 2007 01:59]

Report message to a moderator

Re: merge statement PL/SQL: ORA-00933: SQL command not properly ended [message #237938 is a reply to message #237904] Wed, 16 May 2007 03:06 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Thanks for your reply.I changed the code as per your suggestions.Iam listing it along with line numbers below.But iam getting following error messages:


LINE/COL ERROR
-------- -----------------------------------------------------------------
122/8 PLS-00103: Encountered the symbol "WHEN" when expecting one of
the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe
The symbol "case" was substituted for "WHEN" to continue.

123/20 PLS-00103: Encountered the symbol "(" when expecting one of the

LINE/COL ERROR
-------- -----------------------------------------------------------------
following:
into <a SQL statement>

157/13 PLS-00103: Encountered the symbol "VALUES" when expecting one of
the following:
, * & - + / at mod remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || multiset bulk year DAY_
The symbol ", was inserted before "VALUES" to continue.

190/25 PLS-00103: Encountered the symbol ";" when expecting one of the

LINE/COL ERROR
-------- -----------------------------------------------------------------
following:
, * & - + / at mod remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || multiset bulk year DAY_

1Create or replace PROCEDURE pr_popu_item
2 AS

 	 --- Declare constant Variables
3 	c_Prgram_name CONSTANT VARCHAR2(255) := 'pr_popu_item';
4	POPU_ITEM_DIM_ERR EXCEPTION;
5   	PRAGMA EXCEPTION_INIT(POPU_ITEM_DIM_ERR, -24381);
6 begin
7	po_arror_area :=  'Calling Partition Proc';
8	------ Call the procedure for drop/split partition
9
10       MERGE INTO item_dim dest
11  	USING (SELECT  
12	 a.sku_cd      			 as item_key			--item key
13	,a.rep_sku_cd           	 as rep_sku_cd    		-- rep sku code
14	,a.sku_nm               	 as sku_name       		--sku name
15	,a.sku_nm_kn            	 as sku_kn_name     		-- sku kn name
16	,b.mkr_cd			 as maker_cd			--maker code
17	,b.mkr_nm			 as maker_name			--maker name
18	,b.mkr_nm_kn			 as maker_kn_name		--maker kn name
19	,b.mkr_type  			 as add_maker_type		--add	maker type
20	,b.year_crct_parent_mkr_cd 	 as parent_maker_code		--parent maker code
21	,a.subclass_cd             	 as subclass_cd     		-- subclass code
22	,c.subclass_nm		   	 as subclass_nm			--subclass name
23	,c.subclass_nm_kn	   	 as subclass_kn_name		--subclass kn name
24	,a.class_cd                	 as class_cd      		--class code
25	,d.class_nm		   	 as class_nm			--class name
26	,d.class_nm_kn		   	 as class_nm_kn			--class kn name
27	,a.dept_cd                 	 as department_code     	--department code
28	,e.dept_nm        	   	 as department_name		--department name
29	,e.dept_nm_kn     	   	 as department_kn_name		--department kn name
30	,e.sku_div_cd     		 as sku_division_code 		--sku division code
31	,f.sku_div_nm     		 as sku_division_name 		--sku division name
32	,f.sku_div_nm_kn  		 as sku_division_kn_name	--sku division kn name
33	,a.sku_cls                       as sku_class         		--sku class
34	,i.parent_tree_cd  		 as merchandising_section_code 	--merchandising section code
35	,j.post_nm         		 as merchandising_section_name	--merchandising section name
36	,a.jan_cd                        as jan_code         		--jan code
37	,a.jan_cls                       as jan_class         		--jan class
38	,a.nbpb_cls                      as pos_sku_name        	--pos sku name
39	,a.lcns_cls                      as license_classification      --license classification
40	,a.bsns_cond_flg_1 		 as planned_sale_class 		--planned sale class
41	,z.item_nm         		 as planned_sale_class_name	--planned sale class name
42	,a.latest_flg			 as latest_flag 		--latest flag
43	,a.vald_st_date                  as valid_start_date		--valid start date
44	,a.vald_ed_date 		 as valid_end_date		--valid end date
45	from  	 
46		 ODS.wphdm_od_itm a
47		,ODS.wphdm_oe_maker b
48		,ODS.wphdm_od_subclass c
49		,ODS.wphdm_od_dept_class d
50		,ODS.wphdm_od_dept e
51		,ODS.wphdm_od_itemdiv f
52		,ODS.wphdm_oa_date_control h
53		,ODS.wphdm_oa_name z -- to get planned sale class
54		,ODS.wphdm_oa_gnr_tree_ent  i  -- to get merchandising code
55		,ODS.wphdm_ob_department_basic j -- to get merchandising name
56	where
57	   (a.year_crct_mkr_cd = b.mkr_cd or a.newspaper_publisher_cd = b.mkr_cd) 
58	  and a.dept_cd     = c.dept_cd
59	  and a.class_cd    = c.class_cd
60	  and a.subclass_cd = c.subclass_cd
61	  and c.dept_cd  = d.dept_cd
62	  and c.class_cd = d.class_cd
63	  and d.dept_cd  = e.dept_cd
64	  and e.sku_div_cd = f.sku_div_cd
65	  and a.deny_flg = 0
66	  and b.deny_flg = 0
67	  and c.deny_flg = 0
68	  and d.deny_flg = 0
69	  and e.deny_flg = 0
70	  and f.deny_flg = 0
71	  and (h.today -1) between a.vald_st_date and a.vald_ed_date
72	  and (h.today -1) between b.vald_st_date and b.vald_ed_date 
73	  and (h.today -1) between c.vald_st_date and c.vald_ed_date 
74	  and (h.today -1) between d.vald_st_date and d.vald_ed_date 
75	  and (h.today -1) between e.vald_st_date and e.vald_ed_date 
76	  and (h.today -1) between f.vald_st_date and f.vald_ed_date 
77	  and h.company_cd = '000081' 
78	  and h.system_cls = 'dw'
79	  and z.use_cls ='108'
80	  and z.nm_cd = a.bsns_cond_flg_1
81	  and z.deny_flg = 0
82	  and (h.today -1) between z.vald_st_date and z.vald_ed_date 
83	  and a.dept_cd=i.child_tree_cd   
84	  and i.parent_tree_cd=j.post_cd ) source
85 	     ON (dest.rep_sku_code = source.rep_sku_cd
86  	     	 and dest.first_valid_start_date = source.valid_start_date)
87       WHEN MATCHED THEN
88           UPDATE SET dest.sku_name 				= source.sku_name,
89                      dest.sku_kn_name 				= source.sku_kn_name,
90                      dest.maker_name				= source.maker_name,
91                      dest.maker_kn_name			= source.maker_kn_name ,
92                      dest.parent_maker_code			= source.parent_maker_code,
93                      --dest.parent_maker_name			= source.
94                      --dest.parent_maker_kn_name		= source.
95                      dest.subclass_code			= source.subclass_cd,
96                      dest.maker_code				= source.maker_cd,	
97                      dest.subclass_name			= source.subclass_nm,
98                      dest.subclass_kn_name			= source.subclass_kn_name,
99                      dest.class_code				= source.class_cd ,
100                      dest.class_name				= source.class_nm,
101                      dest.class_kn_name			= source.class_nm_kn,	
102                      dest.department_code			= source.department_code ,
103                      dest.department_name			= source.department_name  ,
104                      dest.department_kn_name			= source.department_kn_name,
105                      dest.sku_division_code			= source.sku_division_code ,
106                      dest.sku_division_name			= source.sku_division_name, 	
107                      dest.sku_division_kn_name			= source.sku_division_kn_name ,
108                      --dest.gross_division_cd			= source.
109                      --dest.gross_division_name		= source.
110                      --dest.gross_division_kn_name		= source.
111                      dest.merchandising_section_code		= source.merchandising_section_code,
112                      dest.merchandising_section_name		= source.merchandising_section_name ,
113                      --dest.merchandising_section_kn_name	= source.
114                      dest.jan_code				= source.jan_code ,    
115                      dest.jan_class				= source.jan_class, 
116                      dest.pos_sku_name				= source.pos_sku_name, 
117                      --dest.item_category_flag			= source.
118                      dest.license_classification		= source.license_classification	,
119                      dest.planned_sale_class			= source.planned_sale_class ,	
120                      dest.latest_flag				= source.latest_flag, 
121                      dest.end_date				= l_date;
            						
122       WHEN NOT MATCHED THEN					
123            INSERT (dest.sku_name, 					
124            	    dest.sku_kn_name ,				
125            	    dest.maker_name,				
126            	    dest.maker_kn_name,			
127            	    dest.parent_maker_code,			
128            	    --dest.parent_maker_name,			
129            	    --dest.parent_maker_kn_name,		
130            	    dest.subclass_code	,		
131            	    dest.maker_code,				
132            	    dest.subclass_name	,		
133            	    dest.subclass_kn_name,			
134            	    dest.class_code,				
135            	    dest.class_name,				
136            	    dest.class_kn_name	,		
137            	    dest.department_code,			
138            	    dest.department_name,			
139            	    dest.department_kn_name,			
140            	    dest.sku_division_code,			
141            	    dest.sku_division_name,			
142            	    dest.sku_division_kn_name,			
143            	    --dest.gross_division_cd,			
144            	    --dest.gross_division_name,		
145            	    --dest.gross_division_kn_name,		
146            	    dest.merchandising_section_code,		
147            	    dest.merchandising_section_name,		
148            	    --dest.merchandising_section_kn_name,	
149            	    dest.jan_code,				
150            	    dest.jan_class,				
151            	    dest.pos_sku_name	,			
152            	    --dest.item_category_flag,			
153            	    dest.license_classification,		
154            	    dest.planned_sale_class,			
155            	    dest.latest_flag,				
156            	    dest.end_date	)       
            
157            VALUES (sku_name,	
158            	    sku_kn_name.
159            	    maker_name,
160            	    maker_kn_name ,
161            	    parent_maker_code,
162            	    null,
163            	    null,
164            	    subclass_cd,
165            	    maker_cd,	
166            	    subclass_nm,
167            	    subclass_kn_name,
168            	    class_cd ,
169            	    class_nm,
170            	    class_nm_kn,	
171            	    department_code ,
172            	    department_name  ,
173            	    department_kn_name,
174            	    sku_division_code ,
175            	    sku_division_name, 	
176            	    sku_division_kn_name ,
177            	    null,
178            	    null,
179            	    null,
180            	    merchandising_section_code,
181            	    merchandising_section_name ,
182            	    null,
183            	    jan_code ,    
184            	    jan_class, 
185            	    pos_sku_name, 
186            	    null,
187            	    license_classification	,
188            	    planned_sale_class ,	
189            	    latest_flag,
190            	    l_date); 
								
191	EXCEPTION						
192        WHEN POPU_ITEM_DIM_ERR THEN
193          dbms_output.put_line(sqlcode||sqlerrm);    
194		
195     COMMIT;
     
    ------ Call the procedure for drop partition of 14 months back as per Retention policy.
196 EXCEPTION
197    WHEN OTHERS THEN
198    dbms_output.put_line(sqlcode||sqlerrm);
199    -- Do appropriate as per logging logic.
200 END PR_POPU_ITEM;
/
Re: merge statement PL/SQL: ORA-00933: SQL command not properly ended [message #237939 is a reply to message #237938] Wed, 16 May 2007 03:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
line 121: a ; denotes the end of a statement.
Re: merge statement PL/SQL: ORA-00933: SQL command not properly ended [message #237979 is a reply to message #237939] Wed, 16 May 2007 04:25 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
thanks for your reply.i removed the ';' and its giving error message as "
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8 PL/SQL: SQL Statement ignored
53/8 PL/SQL: ORA-00942: table or view does not exist".
Can any one help.all the tables used are present in the DB.
iam pasting the code with line number here:
Create or replace PROCEDURE pr_popu_item
 AS

 	 --- Declare constant Variables
  	c_Prgram_name CONSTANT VARCHAR2(255) := 'pr_popu_item';
	POPU_ITEM_DIM_ERR EXCEPTION;
    	PRAGMA EXCEPTION_INIT(POPU_ITEM_DIM_ERR, -24381);
begin
	
	------ Call the procedure for drop/split partition

       MERGE INTO DIM_ITM dest
  	USING (SELECT  
	 a.sku_cd      			 as item_key			--item key
	,a.rep_sku_cd           	 as rep_sku_cd    		-- rep sku code
	,a.sku_nm               	 as sku_name       		--sku name
	,a.sku_nm_kn            	 as sku_kn_name     		-- sku kn name
	,b.mkr_cd			 as maker_cd			--maker code
	,b.mkr_nm			 as maker_name			--maker name
	,b.mkr_nm_kn			 as maker_kn_name		--maker kn name
	,b.mkr_type  			 as add_maker_type		--add	maker type
	,b.year_crct_parent_mkr_cd 	 as parent_maker_code		--parent maker code
	,a.subclass_cd             	 as subclass_cd     		-- subclass code
	,c.subclass_nm		   	 as subclass_nm			--subclass name
	,c.subclass_nm_kn	   	 as subclass_kn_name		--subclass kn name
	,a.class_cd                	 as class_cd      		--class code
	,d.class_nm		   	 as class_nm			--class name
	,d.class_nm_kn		   	 as class_nm_kn			--class kn name
	,a.dept_cd                 	 as department_code     	--department code
	,e.dept_nm        	   	 as department_name		--department name
	,e.dept_nm_kn     	   	 as department_kn_name		--department kn name
	,e.sku_div_cd     		 as sku_division_code 		--sku division code
	,f.sku_div_nm     		 as sku_division_name 		--sku division name
	,f.sku_div_nm_kn  		 as sku_division_kn_name	--sku division kn name
	,a.sku_cls                       as sku_class         		--sku class
	,i.parent_tree_cd  		 as merchandising_section_code 	--merchandising section code
	,j.post_nm         		 as merchandising_section_name	--merchandising section name
	,a.jan_cd                        as jan_code         		--jan code
	,a.jan_cls                       as jan_class         		--jan class
	,a.nbpb_cls                      as pos_sku_name        	--pos sku name
	,a.lcns_cls                      as license_classification      --license classification
	,a.bsns_cond_flg_1 		 as planned_sale_class 		--planned sale class
	,z.item_nm         		 as planned_sale_class_name	--planned sale class name
	,a.latest_flg			 as latest_flag 		--latest flag
	,a.vald_st_date                  as valid_start_date		--valid start date
	,a.vald_ed_date 		 as valid_end_date		--valid end date
	from  	 
	
		 ODS.wphdm_od_itm a
		,ODS.wphdm_oe_maker b
		,ODS.wphdm_od_subclass c
		,ODS.wphdm_od_dept_class d
		,ODS.wphdm_od_dept e
		,ODS.wphdm_od_itemdiv f
		,ODS.wphdm_oa_date_control h
		,ODS.wphdm_oa_name z -- to get planned sale class
		,ODS.wphdm_oa_gnr_tree_ent  i  -- to get merchandising code
		,ODS.wphdm_ob_department_basic j -- to get merchandising name
	where
	   (a.year_crct_mkr_cd = b.mkr_cd or a.newspaper_publisher_cd = b.mkr_cd) 
	  and a.dept_cd     = c.dept_cd
	  and a.class_cd    = c.class_cd
	  and a.subclass_cd = c.subclass_cd
	  and c.dept_cd  = d.dept_cd
	  and c.class_cd = d.class_cd
	  and d.dept_cd  = e.dept_cd
	  and e.sku_div_cd = f.sku_div_cd
	  and a.deny_flg = 0
	  and b.deny_flg = 0
	  and c.deny_flg = 0
	  and d.deny_flg = 0
	  and e.deny_flg = 0
	  and f.deny_flg = 0
	  and (h.today -1) between a.vald_st_date and a.vald_ed_date
	  and (h.today -1) between b.vald_st_date and b.vald_ed_date 
	  and (h.today -1) between c.vald_st_date and c.vald_ed_date 
	  and (h.today -1) between d.vald_st_date and d.vald_ed_date 
	  and (h.today -1) between e.vald_st_date and e.vald_ed_date 
	  and (h.today -1) between f.vald_st_date and f.vald_ed_date 
	  and h.company_cd = '000081' 
	  and h.system_cls = 'dw'
	  and z.use_cls ='108'
	  and z.nm_cd = a.bsns_cond_flg_1
	  and z.deny_flg = 0
	  and (h.today -1) between z.vald_st_date and z.vald_ed_date 
	  and a.dept_cd=i.child_tree_cd   
	  and i.parent_tree_cd=j.post_cd ) source
  	     ON (dest.rep_sku_code = source.rep_sku_cd
  	     	 and dest.first_valid_start_date = source.valid_start_date)
       WHEN MATCHED THEN
           UPDATE SET dest.sku_name 				= source.sku_name,
                      dest.sku_kn_name 				= source.sku_kn_name,
                      dest.maker_name				= source.maker_name,
                      dest.maker_kn_name			= source.maker_kn_name ,
                      dest.parent_maker_code			= source.parent_maker_code,
                      --dest.parent_maker_name			= source.
                      --dest.parent_maker_kn_name		= source.
                      dest.subclass_code			= source.subclass_cd,
                      dest.maker_code				= source.maker_cd,	
                      dest.subclass_name			= source.subclass_nm,
                      dest.subclass_kn_name			= source.subclass_kn_name,
                      dest.class_code				= source.class_cd ,
                      dest.class_name				= source.class_nm,
                      dest.class_kn_name			= source.class_nm_kn,	
                      dest.department_code			= source.department_code ,
                      dest.department_name			= source.department_name  ,
                      dest.department_kn_name			= source.department_kn_name,
                      dest.sku_division_code			= source.sku_division_code ,
                      dest.sku_division_name			= source.sku_division_name, 	
                      dest.sku_division_kn_name			= source.sku_division_kn_name ,
                      --dest.gross_division_cd			= source.
                      --dest.gross_division_name		= source.
                      --dest.gross_division_kn_name		= source.
                      dest.merchandising_section_code		= source.merchandising_section_code,
                      dest.merchandising_section_name		= source.merchandising_section_name ,
                      --dest.merchandising_section_kn_name	= source.
                      dest.jan_code				= source.jan_code ,    
                      dest.jan_class				= source.jan_class, 
                      dest.pos_sku_name				= source.pos_sku_name, 
                      --dest.item_category_flag			= source.
                      dest.license_classification		= source.license_classification	,
                      dest.planned_sale_class			= source.planned_sale_class ,	
                      dest.latest_flag				= source.latest_flag, 
                      dest.end_date				= l_date
                    						
       WHEN NOT MATCHED THEN					
            INSERT (dest.sku_name, 					
            	    dest.sku_kn_name ,				
            	    dest.maker_name,				
            	    dest.maker_kn_name,			
            	    dest.parent_maker_code,			
            	    --dest.parent_maker_name,			
            	    --dest.parent_maker_kn_name,		
            	    dest.subclass_code	,		
            	    dest.maker_code,				
            	    dest.subclass_name	,		
            	    dest.subclass_kn_name,			
            	    dest.class_code,				
            	    dest.class_name,				
            	    dest.class_kn_name	,		
            	    dest.department_code,			
            	    dest.department_name,			
            	    dest.department_kn_name,			
            	    dest.sku_division_code,			
            	    dest.sku_division_name,			
            	    dest.sku_division_kn_name,			
            	    --dest.gross_division_cd,			
            	    --dest.gross_division_name,		
            	    --dest.gross_division_kn_name,		
            	    dest.merchandising_section_code,		
            	    dest.merchandising_section_name,		
            	    --dest.merchandising_section_kn_name,	
            	    dest.jan_code,				
            	    dest.jan_class,				
            	    dest.pos_sku_name	,			
            	    --dest.item_category_flag,			
            	    dest.license_classification,		
            	    dest.planned_sale_class,			
            	    dest.latest_flag,				
            	    dest.end_date	)       
            
            VALUES (sku_name,	
            	    sku_kn_name.
            	    maker_name,
            	    maker_kn_name ,
            	    parent_maker_code,
            	    null,
            	    null,
            	    subclass_cd,
            	    maker_cd,	
            	    subclass_nm,
            	    subclass_kn_name,
            	    class_cd ,
            	    class_nm,
            	    class_nm_kn,	
            	    department_code ,
            	    department_name  ,
            	    department_kn_name,
            	    sku_division_code ,
            	    sku_division_name, 	
            	    sku_division_kn_name ,
            	    null,
            	    null,
            	    null,
            	    merchandising_section_code,
            	    merchandising_section_name ,
            	    null,
            	    jan_code ,    
            	    jan_class, 
            	    pos_sku_name, 
            	    null,
            	    license_classification	,
            	    planned_sale_class ,	
            	    latest_flag,
            	    l_date); 
            	         COMMIT;

								
	EXCEPTION						
        WHEN POPU_ITEM_DIM_ERR THEN
          dbms_output.put_line(sqlcode||sqlerrm);    
		
     
    ------ Call the procedure for drop partition of 14 months back as per Retention policy.

    WHEN OTHERS THEN
    dbms_output.put_line(sqlcode||sqlerrm);
    -- Do appropriate as per logging logic.
END PR_POPU_ITEM;
/
PL/SQL: ORA-00913 [message #238004 is a reply to message #237939] Wed, 16 May 2007 05:53 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Code attached with this mail gives me the following error :

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8 PL/SQL: SQL Statement ignored
9/19 PL/SQL: ORA-00913: too many values
Can any one help?



 1   Create or replace PROCEDURE pr_popu_item
  2   AS
  3      --- Declare constant Variables
  4     c_Prgram_name CONSTANT VARCHAR2(255) := 'pr_popu_item';
  5     POPU_ITEM_DIM_ERR EXCEPTION;
  6             PRAGMA EXCEPTION_INIT(POPU_ITEM_DIM_ERR, -24381);
  7  begin
  8     ------ Call the procedure for drop/split partition
  9         MERGE INTO DIM_ITM dest
 10     USING (SELECT
 11      a.sku_cd                        as item_key                    --item key
 12     ,a.rep_sku_cd                    as rep_sku_cd                  -- rep sku code
 13     ,a.sku_nm                        as sku_name                    --sku name
 14     ,a.sku_nm_kn                     as sku_kn_name                 -- sku kn name
 15     ,b.mkr_cd                        as maker_cd                    --maker code
 16     ,b.mkr_nm                        as maker_name                  --maker name
 17     ,b.mkr_nm_kn                     as maker_kn_name               --maker kn name
 18     ,b.mkr_type                      as add_maker_type              --add   maker type
 19     ,b.year_crct_parent_mkr_cd       as parent_maker_code           --parent maker code
 20     ,a.subclass_cd                   as subclass_cd                 -- subclass code
 21     ,c.subclass_nm                   as subclass_nm                 --subclass name
 22     ,c.subclass_nm_kn                as subclass_kn_name            --subclass kn name
 23     ,a.class_cd                      as class_cd                    --class code
 24     ,d.class_nm                      as class_nm                    --class name
 25     ,d.class_nm_kn                   as class_nm_kn                 --class kn name
 26     ,a.dept_cd                       as department_code             --department code
 27     ,e.dept_nm                       as department_name             --department name
 28     ,e.dept_nm_kn                    as department_kn_name          --department kn name
 29     ,e.sku_div_cd                    as sku_division_code           --sku division code
 30     ,f.sku_div_nm                    as sku_division_name           --sku division name
 31     ,f.sku_div_nm_kn                 as sku_division_kn_name        --sku division kn name
 32     ,a.sku_cls                       as sku_class                   --sku class
 33     ,i.parent_tree_cd                as merchandising_section_code  --merchandising section code
 34     ,j.post_nm                       as merchandising_section_name  --merchandising section name
 35     ,a.jan_cd                        as jan_code                    --jan code
 36     ,a.jan_cls                       as jan_class                   --jan class
 37     ,a.nbpb_cls                      as pos_sku_name                --pos sku name
 38     ,a.lcns_cls                      as license_classification      --license classification
 39     ,a.bsns_cond_flg_1               as planned_sale_class          --planned sale class
 40     ,z.item_nm                       as planned_sale_class_name     --planned sale class name
 41     ,a.latest_flg                    as latest_flag                 --latest flag
 42     ,a.vald_st_date                  as valid_start_date            --valid start date
 43     ,a.vald_ed_date                  as valid_end_date              --valid end date
 44     from    
 45              ODS.wphdm_od_itm a
 46             ,ODS.wphdm_oe_maker b
 47             ,ODS.wphdm_od_subclass c
 48             ,ODS.wphdm_od_dept_class d
 49             ,ODS.wphdm_od_dept e
 50             ,ODS.wphdm_od_itemdiv f
 51             ,ODS.wphdm_oa_date_control h
 52             ,ODS.wphdm_oa_name z -- to get planned sale class
 53             ,ODS.wphdm_oa_gnr_tree_ent  i  -- to get merchandising code
 54             ,ODS.wphdm_ob_department_basic j -- to get merchandising name
 55     where
 56        (a.year_crct_mkr_cd = b.mkr_cd or a.newspaper_publisher_cd = b.mkr_cd)
 57       and a.dept_cd     = c.dept_cd
 58       and a.class_cd    = c.class_cd
 59       and a.subclass_cd = c.subclass_cd
 60       and c.dept_cd  = d.dept_cd
 61       and c.class_cd = d.class_cd
 62       and d.dept_cd  = e.dept_cd
 63       and e.sku_div_cd = f.sku_div_cd
 64       and a.deny_flg = 0
 65       and b.deny_flg = 0
 66       and c.deny_flg = 0
 67       and d.deny_flg = 0
 68       and e.deny_flg = 0
 69       and f.deny_flg = 0
 70       and (h.today -1) between a.vald_st_date and a.vald_ed_date
 71       and (h.today -1) between b.vald_st_date and b.vald_ed_date
 72       and (h.today -1) between c.vald_st_date and c.vald_ed_date
 73       and (h.today -1) between d.vald_st_date and d.vald_ed_date
 74       and (h.today -1) between e.vald_st_date and e.vald_ed_date
 75       and (h.today -1) between f.vald_st_date and f.vald_ed_date
 76       and h.company_cd = '000081'
 77       and h.system_cls = 'dw'
 78       and z.use_cls ='108'
 79       and z.nm_cd = a.bsns_cond_flg_1
 80       and z.deny_flg = 0
 81       and (h.today -1) between z.vald_st_date and z.vald_ed_date
 82       and a.dept_cd=i.child_tree_cd
 83       and i.parent_tree_cd=j.post_cd ) source
 84          ON (dest.rep_sku_code = source.rep_sku_cd
 85              and dest.valid_start_date = source.valid_start_date)
 86         WHEN MATCHED THEN
 87             UPDATE SET dest.sku_name                                = source.sku_name,
 88                        dest.sku_kn_name                             = source.sku_kn_name,
 89                        dest.maker_name                              = source.maker_name,
 90                        dest.maker_kn_name                   = source.maker_kn_name ,
 91                        dest.parent_maker_code                       = source.parent_maker_code,
 92                        --dest.parent_maker_name                     = source.
 93                        --dest.parent_maker_kn_name          = source.
 94                        dest.subclass_code                   = source.subclass_cd,
 95                        dest.maker_code                              = source.maker_cd,      
 96                        dest.subclass_name                   = source.subclass_nm,
 97                        dest.subclass_kn_name                        = source.subclass_kn_name,
 98                        dest.class_code                              = source.class_cd ,
 99                        dest.class_name                              = source.class_nm,
100                        dest.class_kn_name                   = source.class_nm_kn,   
101                        dest.department_code                 = source.department_code ,
102                        dest.department_name                 = source.department_name  ,
103                        dest.department_kn_name                      = source.department_kn_name,
104                        dest.sku_division_code                       = source.sku_division_code ,
105                        dest.sku_division_name                       = source.sku_division_name,     
106                        dest.sku_division_kn_name                    = source.sku_division_kn_name ,
107                        --dest.gross_division_cd                     = source.
108                        --dest.gross_division_name           = source.
109                        --dest.gross_division_kn_name                = source.
110                        dest.merchandising_section_code              = source.merchandising_section_code,
111                        dest.merchandising_section_name              = source.merchandising_section_name ,
112                        --dest.merchandising_section_kn_name = source.
113                        dest.jan_code                                = source.jan_code ,
114                        dest.jan_class                               = source.jan_class,
115                        dest.pos_sku_name                            = source.pos_sku_name,
116                        --dest.item_category_flag                    = source.
117                        dest.license_classification          = source.license_classification ,
118                        dest.planned_sale_class                      = source.planned_sale_class ,   
119                        dest.latest_flag                             = source.latest_flag,
120                        dest.end_date                                = l_date
121         WHEN NOT MATCHED THEN                                       
122              INSERT (dest.sku_name,                                         
123                         dest.sku_kn_name ,                          
124                         dest.maker_name,                            
125                         dest.maker_kn_name,                 
126                         dest.parent_maker_code,                     
127                         --dest.parent_maker_name,                   
128                         --dest.parent_maker_kn_name,                
129                         dest.subclass_code  ,               
130                         dest.maker_code,                            
131                         dest.subclass_name  ,               
132                         dest.subclass_kn_name,                      
133                         dest.class_code,                            
134                         dest.class_name,                            
135                         dest.class_kn_name  ,               
136                         dest.department_code,                       
137                         dest.department_name,                       
138                         dest.department_kn_name,                    
139                         dest.sku_division_code,                     
140                         dest.sku_division_name,                     
141                         dest.sku_division_kn_name,                  
142                         --dest.gross_division_cd,                   
143                         --dest.gross_division_name,         
144                         --dest.gross_division_kn_name,              
145                         dest.merchandising_section_code,            
146                         dest.merchandising_section_name,            
147                         --dest.merchandising_section_kn_name,       
148                         dest.jan_code,                              
149                         dest.jan_class,                             
150                         dest.pos_sku_name   ,                       
151                         --dest.item_category_flag,                  
152                         dest.license_classification,                
153                         dest.planned_sale_class,                    
154                         dest.latest_flag,                           
155                         dest.end_date       )
156              VALUES (sku_name,      
157                         sku_kn_name.
158                         maker_name,
159                         maker_kn_name ,
160                         parent_maker_code,
161                         null,
162                         null,
163                         subclass_cd,
164                         maker_cd,   
165                         subclass_nm,
166                         subclass_kn_name,
167                         class_cd ,
168                         class_nm,
169                         class_nm_kn,        
170                         department_code ,
171                         department_name  ,
172                         department_kn_name,
173                         sku_division_code ,
174                         sku_division_name,  
175                         sku_division_kn_name ,
176                         null,
177                         null,
178                         null,
179                         merchandising_section_code,
180                         merchandising_section_name ,
181                         null,
182                         jan_code ,
183                         jan_class,
184                         pos_sku_name,
185                         null,
186                         license_classification      ,
187                         planned_sale_class ,        
188                         latest_flag,
189                         l_date);
190                              COMMIT;
191     EXCEPTION                                               
192          WHEN POPU_ITEM_DIM_ERR THEN
193            dbms_output.put_line(sqlcode||sqlerrm);
194      ------ Call the procedure for drop partition of 14 months back as per Retention policy.
195      WHEN OTHERS THEN
196      dbms_output.put_line(sqlcode||sqlerrm);
197      -- Do appropriate as per logging logic.
198* END PR_POPU_ITEM;
199  /
Re: PL/SQL: ORA-00913 [message #238008 is a reply to message #238004] Wed, 16 May 2007 06:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can do this by yourself.
Google for that errormessage, it is one of the most self-explaining messages around!!
Re: PL/SQL: ORA-00913 [message #238012 is a reply to message #238004] Wed, 16 May 2007 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Count the values!

Regards
Michel
PL/SQL: ORA-00947: not enough values [message #238013 is a reply to message #237896] Wed, 16 May 2007 06:16 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
i have fixed that error.but now iam getting the following error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8 PL/SQL: SQL Statement ignored
156/13 PL/SQL: ORA-00947: not enough values

code is attached:
  1   Create or replace PROCEDURE pr_popu_item
  2   AS
  3      --- Declare constant Variables
  4     c_Prgram_name CONSTANT VARCHAR2(255) := 'pr_popu_item';
  5     POPU_ITEM_DIM_ERR EXCEPTION;
  6             PRAGMA EXCEPTION_INIT(POPU_ITEM_DIM_ERR, -24381);
  7  begin
  8     ------ Call the procedure for drop/split partition
  9         MERGE INTO DIM_ITM dest
 10     USING (SELECT
 11      a.sku_cd                        as item_key                    --item key
 12     ,a.rep_sku_cd                    as rep_sku_cd                  -- rep sku code
 13     ,a.sku_nm                        as sku_name                    --sku name
 14     ,a.sku_nm_kn                     as sku_kn_name                 -- sku kn name
 15     ,b.mkr_cd                        as maker_cd                    --maker code
 16     ,b.mkr_nm                        as maker_name                  --maker name
 17     ,b.mkr_nm_kn                     as maker_kn_name               --maker kn name
 18     ,b.mkr_type                      as add_maker_type              --add   maker type
 19     ,b.year_crct_parent_mkr_cd       as parent_maker_code           --parent maker code
 20     ,a.subclass_cd                   as subclass_cd                 -- subclass code
 21     ,c.subclass_nm                   as subclass_nm                 --subclass name
 22     ,c.subclass_nm_kn                as subclass_kn_name            --subclass kn name
 23     ,a.class_cd                      as class_cd                    --class code
 24     ,d.class_nm                      as class_nm                    --class name
 25     ,d.class_nm_kn                   as class_nm_kn                 --class kn name
 26     ,a.dept_cd                       as department_code             --department code
 27     ,e.dept_nm                       as department_name             --department name
 28     ,e.dept_nm_kn                    as department_kn_name          --department kn name
 29     ,e.sku_div_cd                    as sku_division_code           --sku division code
 30     ,f.sku_div_nm                    as sku_division_name           --sku division name
 31     ,f.sku_div_nm_kn                 as sku_division_kn_name        --sku division kn name
 32     ,a.sku_cls                       as sku_class                   --sku class
 33     ,i.parent_tree_cd                as merchandising_section_code  --merchandising section code
 34     ,j.post_nm                       as merchandising_section_name  --merchandising section name
 35     ,a.jan_cd                        as jan_code                    --jan code
 36     ,a.jan_cls                       as jan_class                   --jan class
 37     ,a.nbpb_cls                      as pos_sku_name                --pos sku name
 38     ,a.lcns_cls                      as license_classification      --license classification
 39     ,a.bsns_cond_flg_1               as planned_sale_class          --planned sale class
 40     ,z.item_nm                       as planned_sale_class_name     --planned sale class name
 41     ,a.latest_flg                    as latest_flag                 --latest flag
 42     ,a.vald_st_date                  as valid_start_date            --valid start date
 43     ,a.vald_ed_date                  as valid_end_date              --valid end date
 44     from    
 45              ODS.wphdm_od_itm a
 46             ,ODS.wphdm_oe_maker b
 47             ,ODS.wphdm_od_subclass c
 48             ,ODS.wphdm_od_dept_class d
 49             ,ODS.wphdm_od_dept e
 50             ,ODS.wphdm_od_itemdiv f
 51             ,ODS.wphdm_oa_date_control h
 52             ,ODS.wphdm_oa_name z -- to get planned sale class
 53             ,ODS.wphdm_oa_gnr_tree_ent  i  -- to get merchandising code
 54             ,ODS.wphdm_ob_department_basic j -- to get merchandising name
 55     where
 56        (a.year_crct_mkr_cd = b.mkr_cd or a.newspaper_publisher_cd = b.mkr_cd)
 57       and a.dept_cd     = c.dept_cd
 58       and a.class_cd    = c.class_cd
 59       and a.subclass_cd = c.subclass_cd
 60       and c.dept_cd  = d.dept_cd
 61       and c.class_cd = d.class_cd
 62       and d.dept_cd  = e.dept_cd
 63       and e.sku_div_cd = f.sku_div_cd
 64       and a.deny_flg = 0
 65       and b.deny_flg = 0
 66       and c.deny_flg = 0
 67       and d.deny_flg = 0
 68       and e.deny_flg = 0
 69       and f.deny_flg = 0
 70       and (h.today -1) between a.vald_st_date and a.vald_ed_date
 71       and (h.today -1) between b.vald_st_date and b.vald_ed_date
 72       and (h.today -1) between c.vald_st_date and c.vald_ed_date
 73       and (h.today -1) between d.vald_st_date and d.vald_ed_date
 74       and (h.today -1) between e.vald_st_date and e.vald_ed_date
 75       and (h.today -1) between f.vald_st_date and f.vald_ed_date
 76       and h.company_cd = '000081'
 77       and h.system_cls = 'dw'
 78       and z.use_cls ='108'
 79       and z.nm_cd = a.bsns_cond_flg_1
 80       and z.deny_flg = 0
 81       and (h.today -1) between z.vald_st_date and z.vald_ed_date
 82       and a.dept_cd=i.child_tree_cd
 83       and i.parent_tree_cd=j.post_cd ) source
 84          ON (dest.rep_sku_code = source.rep_sku_cd
 85              and dest.valid_start_date = source.valid_start_date)
 86         WHEN MATCHED THEN
 87             UPDATE SET dest.sku_name                                = source.sku_name,
 88                        dest.sku_kn_name                             = source.sku_kn_name,
 89                        dest.maker_name                              = source.maker_name,
 90                        dest.maker_kn_name                   = source.maker_kn_name ,
 91                        dest.parent_maker_code                       = source.parent_maker_code,
 92                        --dest.parent_maker_name                     = source.
 93                        --dest.parent_maker_kn_name          = source.
 94                        dest.subclass_code                   = source.subclass_cd,
 95                        dest.maker_code                              = source.maker_cd,      
 96                        dest.subclass_name                   = source.subclass_nm,
 97                        dest.subclass_kn_name                        = source.subclass_kn_name,
 98                        dest.class_code                              = source.class_cd ,
 99                        dest.class_name                              = source.class_nm,
100                        dest.class_kn_name                   = source.class_nm_kn,   
101                        dest.department_code                 = source.department_code ,
102                        dest.department_name                 = source.department_name  ,
103                        dest.department_kn_name                      = source.department_kn_name,
104                        dest.sku_division_code                       = source.sku_division_code ,
105                        dest.sku_division_name                       = source.sku_division_name,     
106                        dest.sku_division_kn_name                    = source.sku_division_kn_name ,
107                        --dest.gross_division_cd                     = source.
108                        --dest.gross_division_name           = source.
109                        --dest.gross_division_kn_name                = source.
110                        dest.merchandising_section_code              = source.merchandising_section_code,
111                        dest.merchandising_section_name              = source.merchandising_section_name ,
112                        --dest.merchandising_section_kn_name = source.
113                        dest.jan_code                                = source.jan_code ,
114                        dest.jan_class                               = source.jan_class,
115                        dest.pos_sku_name                            = source.pos_sku_name,
116                        --dest.item_category_flag                    = source.
117                        dest.license_classification          = source.license_classification ,
118                        dest.planned_sale_class                      = source.planned_sale_class ,   
119                        dest.latest_flag                             = source.latest_flag,
120                        dest.end_date                                = l_date
121         WHEN NOT MATCHED THEN                                       
122              INSERT (dest.sku_name,                                         
123                         dest.sku_kn_name ,                          
124                         dest.maker_name,                            
125                         dest.maker_kn_name,                 
126                         dest.parent_maker_code,                     
127                         dest.parent_maker_name,                     
128                         dest.parent_maker_kn_name,          
129                         dest.subclass_code  ,               
130                         dest.maker_code,                            
131                         dest.subclass_name  ,               
132                         dest.subclass_kn_name,                      
133                         dest.class_code,                            
134                         dest.class_name,                            
135                         dest.class_kn_name  ,               
136                         dest.department_code,                       
137                         dest.department_name,                       
138                         dest.department_kn_name,                    
139                         dest.sku_division_code,                     
140                         dest.sku_division_name,                     
141                         dest.sku_division_kn_name,                  
142                         dest.gross_division_cd,                     
143                         dest.gross_division_name,           
144                         dest.gross_division_kn_name,                
145                         dest.merchandising_section_code,            
146                         dest.merchandising_section_name,            
147                         dest.merchandising_section_kn_name, 
148                         dest.jan_code,                              
149                         dest.jan_class,                             
150                         dest.pos_sku_name   ,                       
151                         dest.item_category_flag,                    
152                         dest.license_classification,                
153                         dest.planned_sale_class,                    
154                         dest.latest_flag,                           
155                         dest.end_date       )
156              VALUES (sku_name,      
157                         sku_kn_name.
158                         maker_name,
159                         maker_kn_name ,
160                         parent_maker_code,
161                         null,
162                         null,
163                         subclass_cd,
164                         maker_cd,   
165                         subclass_nm,
166                         subclass_kn_name,
167                         class_cd ,
168                         class_nm,
169                         class_nm_kn,        
170                         department_code ,
171                         department_name  ,
172                         department_kn_name,
173                         sku_division_code ,
174                         sku_division_name,  
175                         sku_division_kn_name ,
176                         null,
177                         null,
178                         null,
179                         merchandising_section_code,
180                         merchandising_section_name ,
181                         null,
182                         jan_code ,
183                         jan_class,
184                         pos_sku_name,
185                         null,
186                         license_classification      ,
187                         planned_sale_class ,        
188                         latest_flag,
189                         l_date);
190                              COMMIT;
191     EXCEPTION                                               
192          WHEN POPU_ITEM_DIM_ERR THEN
193            dbms_output.put_line(sqlcode||sqlerrm);
194      ------ Call the procedure for drop partition of 14 months back as per Retention policy.
195      WHEN OTHERS THEN
196      dbms_output.put_line(sqlcode||sqlerrm);
197      -- Do appropriate as per logging logic.
198* END PR_POPU_ITEM;
199  /
Re: PL/SQL: ORA-00947: not enough values [message #238015 is a reply to message #238013] Wed, 16 May 2007 06:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And what does that error mean?

Come on, it is more trouble to post your stuff here than to find the cause of these errors.
Re: PL/SQL: ORA-00947: not enough values [message #238016 is a reply to message #238015] Wed, 16 May 2007 06:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And get rid of your exception handler. It only obfuscates what happens.
Surely, you will not just call this procedure from sqlplus in production, will you?
Re: PL/SQL: ORA-00913 [message #238019 is a reply to message #238012] Wed, 16 May 2007 06:23 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
frank, i know it is very silly.But iam a novice in this area.Number of columns in insert statement is 34 and the number of values that iam giving is 34.There is no mis match in the values but still iam getting not enough values.
can u find any reason?
Re: PL/SQL: ORA-00913 [message #238021 is a reply to message #238019] Wed, 16 May 2007 06:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Number of comma's in both?
This has nothing to do with being a novice. The error message very clearly states the error, so then you focus on both column-lists (as you did).
If the number of items look the same, you just focus in and take the time to very carefully read both lists.
It is all about being precise and thorough. Try to keep to a coding standard, so (for example) your comma's always go in the same place. Either no space, or one space or two between column name and a comma, as long as you do it the same for each column.
It adds to readability

[Updated on: Wed, 16 May 2007 06:30]

Report message to a moderator

Re: PL/SQL: ORA-00913 [message #238022 is a reply to message #238019] Wed, 16 May 2007 06:28 Go to previous message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,


Quote:
Maaher wrote @janardhanam.k:
1. you are missing comma's in the insert part.
2. check whether you haven't made a typo (a "." instead of a ",") at the end of some lines.

MHE


may be you are having the second type in your insert statement what Maaher pointed it to you.

regards
shanth.

[edited a little slow Embarassed ]

[Updated on: Wed, 16 May 2007 06:29]

Report message to a moderator

Previous Topic: number to word conversion.
Next Topic: optimising
Goto Forum:
  


Current Time: Fri Dec 02 20:37:06 CST 2016

Total time taken to generate the page: 0.20619 seconds