Home » SQL & PL/SQL » SQL & PL/SQL » while insert data into the table by using fuction ( oracle 9i,windows xp)
while insert data into the table by using fuction [message #323893] Thu, 29 May 2008 23:41 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hi all,


i created a function vehicle to insert data into the vehicle table
and i returning the type varchar2 .if the data is inserted into the table vehicle then the function will return SUCCESS else FAILURE, thats wat i actually written here. But its not inserting the data into the table vehicle. i thought there some errors in identifiers so i gave the values as hard values...
still im not getting insert data into the table vehicle it returns me NULL.i used dbms print for debugging.pls clear this error.


CREATE OR REPLACE FUNCTION ins_veh_fn (
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
)
RETURN 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
DBMS_OUTPUT.put_line ('1');

OPEN get_veh_cityid;

FETCH get_veh_cityid
INTO v_veh_cityid;

DBMS_OUTPUT.put_line ('2');

OPEN get_veh_base_cityid;

FETCH get_veh_base_cityid
INTO v_veh_base_cityid;

DBMS_OUTPUT.put_line ('3');

SELECT MAX (ROWNUM)
INTO v_before
FROM l_vehicle;
DBMS_OUTPUT.put_line (''||v_before);

DBMS_OUTPUT.put_line ('4' || veh_insu_expdate);
v_ins_date := TO_DATE (veh_insu_expdate, 'DD/MM/YYYY');
DBMS_OUTPUT.put_line ('4jihuj' || v_ins_date);
v_pol_date := TO_DATE (veh_poll_expdate, 'DD/MM/YYYY');
v_tax_date := TO_DATE (veh_tax_expdate, 'DD/MM/YYYY');
DBMS_OUTPUT.put_line ('5');

-- INSERT INTO l_vehicle
-- (vehicle_id, city_id, maximum_vehicle_capacity,
-- vehicle_reg_number, vehicle_insurance_number,
-- vehicle_insurance_expire_date, v_polution_bill_number,
-- v_polution_bill_expire_date, vehicle_tax_bill_number,
-- vehicle_tax_expire_date, vehicle_owner_name, vehicle_type,
-- vehicle_age, base_city_id
-- )
-- VALUES (l_veh_id.NEXTVAL, 1, max_veh_capacity,
-- veh_reg_num, veh_insu_num,
-- '12/may/1945', veh_poll_num,
-- '12/may/1945', veh_tax_num,
-- '12/may/1945', veh_owner_name, veh_type,
-- veh_age, 1
-- );
INSERT INTO l_vehicle
(vehicle_id, city_id, maximum_vehicle_capacity,
vehicle_reg_number, vehicle_insurance_number,
vehicle_insurance_expire_date, v_polution_bill_number,
v_polution_bill_expire_date, vehicle_tax_bill_number,
vehicle_tax_expire_date, vehicle_owner_name, vehicle_type,
vehicle_age, base_city_id
)
VALUES (l_veh_id.NEXTVAL, 1, 55,
'552', '122',
'12/may/1945', '32',
'12/may/1945', '32',
'12/may/1945', 'sed', 'fc',
11, 1
);

DBMS_OUTPUT.put_line ('a');

SELECT MAX (ROWNUM)
INTO v_after
FROM l_vehicle;
DBMS_OUTPUT.put_line ('6'||v_after);
IF v_after > v_before
THEN
RETURN 'SUCCESSFUL';
ELSE
RETURN 'FAILURE';
END IF;

CLOSE get_veh_base_cityid;

CLOSE get_veh_cityid;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'NULL';
COMMIT;
END ins_veh_fn;
Re: while insert data into the table by using fuction [message #323896 is a reply to message #323893] Thu, 29 May 2008 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Perhaps I'm old & going blind, but due nonexistent formatting the only COMMIT seems to be in the EXCEPTION processing.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above.
Re: while insert data into the table by using fuction [message #323911 is a reply to message #323893] Fri, 30 May 2008 00:42 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Anyone can support me on this ..
i tried many possiblities still cudnt solve the error.
Re: while insert data into the table by using fuction [message #323916 is a reply to message #323911] Fri, 30 May 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm like Ana: my eyes are too old to read not formated post.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: while insert data into the table by using fuction [message #323919 is a reply to message #323893] Fri, 30 May 2008 01:01 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Thanks for the reply..
The following code u seeing is formatted ...







CREATE OR REPLACE FUNCTION Ins_veh_fn
(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)
RETURN 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
dbms_Output.Put_Line('1');

OPEN Get_veh_CityId;

FETCH Get_veh_CityId INTO v_veh_CityId;

dbms_Output.Put_Line('2');

OPEN Get_veh_Base_CityId;

FETCH Get_veh_Base_CityId INTO v_veh_Base_CityId;

dbms_Output.Put_Line('3');

SELECT MAX(ROWNUM)
INTO v_Before
FROM l_Vehicle;

dbms_Output.Put_Line(''
||v_Before);

dbms_Output.Put_Line('4'
||veh_Insu_expDate);

v_Ins_Date := To_Date(veh_Insu_expDate,'DD/MM/YYYY');

dbms_Output.Put_Line('4jihuj'
||v_Ins_Date);

v_pol_Date := To_Date(veh_Poll_expDate,'DD/MM/YYYY');

v_Tax_Date := To_Date(veh_Tax_expDate,'DD/MM/YYYY');

dbms_Output.Put_Line('5');
-- INSERT INTO l_vehicle
-- (vehicle_id, city_id, maximum_vehicle_capacity,
-- vehicle_reg_number, vehicle_insurance_number,
-- vehicle_insurance_expire_date, v_polution_bill_number,
-- v_polution_bill_expire_date, vehicle_tax_bill_number,
-- vehicle_tax_expire_date, vehicle_owner_name, vehicle_type,
-- vehicle_age, base_city_id
-- )
-- VALUES (l_veh_id.NEXTVAL, 1, max_veh_capacity,
-- veh_reg_num, veh_insu_num,
-- '12/may/1945', veh_poll_num,
-- '12/may/1945', veh_tax_num,
-- '12/may/1945', veh_owner_name, veh_type,
-- veh_age, 1
-- );

INSERT INTO l_Vehicle
(Vehicle_Id,
City_Id,
Maximum_Vehicle_Capacity,
Vehicle_reg_Number,
Vehicle_Insurance_Number,
Vehicle_Insurance_Expire_Date,
v_polutIon_Bill_Number,
v_polutIon_Bill_Expire_Date,
Vehicle_Tax_Bill_Number,
Vehicle_Tax_Expire_Date,
Vehicle_Owner_Name,
Vehicle_Type,
Vehicle_Age,
Base_City_Id)
VALUES (l_veh_Id.Nextval,
1,
55,
'552',
'122',
'12/may/1945',
'32',
'12/may/1945',
'32',
'12/may/1945',
'sed',
'fc',
11,
1);

dbms_Output.Put_Line('a');

SELECT MAX(ROWNUM)
INTO v_After
FROM l_Vehicle;

dbms_Output.Put_Line('6'
||v_After);

IF v_After > v_Before THEN
RETURN 'SUCCESSFUL';
ELSE
RETURN 'FAILURE';
END IF;

CLOSE Get_veh_Base_CityId;

CLOSE Get_veh_CityId;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
RETURN 'NULL';
END Ins_veh_fn;
Re: while insert data into the table by using fuction [message #323922 is a reply to message #323893] Fri, 30 May 2008 01:09 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
If it's returning null, then it is going to the exception. In order to figure out what is going wrong, remove the exception clause. It should tell you exactly what is failing.
Re: while insert data into the table by using fuction [message #323924 is a reply to message #323893] Fri, 30 May 2008 01:13 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

thanks for the reply. ..


i removed the exception clause and compiled again ..but function s created with no errors and warnings ..

when im invoking that function im getting error as follows

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "LOGISTICS.INS_VEH_FN", line 81

Re: while insert data into the table by using fuction [message #323927 is a reply to message #323924] Fri, 30 May 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-14551: cannot perform a DML operation inside a query
 *Cause:  DML operation like insert, update, delete or select-for-update
          cannot be performed inside a query or under a PDML slave.
 *Action: Ensure that the offending DML operation is not performed or
          use an autonomous transaction to perform the DML operation within
          the query or PDML slave.

Regards
Michel
Re: while insert data into the table by using fuction [message #323947 is a reply to message #323927] Fri, 30 May 2008 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would guess that you've got a trigger on the table, or that you're calling this function from within a piece of SQL.

On an unrelated note - what is going on with the 'SELECT MAX(ROWNUM) queries?
Is that some particularly inefficient way of checking that a row has been inserted? If so, you should be aware of the following things:
1) You're probably doing 2 full table scans on the l_vehicle table during this processing. That'll slow things up when the tables get big.
2) If you're in a multi user environment then the value from the second query will be bigger than the first even if you don't insert but someone else does (as long as they commit their changes.
3) If deletes from this table are allowed, then the result of the second query could be smaler than the first one if someone else has deleted rows.
4) (and most importantly) the second query is totally pointless, as there's no way for the insert to fail that doesn't raise an exception. So if the insert fails, you'll just skip to the exception handler and ignore your 'Success/Fail' logic entirely.
Re: while insert data into the table by using fuction [message #324005 is a reply to message #323893] Fri, 30 May 2008 04:48 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Thanks JRowbottom for the useful information..

by the way i have to know how to find out whether the data s inserted or not ?? ....
i was using rownum it might not work correct ..but so if u support me on this pls do response ...
Re: while insert data into the table by using fuction [message #324020 is a reply to message #324005] Fri, 30 May 2008 05:11 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the insert statement doesn't raise an exception, then it's inserted a record. It's that simple.
You can check the SQL%ROWCOUNT variable after the insert if you really need confirmation, but before you do that, you need to answer, to your own satisfaction, this question:

What can happen to an INSERT statement that would prevent it from inserting a row, but not raise an exception?
Previous Topic: SQL for linking Oracle GL accounts to Oracle Projects
Next Topic: email program
Goto Forum:
  


Current Time: Thu Dec 08 01:55:05 CST 2016

Total time taken to generate the page: 0.12647 seconds