CODEWITHSUNDEEP
oracle-databaseoracle10goracle11goracle9iora-00933
Murali Krishna Pinjala
Murali Krishna Pinjala

Reputation: 403

Oracle update procedure issue

I want to update the following procedure in the Oracle table, but it is throwing the error:

CREATE OR REPLACE PROCEDURE update_keywords (aKEYWORD IN VARCHAR2, aCOUNT IN NUMBER)
AS BEGIN
   update searchable_keywords  
   set KEYWORD =:new.aKEYWORD or COUNT =:new.aCOUNT 
   where KEUWORD_ID = : old.KEYWORD_ID;
  END;

This is my procedure. I want to update the keyword & count in the searchable_keywords table with keyword_id (primary key) but it is throwing an error as follows:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4      PL/SQL: SQL Statement ignored
4/17     PLS-00049: bad bind variable 'NEW.AKEYWORD'
4/31     PL/SQL: ORA-00933: SQL command not properly ended
4/41     PLS-00049: bad bind variable 'NEW.ACOUNT'

Can you please help me solve this problem?

Upvotes: 0

Views: 624

Answers (1)

Matthew Watson
Matthew Watson

Reputation: 14253

This looks like a trigger... You don't need the ":new" for acount/akeyword, and you need to pass in the ID you want update. Eg.

CREATE OR REPLACE
PROCEDURE UPDATE_KEYWORDS(
    AKEYWORD    IN VARCHAR2,
    ACOUNT      IN NUMBER,
    AKEYWORD_ID IN NUMBER)
AS
BEGIN
  UPDATE
    SEARCHABLE_KEYWORDS
  SET
    KEYWORD =  AKEYWORD
  , "COUNT"  = ACOUNT
  WHERE
    KEYWORD_ID = AKEYWORD_ID
END;

I'm not sure why you would have a column named "COUNT".. unless you are trying to do something different in that update, its obviously wrong with that "or" in there..

Upvotes: 5

Related Questions