script error

i m keep getting this eror can some one plz help me with this script
very urgent

SQL>
SQL> declare
2 cursor cur_insertrow is
3 select
4 b.fyi_lab_report,
5 a.fyi_reference_id,
6 a.fyi_title,
7 a.fyi_first_name,
8 a.fyi_last_name,
9 a.fyi_position,
10 c.sys_author,
11 a.fyi_address1,
12 a.fyi_city,
13 a.fyi_state,
14 a.fyi_postal_code,
15 a.fyi_phone,
16 a.fyi_received_date,
17 b.fyi_approval_date,
18 b.fyi_un_ship_name,
19 b.fyi_un_number,
20 b.fyi_un_haz_class,
21 b.fyi_product_desc,
22 b.fyi_ship_name,
23 b.fyi_haz_class,
24 b.fyi_notes,
25 b.fyi_expiration_date
26 from fyiadm.Fyi_ohma_wf a,fyiadm.fyi_ohma_doc
27 b,fyiadm.fyi_sysdata c;
28
29 where
30 fyi_project_status not like '1032%'
31 and
32
33 (b.sys_fkey = a.sys_fkey);
34
35 begin
36
37 --EXECUTE IMMEDIATE('TRUNCATE TABLE DATASET_EXPLO');
38 --COMMIT;
39
40 for var_insertrow in cur_insertrow
41 loop
42
43 insert into dataset_explo(
44 select
45 lab_report_no,
46 reference_number,
47 contact_title,
48 contact_first_name,
49 contact_last_name,
50 contact_position,
51 cotact_company,
52 contact_address,
53 contact_city,
54 contact_state,
55 contact_zip,
56 contact_phone,
57 received_date,
58 national_stock_number,
59 un_proper_shipping_name,
60 un_serial_number,
61 product_name,
62 shipping_name,
63 hazard_class,
64 un_name_massage,
65 expire_date
66 from dataset_explo;
67 )
68 values(
69 var_insertrow.fyi_lab_report,
70 var_insertrow.fyi_reference_id,
71 var_insertrow.fyi_title,
72 var_insertrow.fyi_first_name,
73 var_insertrow.fyi_last_name,
74 var_insertrow.fyi_position,
75 var_insertrow.sys_author,
76 var_insertrow.fyi_address1,
77 var_insertrow.fyi_city,
78 var_insertrow.fyi_state,
79 var_insertrow.fyi_postal_code,
80 var_insertrow.fyi_phone,
81 var_insertrow.fyi_received_date,
82 var_insertrow.fyi_approval_date,
83 var_insertrow.fyi_un_ship_name,
84 var_insertrow.fyi_un_number,
85 var_insertrow.fyi_un_haz_class,
86 var_insertrow.fyi_product_desc,
87 var_insertrow.fyi_ship_name,
88 var_insertrow.fyi_haz_class,
89 var_insertrow.fyi_notes,
90 var_insertrow.fyi_expiration_date);
91
92
93
94
95
96 If MOD(Cur_insertrow%ROWCOUNT,5000) = 0
97 THEN
98 COMMIT;
99 End if;
100 End Loop;
101 commit;
102 end;
103 /
where
*
ERROR at line 29:
ORA-06550: line 29, column 1:
PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

27 b,fyiadm.fyi_sysdata c;
28
29 where

b.fyi_haz_class,
now i m getting these

*
ERROR at line 22:
ORA-06550: line 65, column 19:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 42, column 11:
PL/SQL: SQL Statement ignored
ORA-06550: line 66, column 16:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier

If the listing you copied is correct then on line 65 use comma separator at the end of expire_date.

Sorry that was not a proper solution dont ue it.

I think you dont need to use semi colone at the end of line 66

for your INSERT INTO part, you don't have to have a select in there to get the column names... it should be

INSERT INTO dataset_explo ( column_name1, colum_name2, etc ) VALUES ( value1, value2 )

I've used SQLPLUS a lot before, and I would do it the way I described. You may be able to do it the way you have written but I think it is clearer to do it the other way, your way seems to confuse the backend.

you also do NOT need a semicolon at the end of line 27 I don' t think ,as you haven't even put in the where clause on the cursor yet.

can you give me any advise now what shoud i do?

