Click here to Skip to main content
14,392,016 members
Rate this:
Please Sign up or sign in to vote.
See more:
oracle query is taking too much time to execute , though it doesn't finish except by terminating it , it faces a problem in the update part , and makes no updates or takes too long time , see the syntax right here and if anyone knows anything please help

create or replace function sp_addClient(CITIZENNAME VARCHAR2,
NATIONALNUMBER VARCHAR2 default null,
ADDRESS VARCHAR2 default null,
PHONE VARCHAR2 default null,
MOBILE VARCHAR2 default null,
FAX VARCHAR2 default null,
EMAIL VARCHAR2 default null,
FATHERNAME VARCHAR2 default null,
MOTHERNAME VARCHAR2 default null,
PSURNAME VARCHAR2 default null,
REGISTERNUMBER VARCHAR2 default null,
REGISTERPLACE VARCHAR2 default null,
BIRTHDATE VARCHAR2 default null,
BIRTHPLACE VARCHAR2 default null,
COMPANYNAME varchar2 default null,
P_NATIONALITY INTEGER default 1,
P_PASSEPORT_NO VARCHAR2 default null,
P_STAY_CARD VARCHAR default null)
return integer is
Result integer := null;
cr types.ref_cursor;
begin

open cr for SELECT a.citizen_id

FROM citizens a
WHERE (a.national_number = nationalNumber and
nationalNumber is not null)
or ((a.citizen_name = citizenName) and (a.father_name = fatherName) and
(a.surname = Psurname) and
(a.mother_name = motherName or motherName is null));
fetch cr into result;
close cr;
if result = -1 or result is null then
SELECT citizens_seq.NEXTVAL INTO Result FROM DUAL;

INSERT INTO citizens
VALUES
(Result,
CITIZENNAME,
NATIONALNUMBER,
ADDRESS,
PHONE,
MOBILE,
FAX,
EMAIL,
FATHERNAME,
MOTHERNAME,
PSURNAME,
REGISTERNUMBER,
REGISTERPLACE,
to_date(BIRTHDATE, 'dd-mm-yyyy'),
BIRTHPLACE,
COMPANYNAME,
null,
null,
null,
0,
null,
P_NATIONALITY,
P_PASSEPORT_NO,
P_STAY_CARD);
else
update citizens set

national_number = nationalNumber,
birth_date =to_date(BIRTHDATE, 'dd-mm-yyyy'),
birth_place = BIRTHPLACE,
register_number = REGISTERNUMBER,
register_place = REGISTERPLACE,
nationality = P_NATIONALITY,
passeport_no = P_PASSEPORT_NO,
stay_card = P_STAY_CARD,
--address = ADDRESS,
phone = PHONE,
mobile = MOBILE,
fax = FAX,
email = EMAIL,
company_name = COMPANYNAME

where citizen_id = result;
END if;

return(Result);
end sp_addClient;
Posted
Comments
Sudhakar Shinde 16-Jan-12 0:00am
   
What is primary key of the table citizens?
Rate this:
Please Sign up or sign in to vote.

Solution 1

Create an index on table:

CREATE INDEX IDX_CITIZEN On CITIZEN(CITIZEN_ID);
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

well it seems that it was a silly mistake of me , some parameters have the same name of the modified table fields , check this

phone = PHONE,
mobile = MOBILE,
fax = FAX,
email = EMAIL

this absolutely wont work ...

thank u all 4 ur help
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100