Solved

need help with this trigger code

Posted on 2006-07-21
6
952 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

632 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