CODEWITHSUNDEEP
plsqlprocedure
Steffen
Steffen

Reputation: 3

plsql procedure statement ignored

I have written this procedure and when I try to run it, I get an error "ORA-00933". I have added the update statements and after this the error occurs. Before that it worked.

What could be the issue?

DECLARE 
---values---
   v_sidcount sitetb.sid%type;
   v_sid sitetb.sid%type;
   v_cid sitetb.cid%type := 46;
   v_sptid sitetb.sptid%type := 215;

BEGIN 

---count---
SELECT count(sid) into v_sidcount FROM sitetb
WHERE NOT EXISTS (SELECT sid FROM kam_report_ttn
  WHERE sid = sitetb.sid
) and cid = v_cid and sptid = v_sptid;

---find all data---
SELECT max(sid) into v_sid FROM sitetb
WHERE NOT EXISTS (SELECT sid FROM kam_report_ttn
  WHERE sid = sitetb.sid
) and cid = v_cid and sptid = v_sptid;

---insert into table---
insert into KAM_REPORT_TTN (sid) 
values (v_sid);

---update comments---
UPDATE KAM_REPORT_TTN SET comments = (SELECT addeddate || ', ' || responseperson || ': ' || candidatenote || ' '  FROM candidatetb_extra)
where sid = v_sid order by noteid desc;
UPDATE KAM_REPORT_TTN SET comments2 = (SELECT addeddate || ', ' || responseperson || ': ' || sitenote  || ' '  FROM sitetb_extra)
where sid = v_sid order by noteid desc;

   dbms_output.put_line 
   (v_sid || '/' ||v_sidcount); 
END; 
/

Errors:

ORA-06550: line 29, column 19:
PL/SQL: ORA-00933: SQL command not properly ended

ORA-06550: line 27, column 1:
PL/SQL: SQL Statement ignored

ORA-06550: line 33, column 19:
PL/SQL: ORA-00933: SQL command not properly ended

ORA-06550: line 31, column 1:
PL/SQL: SQL Statement ignored

Error at Line: 7 Column: 0

Upvotes: 0

Views: 38

Answers (1)

reuben42
reuben42

Reputation: 45

This looks like you are trying to apply an order by on an update statement:

order by noteid desc;

I guess you meant to include that in the select subquery so:

UPDATE KAM_REPORT_TTN SET comments = (SELECT addeddate || ', ' || responseperson || ': ' || candidatenote || ' '  FROM candidatetb_extra     where sid = v_si)

I'm not sure if you have the where clause in the right place or not.

Upvotes: 1

Related Questions