Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL - Help - Invalid Identifier (Oracle RDBMS 10.2.0.1)
| PL/SQL - Help - Invalid Identifier [message #529262] |
Sun, 30 October 2011 10:33  |
 |
carolmarol123
Messages: 11
Registered: October 2011
|
Junior Member |
|
|
Here's the deal. I am trying to do an update with an anonymous PL/SQL block because it has been absolutely impossible to get by the infamous "Single-row subquery returns multiple rows" Whatever.
So this code below work... Obviously, I am updating it to a constant.. OK, duh.. Of course it works, it's easy.... Now, I really need to update it to a value in another table that is in my cursor. I believe that I probably need to declare a secondary cursor.
I'm just getting back in the DBA saddle, so I'm a little rusty.. just looking for some help here...
set serveroutput on;
DECLARE
numrows NUMBER := 0;
total NUMBER := 0;
CURSOR upd_record_cur IS SELECT m.rowid, m.swcm_cycle, t.newcycle, p.aprem_no from toad.fswcmas m, toad.ttp43425_load t, toad.fciaprem p
where (p.aprem_no = m.swcm_premise_no)
and (t.acctnum + 100 = m.swcm_cust)
and (t.curroute = p.aprem_route)
and (t.curcycle = p.aprem_cycle)
and p.aprem_application=4;
upd_rec upd_record_cur%rowtype;
BEGIN
FOR upd_rec IN upd_record_cur LOOP
update toad.fswcmas sw set sw.swcm_cycle = 8888
WHERE rowid = upd_rec.rowid;
total := total + 1;
numrows := numrows + 1;
IF (numrows >= 1000) THEN
COMMIT;
numrows := 0;
dbms_output.put_line('numrows is' || numrows);
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Updated ' || total || ' records from TOAD.FSWCMAS.');
END;
/
Not so good here
ORA-06550: line 20, column 53:
PL/SQL: ORA-00904: "TOAD"."TTP43425_LOAD"."NEWCYCLE": invalid identifier
ORA-06550: line 20, column 10:
PL/SQL: SQL Statement ignored
Doesn't work...
BEGIN
FOR upd_rec IN upd_record_cur LOOP
update toad.fswcmas sw set sw.swcm_cycle = toad.ttp43425_load.newcycle
WHERE rowid = upd_rec.rowid;
total := total + 1;
|
|
|
|
|
|
| Re: PL/SQL - Help - Invalid Identifier [message #529265 is a reply to message #529262] |
Sun, 30 October 2011 11:08  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
carolmarol123 wrote on Sun, 30 October 2011 16:33Here's the deal. I am trying to do an update with an anonymous PL/SQL block because it has been absolutely impossible to get by the infamous "Single-row subquery returns multiple rows" Whatever.
Facing this error means that you have design problem - one row is updated with multiple values - so the same update on same data may lead to different correct results. Maybe it would be better to take a step back, determine the relationship of all involved tables and define rules for obtaining the only one value for update of FSWCMAS.SWCM_CYCLE column.
To your code - without any background it is quite impossible to deduce what and how are you trying to achieve. However the error is self-explanatory, is not it?
|
|
|
|
|
|
| Re: PL/SQL - Help - Invalid Identifier [message #529267 is a reply to message #529266] |
Sun, 30 October 2011 11:48 |
|
carolmarol123
Messages: 11
Registered: October 2011
|
Junior Member |
|
|
So here's the goal. I need to update a "Master" table with values received from a spreadsheet. So, I loaded the table into TTP43425_LOAD...It has columns for a "Customer #", a "premise Number", a "current cycle", a "new cycle", a "current route" and a "new route".
The master tables have to be joined in this fashion- FCIAPREM - premise information for utilities, where the current route is equal to the current route in the spreadsheet, - to the FxxCMAS (the water or sewer customer master record).. then, the tables that need to be updated are .. the FxxCMAS records, a table called FCIMONTH (I have no idea what that is), the FCICMTR records (which are meter records for the utilities-- water, sewer, drainage, and refuse).
This is a new job for me, so the data model is still a little sketchy to me-- which is of course, VERY, VERY SCARY! And the model at this company is to do "data hits" outside of applications... again, scary....not good, OK? Got it.
So I am going to attach something, that will blow your mind. I have put a LOT of work into this. And if I could just get ONE update statement that would work, I would just.. be floored!
Also, I have been able to update the records with single UPDATE statements, but this takes too long, and it's inefficient and I don't want to hang out for 8 hours doing this in production!
[Updated on: Sun, 30 October 2011 11:52] Report message to a moderator |
|
|
|
|
|
|
|
| Re: PL/SQL - Help - Invalid Identifier [message #529270 is a reply to message #529267] |
Sun, 30 October 2011 12:14 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
carolmarol123 wrote on Sun, 30 October 2011 17:48This is a new job for me, so the data model is still a little sketchy to me-- which is of course, VERY, VERY SCARY! And the model at this company is to do "data hits" outside of applications... again, scary....not good, OK? Got it.
No, I have no idea what you expect from this forum at all, especially as you are contradicting yourself about failure/success of single statement. There are many methods of analyzing the execution and tuning SQL statement, which are described e.g. in Performance Guide sticky: http://www.orafaq.com/forum/t/84315/96705/ Doing anything in loop will probably not be better.
The only error message you posted in your initial post is clear enough; I would believe it rather than the description in your attachment.
|
|
|
|
|
|
|
|
|
|
| Re: PL/SQL - Help - Invalid Identifier [message #529274 is a reply to message #529271] |
Sun, 30 October 2011 12:44 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Yes, it should be possible. I would maybe use FOR UPDATE OF clause in cursor and update with WHERE CURRENT OF condition, but it should not matter.
Just curious: did you try to run the CURSOR LOOP without the UPDATE statement? As the error is in the line with FOR loop iterator, it should fail there as well.
|
|
|
|
|
|
|
|
| Re: PL/SQL - Help - Invalid Identifier [message #529277 is a reply to message #529272] |
Sun, 30 October 2011 12:52 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Yes, you can update one table by selecting from a join of that table to other tables.
You could save yourself and us a lot of time and trouble by providing what has been asked for and using a copy and paste. The code that you provide could not produce the exact error message that you provided, so you are not posting exactly what you are running, which is why we need the copy and paste.
The first error that you provided regarding the invalid identifier generally occurs when a table or column name is misspelled or in the wrong case or there are insufficient privileges to the table and/or column. This is common when all the tables and code that accesses them are not in the same user schema.
The other error that you mentioned regarding too many rows occurs when it requires one row and your query provides more than one row, such as when you attempt to use = in comparing one value to multiple values.
I have tried to piece together what you have provided, demonstrating that the code that you provided works, then showing a simpler update statement. Notice the use of IN instead of = to avoid the too many rows error. This may not be the simplest query, but you need to get it to work without error first, then focus on tuning. Please notice the copy and paste method that I have used and try to provide that in the future.
-- examples of create table statements and insert statements for sample data that you should have provided:
TOAD@orcl_11gR2> CREATE TABLE ttp43425_load (
2 ACCTNUM NUMBER(12),
3 METERNUM VARCHAR2(12),
4 ERTNUM VARCHAR2(12),
5 PREMNUM NUMBER(12),
6 PREMADDR VARCHAR2(35),
7 CURCYCLE NUMBER(4),
8 CURROUTE NUMBER(4),
9 NEWCYCLE NUMBER(4),
10 NEWROUTE NUMBER(4)
11 )
12 /
Table created.
TOAD@orcl_11gR2> INSERT INTO ttp43425_load
2 VALUES (1, 'A', 'B', 1, 'C', 1, 1, 1, 1)
3 /
1 row created.
TOAD@orcl_11gR2> CREATE TABLE fciaprem (
2 ACC_RN NUMBER(10) NOT NULL,
3 APREM_COMP NUMBER(2) NOT NULL,
4 APREM_NO NUMBER(11) NOT NULL,
5 APREM_APPLICATION NUMBER(2) NOT NULL,
6 APREM_CYCLE NUMBER(4),
7 APREM_ROUTE NUMBER(4)
8 )
9 /
Table created.
TOAD@orcl_11gR2> INSERT INTO fciaprem
2 VALUES (1, 1, 1, 4, 1, 1)
3 /
1 row created.
TOAD@orcl_11gR2> CREATE TABLE fswcmas
2 (swcm_cycle NUMBER,
3 swcm_premise_no NUMBER,
4 swcm_cust NUMBER)
5 /
Table created.
TOAD@orcl_11gR2> INSERT INTO fswcmas
2 VALUES (1, 1, 101)
3 /
1 row created.
TOAD@orcl_11gR2> COMMIT
2 /
Commit complete.
TOAD@orcl_11gR2> SELECT * FROM fswcmas
2 /
SWCM_CYCLE SWCM_PREMISE_NO SWCM_CUST
---------- --------------- ----------
1 1 101
1 row selected.
-- code that you provided (with commits commented out):
TOAD@orcl_11gR2> DECLARE
2 numrows NUMBER := 0;
3 total NUMBER := 0;
4 CURSOR upd_record_cur IS
5 SELECT m.rowid, m.swcm_cycle, t.newcycle, p.aprem_no
6 from toad.fswcmas m, toad.ttp43425_load t, toad.fciaprem p
7 where (p.aprem_no = m.swcm_premise_no)
8 and (t.acctnum + 100 = m.swcm_cust)
9 and (t.curroute = p.aprem_route)
10 and (t.curcycle = p.aprem_cycle)
11 and p.aprem_application=4;
12 upd_rec upd_record_cur%rowtype;
13 BEGIN
14 FOR upd_rec IN upd_record_cur LOOP
15 update toad.fswcmas sw
16 set sw.swcm_cycle = 8888
17 WHERE rowid = upd_rec.rowid;
18 total := total + 1;
19 numrows := numrows + 1;
20 IF (numrows >= 1000) THEN
21 -- COMMIT;
22 numrows := 0;
23 dbms_output.put_line ('numrows is' || numrows);
24 END IF;
25 END LOOP;
26 -- COMMIT;
27 DBMS_OUTPUT.PUT_LINE ('Updated ' || total || ' records from toad.FSWCMAS.');
28 END;
29 /
Updated 1 records from toad.FSWCMAS.
PL/SQL procedure successfully completed.
TOAD@orcl_11gR2> SELECT * FROM fswcmas
2 /
SWCM_CYCLE SWCM_PREMISE_NO SWCM_CUST
---------- --------------- ----------
8888 1 101
1 row selected.
-- rollback data prior to next run:
TOAD@orcl_11gR2> ROLLBACK
2 /
Rollback complete.
TOAD@orcl_11gR2> SELECT * FROM fswcmas
2 /
SWCM_CYCLE SWCM_PREMISE_NO SWCM_CUST
---------- --------------- ----------
1 1 101
1 row selected.
-- simpler method:
TOAD@orcl_11gR2> UPDATE toad.fswcmas sw
2 SET sw.swcm_cycle = 8888
3 WHERE sw.rowid IN
4 (SELECT m.rowid
5 from toad.fswcmas m, toad.ttp43425_load t, toad.fciaprem p
6 where (p.aprem_no = m.swcm_premise_no)
7 and (t.acctnum + 100 = m.swcm_cust)
8 and (t.curroute = p.aprem_route)
9 and (t.curcycle = p.aprem_cycle)
10 and p.aprem_application=4)
11 /
1 row updated.
TOAD@orcl_11gR2> SELECT * FROM fswcmas
2 /
SWCM_CYCLE SWCM_PREMISE_NO SWCM_CUST
---------- --------------- ----------
8888 1 101
1 row selected.
TOAD@orcl_11gR2>
|
|
|
|
| Re: PL/SQL - Help - Invalid Identifier [message #529278 is a reply to message #529273] |
Sun, 30 October 2011 12:57 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
carolmarol123 wrote on Sun, 30 October 2011 18:44For customer number xxxxx (which is unique)
update new cycle to yyyy (which is not, and is in the temp table).
What does this mean? If there are multiple values of new cycle in the temp table for one customer number, it is a problem.
Just example: if there are cycles 5, 10, 12 for a customer 'xxxxx' in the temp table, which one should be assigned to it in updated table? Least? Greatest? Average? Anything else? Why???
|
|
|
|
|
|
| Re: PL/SQL - Help - Invalid Identifier [message #529306 is a reply to message #529279] |
Mon, 31 October 2011 01:12 |
 |
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
If I understood what you said, it was that you managed to do "something" using a single UPDATE statement but it lasts too long. Therefore, you switched to a PL/SQL solution, hoping that it will be faster. Generally speaking, it will not. If possible, stay with SQL, a single UPDATE statement. If it is slow, it might require tuning.
Anyway, Barbara demonstrated how to do that.
Also, this (taken from your first message):IF (numrows >= 1000) THEN
COMMIT; |
|
|
|
Goto Forum:
Current Time: Mon Aug 11 19:45:46 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