1 declare
2 cursor cur_insertrow is
3 select
4 b.fyi_lab_report,
5 a.fyi_reference_id,
6 a.fyi_title,
7 a.fyi_first_name,
8 a.fyi_last_name,
9 a.fyi_position,
10 a.fyi_alt_address,
11 c.sys_author,
12 a.fyi_address1,
13 a.fyi_address2,
14 a.fyi_city,
15 a.fyi_state,
16 a.fyi_postal_code,
17 a.fyi_phone,
18 b.fyi_hydrostatic_flag,
19 b.fyi_acetylene_flag,
20 a.fyi_received_date,
21 b.fyi_approval_date,
22 b.fyi_un_ship_name,
23 b.fyi_un_number,
24 b.fyi_un_haz_class,
25 b.fyi_product_desc,
26 b.fyi_ship_name,
27 b.fyi_haz_class,
28 b.fyi_notes,
29 b.fyi_expiration_date,
30 c.sys_flags
31 from fyiadm.Fyi_ohma_wf a,fyiadm.fyi_ohma_doc
32 b,fyiadm.fyi_sysdata c
33 where
34 fyi_project_status not like '1032%'
35 and
36 (b.sys_fkey = a.sys_fkey);
37 begin
38 --EXECUTE IMMEDIATE('TRUNCATE TABLE DATASET_EXPLO');
39 --COMMIT;
40 for var_insertrow in cur_insertrow
41 loop
42 insert into dataset_explo(
43 lab_report_no,
44 reference_number,
45 contact_title,
46 contact_first_name,
47 contact_last_name,
48 contact_position,
49 cotact_company,
50 contact_address,
51 contact_city,
52 contact_state,
53 contact_zip,
54 contact_phone,
55 received_date,
56 un_revision_date,
57 revision_date,
58 national_stock_number,
59 un_proper_shipping_name,
60 product_name,
61 date_of_entry,
62 user_of_change
63 )
64 values(
65 var_insertrow.fyi_reference_id,
66 var_insertrow.fyi_title,
67 var_insertrow.fyi_first_name,
68 var_insertrow.fyi_last_name,
69 var_insertrow.fyi_position,
70 var_insertrow.fyi_alt_address,
71 var_insertrow.sys_author,
72 var_insertrow.fyi_address1,
73 var_insertrow.fyi_city,
74 var_insertrow.fyi_state,
75 var_insertrow.fyi_postal_code,
76 var_insertrow.fyi_phone,
77 var_insertrow.fyi_hydrostatic_flag,
78 var_insertrow.fyi_acetylene_flag,
79 var_insertrow.fyi_received_date,
80 var_insertrow.fyi_approval_date,
81 var_insertrow.fyi_un_ship_name,
82 var_insertrow.fyi_un_number,
83 var_insertrow.fyi_product_desc,
84 var_insertrow.fyi_ship_name,
85 var_insertrow.fyi_notes,
86 var_insertrow.fyi_expiration_date,
87 );
88 If MOD(Cur_insertrow%ROWCOUNT,5000) = 0
89 THEN
90 COMMIT;
91 End if;
92 End Loop;
93 commit;
94* end;
SQL> /
contact_phone,
*
ERROR at line 54:
ORA-06550: line 87, column 12:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 42, column 10:
PL/SQL: SQL Statement ignored

ok bud, 6550 means that you don't have the same parameters as you need between two expressions. First ,you should definitely get yourself a copy of what all the oracle errors mean to make your own life easier, and secondly, I counted 20 items of the relation you are inserting into, but 22 items are being inserted.... that is most likely what your problem is.

1 declare
2 cursor cur_insertrow is
3 select
4 b.fyi_lab_report,
5 a.fyi_reference_id,
6 a.fyi_title,
7 a.fyi_first_name,
8 a.fyi_last_name,
9 a.fyi_position,
10 a.fyi_alt_address,
11 c.sys_author,
12 a.fyi_address1,
13 a.fyi_address2,
14 a.fyi_city,
15 a.fyi_state,
16 a.fyi_postal_code,
17 a.fyi_phone,
18 b.fyi_hydrostatic_flag,
19 b.fyi_acetylene_flag,
20 a.fyi_received_date,
21 b.fyi_approval_date,
22 b.fyi_un_ship_name,
23 b.fyi_un_number,
24 b.fyi_un_haz_class,
25 b.fyi_product_desc,
26 b.fyi_ship_name,
27 b.fyi_haz_class,
28 b.fyi_notes,
29 b.fyi_expiration_date,
30 c.sys_flags
31 from fyiadm.Fyi_ohma_wf a,fyiadm.fyi_ohma_doc
32 b,fyiadm.fyi_sysdata c
33 where
34 fyi_project_status not like '1032%'
35 and
36 (b.sys_fkey = a.sys_fkey);
37 begin
38 --EXECUTE IMMEDIATE('TRUNCATE TABLE DATASET_EXPLO');
39 --COMMIT;
40 for var_insertrow in cur_insertrow
41 loop
42 insert into dataset_explo(
43 lab_report_no, //1
44 reference_number, //2
45 contact_title, //3
46 contact_first_name, //4
47 contact_last_name, //5
48 contact_position, //6
49 cotact_company, //7
50 contact_address, //8
51 contact_city, //9
52 contact_state, //10
53 contact_zip, //11
54 contact_phone, //12
55 received_date, //13
56 un_revision_date, //14
57 revision_date, //15
58 national_stock_number, //16
59 un_proper_shipping_name, //17
60 product_name, //18
61 date_of_entry, //19
62 user_of_change //20
63 )
64 values(
65 var_insertrow.fyi_reference_id, //1
66 var_insertrow.fyi_title, //2
67 var_insertrow.fyi_first_name, //3
68 var_insertrow.fyi_last_name, //4
69 var_insertrow.fyi_position, //5
70 var_insertrow.fyi_alt_address, //6
71 var_insertrow.sys_author, //7
72 var_insertrow.fyi_address1, //8
73 var_insertrow.fyi_city, //9
74 var_insertrow.fyi_state, //10
75 var_insertrow.fyi_postal_code, //11
76 var_insertrow.fyi_phone, //12
77 var_insertrow.fyi_hydrostatic_flag, //13
78 var_insertrow.fyi_acetylene_flag, //14
79 var_insertrow.fyi_received_date, //15
80 var_insertrow.fyi_approval_date, //16
81 var_insertrow.fyi_un_ship_name, //17
82 var_insertrow.fyi_un_number, //18
83 var_insertrow.fyi_product_desc, //19
84 var_insertrow.fyi_ship_name, //20
85 var_insertrow.fyi_notes, //21
86 var_insertrow.fyi_expiration_date, //22
87 );
88 If MOD(Cur_insertrow%ROWCOUNT,5000) = 0
89 THEN
90 COMMIT;
91 End if;
92 End Loop;
93 commit;
94* end;
SQL> /

you are selecting only 20 column from table but want to insert 22 columns (see those i commented out) , by the way what the comma separator is doing at the end of line 86 as you dont need it at the end of statement.
You were making the same mistake in your first script also.