Solved

need help with this trigger code

Posted on 2006-07-21
6
944 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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