Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

need help with this trigger code

Posted on 2006-07-21
6
Medium Priority
?
964 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 1500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

824 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