Have you ever received the ORA-00947 from one of your XU2/XU5
scripts? This occurs in SQL statements requiring two
equal sets of values (very common to occur in an insert statement). Here is an example from a part of a XU2 script:
SQL>
INSERT INTO n_view_column_templates
2
(view_label
3 ,
query_position
4 ,
column_label
5 ,
table_alias
6 ,
column_expression
7 ,
column_position
8 ,
column_type
9 ,
description
10 ,
group_by_flag
11 ,
gen_search_by_col_flag
12 ,
profile_option
13 ,
product_version
14 , created_by
15 ,
creation_date
16 ,
last_updated_by
17 ,
last_update_date
18 )
19
VALUES
20 ('OE_ACME_Blanket_Order_Pricing' --view_label
21
,1 --query_position number
22
,'Line_Revision_Date' --column_label
23
,'QPLNS' --table_alias
24
,'LINE_REVISION_DATE'
--COLUMN_EXPRESSION
25
,76 --column_position number
26
,'COL' --column_type
27
,'The line revision date ' --description
28 ,
NULL --profile_option
29
,'*' --product_version
30 ,’flintstonef’ -- created_by
31 ,
SYSDATE --creation_date
32 ,’flintstonef’ --last_updated_by
33 ,
SYSDATE --last_update_date
34 );
VALUES
*
ERROR at
line 19:
ORA-00947:
not enough values
With a
cursory look, you might not catch the discrepancy, but by juxtaposing the
insert columns with the value columns, we see that the insert set has 16 columns
whereas the value set has 14 columns:
SQL>
INSERT INTO n_view_column_templates
|
19
VALUES
|
2
(view_label
|
20
('OE_ACME_Blanket_Order_Pricing'
--view_label
|
3 ,
query_position
|
21
,1 --query_position number
|
4 ,
column_label
|
22
,'Line_Revision_Date' --column_label
|
5 ,
table_alias
|
23
,'QPLNS' --table_alias
|
6 ,
column_expression
|
24
,'LINE_REVISION_DATE'
--COLUMN_EXPRESSION
|
7 ,
column_position
|
25
,76 --column_position number
|
8 ,
column_type
|
26
,'COL' --column_type
|
9 ,
description
|
27
,'The line revision date ' --description
|
10 ,
group_by_flag
|
28 ,
NULL --profile_option
|
11 ,
gen_search_by_col_flag
|
29
,'*' --product_version
|
12 ,
profile_option
|
30
,’flintstonef’ -- created_by
|
13 ,
product_version
|
31 ,
SYSDATE --creation_date
|
14 ,
created_by
|
32
,’flintstonef’
--last_updated_by
|
15 ,
creation_date
|
33 ,
SYSDATE
--last_update_date
|
16 ,
last_updated_by
|
34 );
|
17 ,
last_update_date
|
|
18 )
|
This juxtaposition allows us to see that the group_by_flag and the gen_search_by_col_flag columns have been omitted from the values set.
In this context the solution is easy, we just add these columns to the value set as follows:
In this context the solution is easy, we just add these columns to the value set as follows:
INSERT INTO n_view_column_templates
2
(view_label
3 ,
query_position
4 ,
column_label
5 ,
table_alias
6 ,
column_expression
7 ,
column_position
8 ,
column_type
9 ,
description
10 , group_by_flag
11 ,
gen_search_by_col_flag
12 ,
profile_option
13 ,
product_version
14 ,
created_by
15 ,
creation_date
16 ,
last_updated_by
17 ,
last_update_date
18 )
19
VALUES
20
('OE_ACME_Blanket_Order_Pricing'
--view_label
21
,1 --query_position number
22
,'Line_Revision_Date' --column_label
23
,'QPLNS' --table_alias
24
,'LINE_REVISION_DATE'
--COLUMN_EXPRESSION
25
,76 --column_position number
26
,'COL' --column_type
27
,'The line revision date ' –description
28 ,'N'
-- group_by_flag
29 ,'N'
-- gen_search_by_col_flag
30 ,
NULL --profile_option
31
,'*' --product_version
32
,’flintstonef’ -- created_by
33 ,
SYSDATE --creation_date
34
,’flintstonef’ --last_updated_by
35 ,
SYSDATE --last_update_date
36 );
While this
is not a common error, this has occurred in my development process. I think the root cause is accidently deleting
rows.
No comments:
Post a Comment