'PLS-00103 Encountered the symbol "+" ...' error when trying to concatenate some strings in a trigger

Question

I have a SQL table where a particular record ID (the primary key) could have several Contact IDs. I want to create a trigger where each time a new row is added, it will check whether the record ID exists, and if it does it will add the new Contact ID to the current Contact ID field separated by a comma (to essentially create a list). So for multiple Contact IDs the field could become:

Contact123, Contact456, Contact999

To do this, I've written the following trigger:

CREATE OR REPLACE
TRIGGER "TEST_INSERT" BEFORE
   INSERT ON "TEST_INSERT" REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE REC_COUNT INTEGER;
BEGIN
   SELECT COUNT(*)
   INTO REC_COUNT
   FROM TEST_INSERT
   WHERE RECORDID = :NEW.RECORDID;
   IF REC_COUNT > 0 THEN
      CONTACTID + ', ' + :NEW.CONTACTID AS CONTACTID;   --This is the line where I'm getting the error
   END IF;
END;

I'm getting an error for the + sign as well as the AS operation. Am I going about this completely the wrong way?

Thanks

Oracle strings are concatenated usually using `||` operator instead of `+` — tvm, Jun 18 '15 at 15:42
[The concatenation operator is ||](http://stackoverflow.com/q/278189/266304); you also need to assign your concatenated string to something. What you're doing doesn't really make sense though. If you insert a new contact, you want to change that new value to be an old value *and* the new value? Or you want to change the insert into an update so you only have a single row, breaking normalisation? You almost certainly don't want to do that, and if you do you can't use a trigger. You mentioned a PK; are the contacts in a child table? — Alex Poole, Jun 18 '15 at 16:34
@tvm: that is nothing specific to Oracle. That's the operator defined in the SQL standard. — , Jun 18 '15 at 17:46
Thanks for your responses. I have tried it with both + and || and they both come out as the same PL-00103 error. Alex, I am trying to add the new value of `CONTACTID` to the one on the row that I have matched the `RECORDID` against. I want to end up with a list of Contact IDs rather than having a separate row for each. In Javascript, for example, I would do this as `contactId=contactId+", "+newContactId`, which is what I'm trying to replicate. — MDalt, Jun 19 '15 at 12:29
If you want to append a new value to a value in an existing row, then you need to update that row, not insert a new one. You can 'upsert' with a merge statement, but not from a trigger - use merge instead of insert in the first place. I'd really question your approach anyway though; you're denormalising the data - why not have a separate table with one row for each recordid/contactid combination? What will you do when you want to change or remove a contact? — Alex Poole, Jun 19 '15 at 16:28

score 1 · Accepted Answer · answered Jun 22 '15 at 09:38

You can't change an insert into an update in a trigger. There are several issues in your erroring line anyway:

CONTACTID + ', ' + :NEW.CONTACTID AS CONTACTID;

As well as using + instead of || for concatenation, you are not assigning that new value to a variable - CONACTID doesn't exist in the PL/SQL scope you're working in, and although you could potentially do the concatenation as part of an update (without the AS clause either), you can't do that update inside the before-insert for-each-row insert trigger as you'd hit a mutating table error, and even if that worked the insert would still happen as well.

It looks like you really want to do an 'upsert' - update an existing record if it exists, or add a new record if it does not. In Oracle you do that with the merge statement. Here's an example, which I've wrapped in a procedure just to make the calls cleaner and reduce duplication - it would normally just be plain SQL rather than embedded in PL/SQL:

create table test_merge (recordid number primary key,
  contactid varchar2(4000));

create procedure p_merge(p_recordid number, p_contactid number) as
begin
  merge into test_merge t
  using (select p_recordid as recordid, p_contactid as contactid from dual) x
  on (t.recordid = x.recordid)
  when matched then
    update set contactid = t.contactid ||','|| x.contactid
  when not matched then
    insert (recordid, contactid) values (x.recordid, x.contactid);
end;
/

exec p_merge (1, 123);
exec p_merge (1, 234);
exec p_merge (1, 456);
exec p_merge (2, 567);

select * from test_merge;

  RECORDID CONTACTID          
---------- --------------------
         1 123,234,456         
         2 567

But storing a comma-separated list of denormalised data is not a good idea. You need to think about how you will modify that value (if you have to remove a contact, say, or want them to be ordered) and how you will use the values in the string - assuming each contact ID is a reference out to another table you'd have to deconstruct the string to do the join. You're also limited but the maximum size of a string column.

It would be much better to have a separate table linking the record and contact IDs:

create table test_parent (recordid number primary key);
create table test_child (recordid number references test_parent(recordid),
  contactid number);

insert into test_parent (recordid) values (1);
insert into test_parent (recordid) values (2);
insert into test_child (recordid, contactid) values (1, 123);
insert into test_child (recordid, contactid) values (1, 234);
insert into test_child (recordid, contactid) values (1, 456);
insert into test_child (recordid, contactid) values (2, 567);

If you want to see the comma-separated list then generate that when you need it, with string aggregation. 11g and upwards have the built-in listagg function to do this:

select p.recordid,
  listagg(c.contactid, ',') within group (order by c.contactid) as contactids
from test_parent p
left join test_child c on c.recordid = p.recordid
group by p.recordid
order by p.recordid;

  RECORDID CONTACTIDS         
---------- --------------------
         1 123,234,456         
         2 567

Excellently explained. I see your point about data normalisation - will look at implementing multiple tables as you suggest. — MDalt, Jun 22 '15 at 14:36

score 0 · Answer 2 · answered Jun 18 '15 at 15:42

0

In SQL Server and Microsoft Access you concatenate with the +, But in oracle you use || to concatenate and in mySQL you use the CONCAT function. Are you sure that in your situation you are using the right concatenate (If I remember correctly PLS means it's an oracle error, so you should be using || instead of +)?

answered Jun 18 '15 at 15:42

Nickknack

827
4
12
26

2

`||` is the concatenation operator defined by the SQL **standard** it's nothing Oracle specific. Microsoft simply decided to ignore that standard even though it existed **long** before Access or SQL Server were created. – Jun 18 '15 at 17:45

'PLS-00103 Encountered the symbol "+" ...' error when trying to concatenate some strings in a trigger

2 Answers2