Solved

need help with this trigger code

Posted on 2006-07-21
6
943 Views
Last Modified: 2007-12-19
Hi guys,

where am i goin wrong.

CREATE OR REPLACE TRIGGER MONITOR_SCHEDULE_JOBS
AFTER UPDATE ON fnd_concurrent_requests
FOR EACH ROW
declare
v_conc_program_name varchar2;
v_mailid varchar2;
v_phase fnd_concurrent_requests.phase_code%type;
v_status fnd_concurrent_requests.status_code%type;
v_message varchar2;
V_exists varchar2;
begin
v_phase := :new.phase_code;
v_status := :new.status_code;
select user_concurrent_program_name into v_conc_program_name from fnd_conc_req_summary_v where request_id = :new.request_id;
select 'exists' into v_exists from monitor where  CONCURRENT_REQUEST_NAME = v_conc_program_name;
update monitor set phase= 'v_phase' and status= 'v_status' and flag='Y' where CONCURRENT_REQUEST_NAME=v_conc_program_name;
commit;
exception
when no_data_found then return;
end;
/

show errors;
Errors for TRIGGER MONITOR_SCHEDULE_JOBS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/1     PL/SQL: SQL Statement ignored
13/37    PL/SQL: ORA-00933: SQL command not properly ended
SQL>

help appreciated

thanks
0
Comment
Question by:amankhan
  • 3
  • 3
6 Comments
 
LVL 14

Accepted Solution

by:
sathyagiri earned 500 total points
ID: 17156739
CREATE OR REPLACE TRIGGER MONITOR_SCHEDULE_JOBS
AFTER UPDATE ON fnd_concurrent_requests
FOR EACH ROW
declare
v_conc_program_name fnd_conc_req_summary_v.user_concurrent_program_name%type;
v_mailid varchar2(100);
v_phase fnd_concurrent_requests.phase_code%type;
v_status fnd_concurrent_requests.status_code%type;
v_message varchar2(100);
V_exists varchar2(100);
begin
v_phase := :new.phase_code;
v_status := :new.status_code;
select user_concurrent_program_name into v_conc_program_name from fnd_conc_req_summary_v where request_id = :new.request_id;
select 'exists' into v_exists from monitor where  CONCURRENT_REQUEST_NAME = v_conc_program_name;
update monitor set phase= 'v_phase' and status= 'v_status' and flag='Y' where CONCURRENT_REQUEST_NAME=v_conc_program_name;
commit;
exception
when no_data_found then return;
end;
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17156758
At least your update needs changed...no "and" between fields that are updated:

update monitor set phase= 'v_phase', status= 'v_status', flag='Y' where CONCURRENT_REQUEST_NAME=v_conc_program_name;
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17156794
also 2 more things:

1) as sathyagiri showed, you need lengths on your declared variables (not just varchar2)
2) no quotes around the variables you use in the update:

update monitor set phase= v_phase, status= v_status, flag='Y'
where CONCURRENT_REQUEST_NAME=v_conc_program_name;


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:sathyagiri
ID: 17157043
Yes you shouldn't single quote variables
Try this
CREATE OR REPLACE TRIGGER MONITOR_SCHEDULE_JOBS
AFTER UPDATE ON fnd_concurrent_requests
FOR EACH ROW
declare
v_conc_program_name fnd_conc_req_summary_v.user_concurrent_program_name%type;
v_mailid varchar2(100);
v_phase fnd_concurrent_requests.phase_code%type;
v_status fnd_concurrent_requests.status_code%type;
v_message varchar2(100);
V_exists varchar2(100);
begin
v_phase := :new.phase_code;
v_status := :new.status_code;
select user_concurrent_program_name into v_conc_program_name from fnd_conc_req_summary_v where request_id = :new.request_id;
select 'exists' into v_exists from monitor where  CONCURRENT_REQUEST_NAME = v_conc_program_name;
update monitor set phase= v_phase and status= v_status and flag='Y' where CONCURRENT_REQUEST_NAME=v_conc_program_name;
commit;
exception
when no_data_found then return;
end;
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17157223
you still have the "AND" statements in the update.  It needs to be:

CREATE OR REPLACE TRIGGER MONITOR_SCHEDULE_JOBS
AFTER UPDATE ON fnd_concurrent_requests
FOR EACH ROW
declare
v_conc_program_name monitor.CONCURRENT_REQUEST_NAME%type;
v_mailid varchar2(100);
v_phase fnd_concurrent_requests.phase_code%type;
v_status fnd_concurrent_requests.status_code%type;
v_message varchar2(255);
V_exists varchar2(6);
begin
v_phase := :new.phase_code;
v_status := :new.status_code;
select user_concurrent_program_name into v_conc_program_name from fnd_conc_req_summary_v where request_id = :new.request_id;
select 'exists' into v_exists from monitor where  CONCURRENT_REQUEST_NAME = v_conc_program_name;
update monitor set phase= v_phase, status= v_status, flag='Y' where CONCURRENT_REQUEST_NAME=v_conc_program_name;
commit;
exception
when no_data_found then return;
end;
/
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17157432
You're right JRB.. Guess overlooked it..
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question