| Correct My Code [message #458389] |
Sat, 29 May 2010 07:15  |
snsiddiqui
Messages: 172
Registered: December 2008
|
Senior Member |
|
|
Dear All
I want to update/insert records from one user to other user therefore I write this code in my form but it gives me error.
REPORT_DETAILS is a detail table of REPORT_PARAM, given code is working fine at master tables.
Form always goes in TOO_MANY_ROWS exception.
Pleae correct my code, my requirement is: if data found then update else insert new record.
declare
cursor c1 is select * from factory_data.report_details order by rd_rp_id;
rec c1%rowtype;
v_check varchar2(1) := null;
begin
for rec in c1 loop
begin
select 'x' into v_check
from store.report_details
where rd_rp_id = rec.rd_rp_id;
update store.report_details
set
RD_CD_DCODE = rec.rd_cd_dcode,
RD_ABBR = rec.rd_abbr,
RD_DOC_TYPE = rec.rd_doc_type,
RD_DOC_NO = rec.rd_doc_no,
RD_ISSUE_NO = rec.rd_issue_no,
RD_REV_NO = rec.rd_rev_no,
RD_ENT_BY = rec.rd_ent_by,
RD_CC_COMPC = rec.rd_cc_compc,
RD_CB_BRANC = rec.rd_cb_branc,
RD_CFY_CODE = rec.rd_cfy_code,
RD_ENT_DATE = rec.rd_ent_date,
RD_LAST_UPDT_BY = rec.rd_last_updt_by,
RD_LAST_UPDT_DATE = rec.rd_last_updt_date
where rd_rp_id = rec.rd_rp_id;
exception
when no_data_found then message('no data found');message('no data found');
insert into store.report_details select * from factory_data.report_details
where rd_rp_id = rec.rd_rp_id;
when too_many_rows then message('2 many rows');message('2 many rows');
insert into store.report_details(rd_rp_id, rd_abbr, rd_doc_type, rd_doc_no, rd_issue_no, rd_rev_no, rd_ent_by, rd_cc_compc, rd_cb_branc, rd_cfy_code, rd_ent_date, rd_last_updt_date, rd_last_updt_by)
values(rec.rd_rp_id, rec.rd_abbr, rec.rd_doc_type, rec.rd_doc_no, rec.rd_issue_no, rec.rd_rev_no, rec.rd_ent_by, rec.rd_cc_compc, rec.rd_cb_branc, rec.rd_cfy_code, rec.rd_ent_by, rec.rd_last_updt_date, rec.rd_last_updt_by);
end;
exit when c1%notfound;
end loop;
end;
[Updated on: Sat, 29 May 2010 10:41] Report message to a moderator |
|
|
|
|
|
| Re: Correct My Code [message #458406 is a reply to message #458403] |
Sat, 29 May 2010 10:59  |
snsiddiqui
Messages: 172
Registered: December 2008
|
Senior Member |
|
|
But I have to insert records in TOO_MANY_ROWS exception because a master can have multiple child records therefore I written insert statment in my TOO_MANY_ROWS exception then why it is not inserting records?
I wiil be very gratefull to you, if you explain me by any other sample code.
|
|
|
|
| Re: Correct My Code [message #458410 is a reply to message #458406] |
Sat, 29 May 2010 11:59 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You say you are getting an error when you run this - what is the error message?
What is the primary key of report_details?
|
|
|
|
| Re: Correct My Code [message #458413 is a reply to message #458410] |
Sat, 29 May 2010 12:18 |
snsiddiqui
Messages: 172
Registered: December 2008
|
Senior Member |
|
|
There isn't any error in my code, actually TOO_MANY_ROWS exception raised whenever I execute my code and it should because there might be multiple records in report_details against any report_param master record.
REPORT_DETAILS is child table and it hasn't any primary key.
I have to execute insert statment in TOO_MANY_ROWS exception and insert all records who matched with cursor record.
|
|
|
|
|
|
|
|
| Re: Correct My Code [message #458473 is a reply to message #458417] |
Sun, 30 May 2010 15:37 |
gregor
Messages: 86
Registered: March 2010
Location: Germany
|
Member |
|
|
Hi,
1.) please check your (c1-cursor) select in SQLPUS with real inputvalues for "rd_rp_id".
"cursor c1 is select * from factory_data.report_details order by rd_rp_id" -> what is the result in sqlplus ? -> No_Data or more than one row? -- then ckeck your eception-handling in sqlplus. Does the Insert or Update work?
2.1. It isn't a good practice to do DML( inert/update/del..) in an EceptionHandler ( check what possible before). 2-2. your insert/update in Exception, had no exception handler. -- better: In case of error raise an error or give information to the mainprogramm .
GG
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
