Home » SQL & PL/SQL » SQL & PL/SQL » Invalid Number exception with the cursor (Oracle 10g)
Invalid Number exception with the cursor [message #338091] Sun, 03 August 2008 07:27 Go to next message
Adarshsk
Messages: 5
Registered: August 2008
Junior Member
This the Stored procedure i am having......, in this i am getting
v_all_srvc_area_ids to contain comma seperated ids,
eg: v_all_srvc_area = (123456,456456).

for this v_all_srvc_areas, i need to get some other column from another table.
so i am using "select long_englsh_nm from table1 where id IN(v_all_srvc_areas)";

if i execute the above statement seperately, i am getting the answers.
but inside the cursor, i am getting error like INVALID NUMBER.
can't we used IN() inside a cursor?????

Here is my SP(inside a package actually)

Please help....


procedure get_all_subdivisions(srvc_area IN varchar2,cur_sub_division out t_cursor)
is
Cnt number;
SplitOn varchar2(1);
v_srvc_area varchar2(1000);
v_indiv_srvc_area varchar2(100);
v_indiv_srvc_area_id integer;
v_all_srvc_area_ids varchar2(1000);
v_name varchar2(100);
begin
v_srvc_area := srvc_area;
SplitOn :=',';
Cnt := 1;
v_all_srvc_area_ids := null;

WHILE (Instr(v_srvc_area,SplitOn)>0)
LOOP

select ltrim(rtrim(Substr(v_srvc_area,1,Instr(v_srvc_area,SplitOn)-1))) into v_indiv_srvc_area FROM DUAL;
dbms_output.put_line(v_indiv_srvc_area);

select DISTINCT TFAA.SRVC_AREA_ID into v_indiv_srvc_area_id FROM TRKFC.TRKFCG_SRVC_AREA TFAA
WHERE TFAA.LONG_ENGLSH_NM = v_indiv_srvc_area;
dbms_output.put_line(v_indiv_srvc_area_id);

v_all_srvc_area_ids := v_all_srvc_area_ids || v_indiv_srvc_area_id || ',';

v_srvc_area := Substr(v_srvc_area,Instr(v_srvc_area,SplitOn)+1,length(v_srvc_area));
Cnt := Cnt + 1;
END LOOP;
dbms_output.put_line(v_all_srvc_area_ids);

-- To remove the last comma present in the string builder
v_all_srvc_area_ids := Substr(v_all_srvc_area_ids,1,length(v_all_srvc_area_ids)-1);

dbms_output.put_line(v_all_srvc_area_ids);

open cur_sub_division for select distinct long_englsh_nm from TRKFC.TRKFCG_SBDVSN where SRVC_AREA_ID IN(v_all_srvc_area_ids);

/* LOOP
FETCH cur_sub_division INTO v_name;
EXIT WHEN cur_sub_division%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP; */


end get_all_subdivisions;


Thanks in advance
Re: Invalid Number exception with the cursor [message #338092 is a reply to message #338091] Sun, 03 August 2008 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "varying in list" or "value list in string" or the like, this has been asked and answered many times.

Regards
Michel
Re: Invalid Number exception with the cursor [message #338098 is a reply to message #338092] Sun, 03 August 2008 09:27 Go to previous message
Adarshsk
Messages: 5
Registered: August 2008
Junior Member
Thanks a lot. The str2tbl function helped me a lot.
Previous Topic: Upload csv file into Oracle db
Next Topic: Concurrent Insert statements Using Dbms_job.Submit
Goto Forum:
  


Current Time: Sat Dec 10 14:24:40 CST 2016

Total time taken to generate the page: 0.12601 seconds