Home » SQL & PL/SQL » SQL & PL/SQL » how to find whether the table is inserted or not in a procedure (oracle 9.0.2.0 ,windows xp)
how to find whether the table is inserted or not in a procedure [message #326043] |
Tue, 10 June 2008 00:58  |
|
hi all.,,
the following procedure created by me ..
its working fine but its time consuming when im using rownum to find the table is inserted or not ,...
since i was posting this topic senior members advised me that its not the right way to find table is inserted or not....
pls advise me other solutions ..
/* Formatted on 2008/06/10 11:06 (Formatter Plus v4.8. */
CREATE OR REPLACE PROCEDURE ins_veh_proc (
veh_city_name IN VARCHAR2,
max_veh_capacity IN NUMBER,
veh_reg_num IN VARCHAR2,
veh_insu_num IN VARCHAR2,
veh_insu_expdate IN VARCHAR2,
veh_poll_num IN VARCHAR2,
veh_poll_expdate IN VARCHAR2,
veh_tax_num IN VARCHAR2,
veh_tax_expdate IN VARCHAR2,
veh_owner_name IN VARCHAR2,
veh_type IN VARCHAR2,
veh_age IN NUMBER,
veh_base_city_name IN VARCHAR2,
retval OUT VARCHAR2
)
IS
v_veh_cityid NUMBER;
v_veh_base_cityid NUMBER;
v_before NUMBER;
v_after NUMBER;
v_ins_date DATE;
v_pol_date DATE;
v_tax_date DATE;
CURSOR get_veh_cityid
IS
SELECT city_id
FROM l_city
WHERE city_name = veh_city_name;
CURSOR get_veh_base_cityid
IS
SELECT city_id
FROM l_city
WHERE city_name = veh_base_city_name;
BEGIN
OPEN get_veh_cityid;
FETCH get_veh_cityid
INTO v_veh_cityid;
OPEN get_veh_base_cityid;
FETCH get_veh_base_cityid
INTO v_veh_base_cityid;
DBMS_OUTPUT.put_line ('1');
SELECT NVL (MAX (ROWNUM), 0)
INTO v_before
FROM l_vehicle;
DBMS_OUTPUT.put_line ('max row num' || v_before);
v_ins_date := TO_DATE (veh_insu_expdate, 'DD/MM/YYYY');
v_pol_date := TO_DATE (veh_poll_expdate, 'DD/MM/YYYY');
v_tax_date := TO_DATE (veh_tax_expdate, 'DD/MM/YYYY');
INSERT INTO l_vehicle
(vehicle_id, city_id, maximum_vehicle_capacity,
vehicle_reg_number, v_polution_bill_number,
v_polution_bill_expire_date, vehicle_insurance_number,
vehicle_insurance_expire_date, vehicle_tax_bill_number,
vehicle_tax_expire_date, vehicle_age, vehicle_owner_name,
vehicle_type, base_city_id
)
VALUES (l_veh_id.NEXTVAL, v_veh_cityid, max_veh_capacity,
veh_reg_num, veh_poll_num,
v_pol_date, veh_insu_num,
v_ins_date, veh_tax_num,
v_tax_date, veh_age, veh_owner_name,
veh_type, v_veh_base_cityid
);
SELECT MAX (ROWNUM)
INTO v_after
FROM l_vehicle;
IF v_after > v_before
THEN
retval := 'SUCCESSFUL';
ELSE
retval := 'FAILURE';
END IF;
CLOSE get_veh_base_cityid;
CLOSE get_veh_cityid;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: how to find whether the table is inserted or not in a procedure [message #326079 is a reply to message #326075] |
Tue, 10 June 2008 02:22   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The two best solutions for checking that the insert failed are:
1) Wrap the Insert statement in a BEGIN...EXCEPTION...END block that actually does something with the exception (ie an exception block that doesn't just call dbms_output.put_line.
The only way your row can fail to insert is if an exception is raised - trap this and you'll know it failed.
2) Check the SQL%ROWCOUNT variable after the insert statement. This will be equal to the number of rows inserted by the SQL
|
|
|
Re: how to find whether the table is inserted or not in a procedure [message #326080 is a reply to message #326075] |
Tue, 10 June 2008 02:23   |
sarwagya
Messages: 87 Registered: February 2008 Location: Republic of Nepal
|
Member |
|
|
Quote: |
3/ If you don't use SQL*Plus family or the like or don't set serveroutput on, you don't know if there was an error or not
|
In this case, we can use a variable, assign it with some value and use it to find out if inserted or not.
Can we know the possible errors in case of INSERT?
If yes, we can handle all of them in the exception block.
|
|
|
|
|
Re: how to find whether the table is inserted or not in a procedure [message #326109 is a reply to message #326043] |
Tue, 10 June 2008 04:08   |
|
thanks all.. .
if i included a following stmt its not inserting data s into the l_vehicle table ..you may ask why im using this following statement ..
if GET_veh_CITYID%notfound then
insert into L_CITY(CITY_ID,CITY_NAME)
values (l_city_id.nextval,veh_city_name );
end if;
im using sequence here for l_city_id .so it will automatically increment id .city_id is a primary key n not null in most of the tables . if city id not found from the cursor then i have to insert new city id by using nextval and city name.
pls tell me the appropriate way to deal with this problem.
It causes me not inserting data into the l_vehicle table and l_city table. .
CREATE OR REPLACE PROCEDURE ins_veh_proc (
veh_city_name IN VARCHAR2,
max_veh_capacity IN NUMBER,
veh_reg_num IN VARCHAR2,
veh_insu_num IN VARCHAR2,
veh_insu_expdate IN VARCHAR2,
veh_poll_num IN VARCHAR2,
veh_poll_expdate IN VARCHAR2,
veh_tax_num IN VARCHAR2,
veh_tax_expdate IN VARCHAR2,
veh_owner_name IN VARCHAR2,
veh_type IN VARCHAR2,
veh_age IN NUMBER,
veh_base_city_name IN VARCHAR2,
retval OUT VARCHAR2
)
IS
v_veh_cityid NUMBER;
v_veh_base_cityid NUMBER;
v_before NUMBER;
v_after NUMBER;
v_ins_date DATE;
v_pol_date DATE;
v_tax_date DATE;
CURSOR get_veh_cityid
IS
SELECT city_id
FROM l_city
WHERE city_name = veh_city_name;
CURSOR get_veh_base_cityid
IS
SELECT city_id
FROM l_city
WHERE city_name = veh_base_city_name;
BEGIN
OPEN get_veh_cityid;
FETCH get_veh_cityid
INTO v_veh_cityid;
OPEN get_veh_base_cityid;
FETCH get_veh_base_cityid
INTO v_veh_base_cityid;
if GET_veh_CITYID%notfound then
insert into L_CITY(CITY_ID,CITY_NAME)
values (l_city_id.nextval,veh_city_name);
end if;
DBMS_OUTPUT.put_line ('1');
SELECT NVL (MAX (ROWNUM), 0)
INTO v_before
FROM l_vehicle;
DBMS_OUTPUT.put_line ('max row num' || v_before);
v_ins_date := TO_DATE (veh_insu_expdate, 'DD/MM/YYYY');
v_pol_date := TO_DATE (veh_poll_expdate, 'DD/MM/YYYY');
v_tax_date := TO_DATE (veh_tax_expdate, 'DD/MM/YYYY');
INSERT INTO l_vehicle
(vehicle_id, city_id, maximum_vehicle_capacity,
vehicle_reg_number, v_polution_bill_number,
v_polution_bill_expire_date, vehicle_insurance_number,
vehicle_insurance_expire_date, vehicle_tax_bill_number,
vehicle_tax_expire_date, vehicle_age, vehicle_owner_name,
vehicle_type, base_city_id
)
VALUES (l_veh_id.NEXTVAL, v_veh_cityid, max_veh_capacity,
veh_reg_num, veh_poll_num,
v_pol_date, veh_insu_num,
v_ins_date, veh_tax_num,
v_tax_date, veh_age, veh_owner_name,
veh_type, v_veh_base_cityid
);
SELECT MAX (ROWNUM)
INTO v_after
FROM l_vehicle;
IF v_after > v_before
THEN
retval := 'SUCCESSFUL';
ELSE
retval := 'FAILURE';
END IF;
CLOSE get_veh_base_cityid;
CLOSE get_veh_cityid;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
[Updated on: Tue, 10 June 2008 04:09] Report message to a moderator
|
|
|
Re: how to find whether the table is inserted or not in a procedure [message #326113 is a reply to message #326109] |
Tue, 10 June 2008 04:25   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The bit doing the insert looks ok. The rest of the code is the utter disaster areal it's always been.
If you want to know wht the error you're getting is, get rid of tha texception handler, and run the code again.
If your Insert fails then the only thing your calling procedure will know about it is that the returned value from the procedure is null.
You will never get a returned value of 'FAILURE' except in the case when your insert works, but someone else has deleted one or more rows at the same time.
If your insert fails, an exception will be raised, and you'll jump straight to that abomination of an Exception handler, which will quietly ignore the whole thing.
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 12:27:23 CST 2025
|