Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00927: missing equal sign for update statement
| ORA-00927: missing equal sign for update statement [message #459777] |
Tue, 08 June 2010 05:31  |
sassmine82
Messages: 19
Registered: June 2010
|
Junior Member |
|
|
Hi everybody,
I try to make an update statement.
But I want to read my set statement from a table.
CREATE TABLE OUTPUT(
ID varchar2(10),
ACTION varchar2(5),
TYPE varchar2(5),
REQ_TYPE NUMBER,
REQ_OBJ NUMBER,
IMSI1 varchar2(50)
);
CREATE TABLE CONVERT(
ACTION VARCHAR2(5),
TYPE VARCHAR2(5),
SET_STMT VARCHAR2(4000)
);
insert into OUTPUT values ('x1','CRE_AUC',NULL,NULL,'1234');
insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1='60503' || IMSI1');
commit;
In a procedure, I open a cursor on table OUTPUT, I read the row from CONVERT where OUTPUT.ACTION = CONVERT.ACTION and I update the table OUTPUT with SET_stmt in table CONVERT.
CREATE OR REPLACE PROCEDURE CONVERTING
IS
set_expression varchar2(4000);
CURSOR tasks IS SELECT * FROM OUTPUT;
BEGIN
FOR cur IN tasks
LOOP
SELECT SET_STMT
INTO set_expression
FROM CONVERT
WHERE ACTION = cur.ACTION
AND TYPE = cur.TYPE;
UPDATE OUTPUT SET set_expression
WHERE ID = cur.ID
AND ACTION = cur.ACTION
AND current of tasks;
END LOOP;
END;
I have the error ORA-00927: missing equal sign
What's the wrong thing on my code?
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459784 is a reply to message #459782] |
Tue, 08 June 2010 05:51  |
sassmine82
Messages: 19
Registered: June 2010
|
Junior Member |
|
|
I know how the update statement works but maybe I want just to use something specific.
How can I store the set statement in a table? I don't want to put it in the update statement like that :
CREATE OR REPLACE PROCEDURE CONVERTING
IS
CURSOR tasks IS SELECT * FROM OUTPUT;
BEGIN
FOR cur IN tasks
LOOP
SELECT SET_STMT
INTO set_expression
FROM CONVERT
WHERE ACTION = cur.ACTION
AND TYPE = cur.TYPE;
UPDATE OUTPUT SET REQ_TYPE=1,REQ_OBJ=1,IMSI1='60503' || cur.IMSI1
WHERE ID = cur.ID
AND ACTION = cur.ACTION;;
END LOOP;
END;
I just want to store the set statement in a table and read it from table.
Thanks cookiemonster for your patience 
[Updated on: Tue, 08 June 2010 05:52] Report message to a moderator |
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459785 is a reply to message #459777] |
Tue, 08 June 2010 05:52 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
it would be nice if you provided working testcase. SQL> insert into OUTPUT values ('x1','CRE_AUC',NULL,NULL,'1234');
insert into OUTPUT values ('x1','CRE_AUC',NULL,NULL,'1234')
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1='60503' || IMSI1');
insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1='60503' || IMSI1')
*
ERROR at line 1:
ORA-00917: missing comma
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459789 is a reply to message #459785] |
Tue, 08 June 2010 06:05 |
sassmine82
Messages: 19
Registered: June 2010
|
Junior Member |
|
|
I'm sorry.
This is a working test case :
CREATE TABLE OUTPUT(
ID varchar2(10),
ACTION varchar2(20),
TYPE varchar2(5),
REQ_TYPE NUMBER,
REQ_OBJ NUMBER,
IMSI1 varchar2(50)
);
CREATE TABLE CONVERT(
ACTION VARCHAR2(20),
TYPE VARCHAR2(5),
SET_STMT VARCHAR2(4000)
);
insert into OUTPUT values ('x1','CRE_AUC','NT',NULL,NULL,'1234');
insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1=''60503'' || cur.IMSI1');
CREATE OR REPLACE PROCEDURE CONVERTING_TEST
IS
CURSOR tasks IS SELECT * FROM OUTPUT;
set_expression varchar2(4000);
sql_stmt varchar2(4000);
BEGIN
FOR cur IN tasks
LOOP
SELECT SET_STMT
INTO set_expression
FROM CONVERT
WHERE ACTION = cur.ACTION
AND TYPE = cur.TYPE;
sql_stmt:= 'UPDATE OUTPUT SET set_expression WHERE ID = '''
|| cur.id
|| ''' AND ACTION = '''
|| cur.action
|| '''';
execute immediate sql_stmt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END CONVERTING_TEST;
/
Even with 'EXECUTE IMMEDIATE', it doesn't work. I have the same error ORA-00927: missing equal sign
|
|
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459793 is a reply to message #459777] |
Tue, 08 June 2010 06:10 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
In addition (to the need of dynamic statement), your input statement is not correct:
SQL> insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1='60503' || IMSI1');
insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1='60503' || IMSI1')
*
ERROR at line 1:
ORA-00917: missing comma
Also I recommend to not use this kind of stuff without a complete set of controls on the statement that will be executed by the procedure.
Regards
Michel
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459796 is a reply to message #459777] |
Tue, 08 June 2010 06:15 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Still not working for me: SQL> insert into OUTPUT values ('x1','CRE_AUC','NT',NULL,NULL,'1234');
insert into OUTPUT values ('x1','CRE_AUC','NT',NULL,NULL,'1234')
*
ERROR at line 1:
ORA-12899: value too large for column "FLYBOY"."OUTPUT"."ACTION" (actual: 7,
maximum: 5)
SQL> insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1=''60503'' || cur.IMSI1')
;
insert into convert values ('CRE_AUC','NT','REQ_TYPE=1,REQ_OBJ=1,IMSI1=''60503'' || cur.IMSI1')
*
ERROR at line 1:
ORA-12899: value too large for column "FLYBOY"."CONVERT"."ACTION" (actual:
7, maximum: 5)
By the way, you are interested in SET_STATEMENT content, so you should rather concatenate it - the same way you use cursor values. sql_stmt:= 'UPDATE OUTPUT SET '||set_expression
||' WHERE ID = '''||cur.id
|| ''' AND ACTION = '''||cur.action||'''';
However, I would change the logic by looping CONVERT table (not OUTPUT). By the way, what shall happen when there is no row in CONVERT for some row in OUTPUT? Your current code will fail with NO_DATA_FOUND exception.
[Edit: added the last sentence]
[Updated on: Tue, 08 June 2010 06:20] Report message to a moderator |
|
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459803 is a reply to message #459800] |
Tue, 08 June 2010 06:47 |
sassmine82
Messages: 19
Registered: June 2010
|
Junior Member |
|
|
I corrected the quotes as Michel Cadot said.
and I have still the error ORA-00927: missing equal sign.
flyboy, I don't want to change the logic.
because OUTPUT will contains more than 20000 lines. but CONVERT will contain only 4 lines. It could be efficient with this logic no?
So have you any idea?
Thanks
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459807 is a reply to message #459803] |
Tue, 08 June 2010 06:57 |
cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
sassmine82 wrote on Tue, 08 June 2010 12:47I corrected the quotes as Michel Cadot said.
and I have still the error ORA-00927: missing equal sign.
cookiemonster wrote on Tue, 08 June 2010 12:09
First rule of using dynamic sql:
Build a string containing the dynamic sql statement (which you've done).
Use dbms_ouput or similar to display the contents of the string to see if it's a valid statement.
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459815 is a reply to message #459811] |
Tue, 08 June 2010 07:11 |
cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Dynamic sql can't see variables from the procedure that calls it.
To use variables you need to either:
a) concatenate them into the string:
l_str := 'SET column = '||l_variable||;
l_str := 'SET column = l_variable';
b) use bind variables - but you'll hit the same problem with that.
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459841 is a reply to message #459836] |
Tue, 08 June 2010 08:23 |
cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Basically with a dynamic sql you are trying to generate a sql string that'll run independantly of the procedure you are generating it in.
If you can't copy and paste the generated string into sqlplus and have it run then it won't work.
Obviously if the generated statement uses binds then you'll have to declare them in sqlplus but in this context binds differ from pl/sql variables.
|
|
|
|
| Re: ORA-00927: missing equal sign for update statement [message #459862 is a reply to message #459811] |
Tue, 08 June 2010 09:48 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
sassmine82 wrote on Tue, 08 June 2010 14:05OK.
Now I have the error ORA-00904: "CUR"."IMSI1": invalid identifier
It seems that, when executing statement, "CUR"."IMSI1" is seen as varchar variable.
or maybe the cursor cur is not seen.
No, it is seen as identifier (as the message says). Yes, cursor is not known in the dynamic statement.
But, as you update the same row from cursor, it is possible to get rid of it completely - so you may use 'REQ_TYPE=1,REQ_OBJ=1,IMSI1=''60503''|| IMSI1'
Quote:flyboy, I don't want to change the logic.
because OUTPUT will contains more than 20000 lines. but CONVERT will contain only 4 lines. It could be efficient with this logic no?
Probably no. Issuing 4 UPDATEs (although when treating multiple rows) instead of 20000 dynamic ones should be more efficient. Creating index on (ACTION, TYPE) in OUTPUT could help if there is small number of rows with some its combination. As you cannot use WHERE CURRENT OF <cursor> (the same reason as above), you would have to choose some unique row identification (ROWID would be most efficient, but I was unable to pass it dynamically; maybe you will have better luck). Also it would be very useful to use bind variables not to extensively flood V$SQLAREA.
In the end, my proposal should not be inefficient: declare
sql_stmt varchar2(4000);
begin
for cur in (select * from convert) loop
sql_stmt := 'UPDATE OUTPUT SET '||cur.set_stmt
||' WHERE ACTION = '''||cur.action||''' and TYPE = '''||cur.type||'''';
execute immediate sql_stmt;
end loop;
end;
/
SQL> select * from output;
ID ACTION TYPE REQ_TYPE REQ_OBJ IMSI1
---------- -------------------- ----- ---------- ---------- ---------
x1 CRE_AUC NT 1 1 605031234 |
|
|
|
Goto Forum:
Current Time: Sun Sep 14 04:17:07 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
