i seem have problem procedure. search , gets search correctly returns me same answer many times point fatal error of exhausted memory.this code:
function pet_search(pettype in varchar2, breed in varchar2, color in varchar2, petsize in varchar2, ts in varchar2, energy in varchar2, space in varchar2) return sys_refcursor pet_search_result sys_refcursor; type_id number; breed_id number; color_id number; size_id number; ts_id number; energy_id number; space_id number; begin select tp.pet_type_code type_id dbadmin.pettype tp tp.pet_type_name = pettype; select b.pet_race_code breed_id dbadmin.petrace b b.pet_race_name = breed; select pc.pet_color_code color_id dbadmin.petcolor pc pc.pet_color = color; select ps.pet_size_code size_id dbadmin.petsize ps ps.pet_size = petsize; select ls.pet_learn_code ts_id dbadmin.petlearningskill ls ls.pet_learn_skill = ts; select en.pet_energy_code energy_id dbadmin.petenergy en en.pet_energy_level = energy; select sp.pet_space_code space_id dbadmin.petspace sp sp.pet_space = space; open pet_search_result select pet_type_name, pet_race_name, pet_cond_name, pet_energy_level, pet_learn_skill, vet_name, person_name, petlocation, petnotes, petabandondescription, pet_space, pet_treatment, pet_color, pet_sickness_name, pet_med_name pet, pettype, petrace, petcondition, petsize,petenergy, petlearningskill, veterinary, person, petspace, pettreatments, petcolor, petsickness, petmedicine pettype.pet_type_code = type_id , petrace.pet_race_code = breed_id , pet.pet_color_code = color_id , pet.pet_size_code = size_id , pet.pet_learn_code = ts_id , pet.pet_energy_code = energy_id , pet.petspace_id = space_id; return pet_search_result; exception when no_data_found return null; end; end pet_search_package; can see mistake ? suggestions appreciated
the problem seems in select query, if clause doenst satisfy conditions rows duplicated due join. see example below states problem , how resolve it.
select pet_type_name, pet_race_name, pet_cond_name, pet_energy_level, pet_learn_skill, vet_name, person_name, petlocation, petnotes, petabandondescription, pet_space, pet_treatment, pet_color, pet_sickness_name, pet_med_name pet, pettype, petrace, petcondition, petsize,petenergy, petlearningskill, veterinary, person, petspace, pettreatments, petcolor, petsickness, petmedicine pettype.pet_type_code = type_id , petrace.pet_race_code = breed_id , pet.pet_color_code = color_id , pet.pet_size_code = size_id , pet.pet_learn_code = ts_id , pet.pet_energy_code = energy_id , pet.petspace_id = space_id; for example here 2 tables emp , dept,
sql> select * emp; empno ename job mgr hiredate sal comm deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 smith clerk 7902 17-dec-80 800 20 7499 allen salesman 7698 20-feb-81 1600 300 30 7521 ward salesman 7698 22-feb-81 1250 500 30 7566 jones manager 7839 02-apr-81 2975 20 7654 martin salesman 7698 28-sep-81 1250 1400 30 7698 blake manager 7839 01-may-81 2850 30 7782 clark manager 7839 09-jun-81 2450 10 7788 scott analyst 7566 19-apr-87 3000 20 7839 king president 17-nov-81 5000 10 7844 turner salesman 7698 08-sep-81 1500 0 30 7876 adams clerk 7788 23-may-87 1100 20 7900 james clerk 7698 03-dec-81 950 30 7902 ford analyst 7566 03-dec-81 3000 20 7934 miller clerk 7782 23-jan-82 1300 10 14 rows selected. sql> select * dept; deptno dname loc ---------- -------------- ------------- 10 accounting new york 20 research dallas 30 sales chicago 40 operations boston this query return 1 row if conditions in clause satisfied.
sql> select a.empno,b.deptno,a.ename emp a,dept b a.empno=7369 , b.deptno=20; empno deptno ename ---------- ---------- ---------- 7369 20 smith here return duplicates if conditions clause not matched, or less number of differntiators.
sql> select a.empno,b.deptno,a.ename emp a,dept b a.empno=7369 ; empno deptno ename --------- ---------- ---------- 7369 10 smith 7369 20 smith 7369 30 smith 7369 40 smith if have reference between these tables advisable use in clause , compare
