CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Glitch
Glitch

Reputation: 683

Trigger Issue : ORA-00905: missing keyword

I am trying to insert a record into a another table (tableb) once a certain condition is met after an insert into a certain table (tablea) So i have created a trigger that checks does the above, condition : after inserting into tablea, check whether the sum of price in tablea is greater than a certain value, if it is, then insert the tino into tableb.

Script below will recreate the issue i am currently facing.. Need another paid of eyes on this.

-- create the tables
CREATE TABLE tablea
(
   tino NUMBER not null,
   price VARCHAR2(200),
   dated date
)
partition by range (DATED)
(
 partition PART_201608 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition PART_201609 values less than (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition PART_201610 values less than (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)

--INSERT VALUES
 INSERT INTO tablea (tino,price,dated)VALUES('1234567',10,SYSDATE);
  COMMIT;
  INSERT INTO tablea (tino,price,dated)VALUES('1234560',20,SYSDATE);
  COMMIT;
   -- create table table which once condition is met,, data is written into
 CREATE TABLE tableb(tino number);


-- CREATE THE TRIGGER
CREATE OR REPLACE TRIGGER trg1
AFTER INSERT
ON tablea
FOR EACH ROW

DECLARE
v_price NUMBER;
v_partition VARCHAR2(20) := 'PART_'||TO_CHAR(SYSDATE,'YYYYMM');
v_tino VARCHAR2(20) := :NEW.tino;
sql_smt VARCHAR2(1000) := '';

BEGIN
  sql_smt :='
      SELECT SUM(price) price INTO v_price
      FROM tablea PARTITION('||v_partition||')
       WHERE tino = '||''''||v_tino||''''||'';

 BEGIN
   EXECUTE IMMEDIATE sql_smt;
   EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(SQLERRM);
   DBMS_OUTPUT.PUT_LINE('QUERY='|| sql_smt);
 END;

--DBMS_OUTPUT.PUT_LINE('PRICE  =' || v_price);

 IF v_price >= 15 THEN
   INSERT INTO tableb (tino) VALUES (v_tino);
   COMMIT;
  END IF; 
 END;
  -- test the trigger 
   INSERT INTO tablea(tino,price,dated) VALUES('1234567',10,sysdate);
  COMMIT;

Should return an ORA-00905: missing keyword

 DBMS_OUTPUT.PUT_LINE(SQLERRM);

Returns : 

SELECT SUM(price) price INTO v_price
FROM tablea PARTITION(PART_201609)
WHERE tino = '1234567'

Which should ran with no issues

Any pointers??

Upvotes: 0

Views: 572

Answers (1)

William Robertson
William Robertson

Reputation: 15991

INTO v_price should be part of the execute immediate statement, not part of the dynamic SQL.

btw I think

WHERE tino = '||''''||v_tino||''''||'';

can be simplified to

WHERE tino = '''||v_tino||'''';

Or even better,

WHERE tino = :tino';

with v_tino passed as a bind variable with something like

execute immediate xyz into v_price using v_tino;

Upvotes: 1

Related Questions