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 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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.Cool */
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 #326046 is a reply to message #326043] Tue, 10 June 2008 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Remove the when others then null and you can ditch the check.
Re: how to find whether the table is inserted or not in a procedure [message #326050 is a reply to message #326043] Tue, 10 June 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And please read OraFAQ Forum Guide, especially "How to format your post?" section and the use of code tags.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel

Re: how to find whether the table is inserted or not in a procedure [message #326056 is a reply to message #326043] Tue, 10 June 2008 01:30 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
put the insert statement inside a BEGIN END block as in:

BEGIN
	INSERT INTO table_name() VALUES ();
	DBMS_OUTPUT.PUT_LINE('successful');
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('unsuccessful');
END;
Re: how to find whether the table is inserted or not in a procedure [message #326058 is a reply to message #326043] Tue, 10 June 2008 01:32 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

thanks frank for your reply...

my query is working fine but when im using max(rownum) it will look into the whole table so it took much time ..
if i include delete stmt after insert stmt then i cudnt able to find whether row inserted into the table or not. .


pls gimme the alternate solution to find the table inserted or not.
Re: how to find whether the table is inserted or not in a procedure [message #326065 is a reply to message #326056] Tue, 10 June 2008 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sarwagya wrote on Tue, 10 June 2008 08:30
put the insert statement inside a BEGIN END block as in:

BEGIN
	INSERT INTO table_name() VALUES ();
	DBMS_OUTPUT.PUT_LINE('successful');
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('unsuccessful');
END;


This is very BAD for so many reasons that I can't even enumerate them.

Regards
Michel

Re: how to find whether the table is inserted or not in a procedure [message #326066 is a reply to message #326058] Tue, 10 June 2008 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SELECT NVL (MAX (ROWNUM), 0)

Why not "SELECT COUNT(*)"?

Without saying that the whole logic is wrong unless you are in serializable transaction level.

Regards
Michel
Re: how to find whether the table is inserted or not in a procedure [message #326067 is a reply to message #326065] Tue, 10 June 2008 01:59 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Oh! Is it that bad?

Please let me know about them.
Re: how to find whether the table is inserted or not in a procedure [message #326069 is a reply to message #326043] Tue, 10 June 2008 02:05 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

but micheal
if i have thousands of rows then it will count total number of rows in the table..so count(*) leads more time consuming right ??...
Re: how to find whether the table is inserted or not in a procedure [message #326072 is a reply to message #326069] Tue, 10 June 2008 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"SELECT NVL (MAX (ROWNUM), 0)" = "SELECT COUNT(*)" but the former is slower.

Regards
Michel
Re: how to find whether the table is inserted or not in a procedure [message #326074 is a reply to message #326043] Tue, 10 June 2008 02:13 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

thanks cadot .
is there any other way to locate this query more faster than count(*) ?? ?...
Re: how to find whether the table is inserted or not in a procedure [message #326075 is a reply to message #326067] Tue, 10 June 2008 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@sarwagya

1/ You ignore the actual error
2/ You ignore where the error comes from
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.
4/ It is useless, that is it does not say more (but actually far less) than if it does not exist
...

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #326092 is a reply to message #326080] Tue, 10 June 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can we know the possible errors in case of INSERT?

No.

Regards
Michel
Re: how to find whether the table is inserted or not in a procedure [message #326104 is a reply to message #326080] Tue, 10 June 2008 03:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There are a huge list of errors that can occur.
There is no point even trying to handle them all.
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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 Go to previous messageGo to next message
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.
Re: how to find whether the table is inserted or not in a procedure [message #326115 is a reply to message #326109] Tue, 10 June 2008 04:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 10 June 2008 08:16
And please read OraFAQ Forum Guide, especially "How to format your post?" section and the use of code tags.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel

Previous Topic: find out isolation levels used for my database
Next Topic: Trigger not working
Goto Forum:
  


Current Time: Sat Dec 03 04:04:36 CST 2016

Total time taken to generate the page: 0.10280 seconds