Hi, I am writing a trigger for a

[robdunfey]

Hi,

I am writing a trigger for an oracle form. When a record is created the trigger is executed. It inserts a UID into a field on the form called applicant number. I want to make the UID out of a combination of the number of applicants in any one year and the year of application.

E.g. 300 applications in 2003, then UID of applicant 301 is '30103'.

So taking the current YY I search the database for applicants with UID's like %%%YY. But how do I execute a select count query and return the value to a variable I can use in my trigger? I see some web pages that suggest using Execute Immediate, but this wont work because I am using forms 6. How do I execute a COUNT SQL query using DBMS_SQL?

Any help much appreciated, Im a complete novice, so sorry if this seems obvious. Any useful websites for learning PL?SQL for forms would also be much appreciated.

Many Thanks; Rob

DECLARE

date_yy CHAR;
noOfApps VARCHAR(3);

BEGIN

date_yy := TO_CHAR(SYSDATE, 'YY');
:APPLICANT1.APPLICANT# := date_yy;

Execute Immediate 'select COUNT(APPLICANT#) from APPLICANT where APPLICANT# like '%%%'||date_yy;'
INTO numOfApps;

END;

 

[sem]

First of all I find it to be fallacious the idea of concatenating date with ID. This violates even the 1-st normal form, if you know what I'm talking about.
Then I can not understand the need in dynamic sql when your query is completely static. Have I missed something essential? BTW % stands for ANY combinations of ARBITRARY characters, so using %%% makes no sence at all.

select COUNT(APPLICANT#) INTO numOfApps
from APPLICANT where APPLICANT# like '%'|| TO_CHAR(SYSDATE, 'YY');

Regards, Dima

http://seminihin.narod.ru