Solved

need help with this trigger code

Posted on 2006-07-21
6
935 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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now