CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apexoracle-apex-5
thelamp
thelamp

Reputation: 89

How to update column uniquely for each row, in Oracle Apex 5?

I have created a column that is basically a concatenation of other columns. What I want it to do is update itself with the information that comes from the form that is currently being filled out by the user. However, right now it just updates it self with the most recent row (record in the table) and not only that but all the rows in this column (ID) are the same (all of them are based off the most recent row). 

DECLARE
  ID VARCHAR2(40);
BEGIN
 SELECT NEW_ID
 INTO ID
 FROM (SELECT YEAR || '-' || COL2|| PRIMKEY as NEW_ID
       FROM TABLE
       ORDER BY MAIN_ID DESC)
 WHERE ROWNUM = 1;
 RETURN ID; 
END;

So for rows 1, 2, 3, I want the ID to be 17-A1, 17-B2, and 17-C3. But all of the ID rows for them are 17-C3. If I add a record (row) 4, then all of the ID rows will become 17-D4.

Upvotes: 1

Views: 669

Answers (1)

APC
APC

Reputation: 146239

Instead of doing this is Apex you could do it in the database, using the 11g Virtual Column syntax.

alter table t23 
    add new_id as (YEAR || '-' || COL2|| PRIMKEY )
;

This column will be populated automatically when you insert a row. It will be maintained automatically if your application updates any of those three columns. It cannot be overwritten.

The key (oh ho!) advantage of this approach is that the logic for maintaining NEW_ID exists in one place, the table declaration, and is applied however the table is populated. The disadvantage is that we cannot include the column in INSERT statements, so they must always be specified with explicit projections: insert into t23 (year, col2, primkey) values (..... But this is good practice anyway, so not a major disadvantage.

Upvotes: 2

Related Questions