Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
You don't need a fax machine to get faxes. Get a fax-to-email fax number from CallWave. Try it free.
  #1  
Old November 4th, 2003, 08:54 AM
mehak mehak is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Posts: 20 mehak User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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

Reply With Quote
  #2  
Old November 4th, 2003, 09:09 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 5th Plane (7000 - 7499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,401 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 2 Weeks 6 h 31 m 39 sec
Reputation Power: 255
27 b,fyiadm.fyi_sysdata c;
28
29 where

Reply With Quote
  #3  
Old November 4th, 2003, 09:13 AM
mehak mehak is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Posts: 20 mehak User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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

Reply With Quote
  #4  
Old November 4th, 2003, 09:36 AM
shafique shafique is offline
Senior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Canada
Posts: 305 shafique User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 5
If the listing you copied is correct then on line 65 use comma separator at the end of expire_date.

Reply With Quote
  #5  
Old November 4th, 2003, 09:44 AM
shafique shafique is offline
Senior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Canada
Posts: 305 shafique User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 5
Sorry that was not a proper solution dont ue it.

Reply With Quote
  #6  
Old November 4th, 2003, 09:47 AM
shafique shafique is offline
Senior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Canada
Posts: 305 shafique User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 5
I think you dont need to use semi colone at the end of line 66

Reply With Quote
  #7  
Old November 4th, 2003, 10:53 AM
metaBarf metaBarf is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 373 metaBarf User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 49 m 6 sec
Reputation Power: 5
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.

Reply With Quote
  #8  
Old November 4th, 2003, 11:02 AM
mehak mehak is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Posts: 20 mehak User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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

Reply With Quote
  #9  
Old November 4th, 2003, 12:11 PM
metaBarf metaBarf is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 373 metaBarf User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 49 m 6 sec
Reputation Power: 5
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.

Reply With Quote
  #10  
Old November 4th, 2003, 12:48 PM
shafique shafique is offline
Senior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Canada
Posts: 305 shafique User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 5
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > script error


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

 Free IT White Papers!
 
Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

 
Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

 
Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

 
Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

 
Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway