Home » SQL & PL/SQL » SQL & PL/SQL » problem with my delete procedure
problem with my delete procedure [message #274490] |
Tue, 16 October 2007 04:04  |
soujanya_srk
Messages: 111 Registered: November 2006 Location: HYDERABAD
|
Senior Member |
|
|
i have a table
create table sample(iid number,usid number);
insert into sample values(1,1);
insert into sample values(1,2);
insert into sample values(1,3);
insert into sample values(2,4);
insert into sample values(2,5);
insert into sample values(2,6);
insert into sample values(3,7);
insert into sample values(3,8);
insert into sample values(3,9);
now i am getting two input parameters
first one is iid, and second one the comma seperated list
of usids
i have to remove the usids corresponding to the particular iid
create or replace procedure mydel(piid in number,pusid in varchar2)
as
begin
delete from sample where usid in ('pusid')
and iid=piid;
end;
when i execte above code,
getting
SQL> exec mydel(1,'2,3');
BEGIN mydel(1,'2,3'); END;
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "MYDEL", line 5
ORA-06512: at line 1
SQL>
i have 2 doubts
1) how to accept the input parameter which is varchar2 in
the proc?
2) suppose i delete the data which is not there
something like
delete from sample where iid=1 and usid in(5,6)..
this combination is not there in table, then wil i need
to handle no_data_found??
|
|
|
|
|
Re: problem with my delete procedure [message #274505 is a reply to message #274490] |
Tue, 16 October 2007 04:40   |
soujanya_srk
Messages: 111 Registered: November 2006 Location: HYDERABAD
|
Senior Member |
|
|
alright, this is what i tried..
i already have a procedure, which , when given
a list and a delimiter, will tokenize and give u the
list in one by one fashion
the proc is
CREATE OR REPLACE PROCEDURE List_To_Table (
n_List IN VARCHAR2,
n_Dlim IN VARCHAR2,
n_TabLen OUT BINARY_INTEGER,
n_Tab OUT DBMS_UTILITY.Uncl_Array
) AS
l_Dlim VARCHAR2(1);
l_start BINARY_INTEGER := 1;
l_index BINARY_INTEGER;
l_count BINARY_INTEGER := 0;
l_escape varchar2(1) := '\';
l_list varchar2(32767);
BEGIN
-- Return if list is null
IF LTRIM(RTRIM(n_List)) IS NULL THEN
n_TabLen := 0;
RETURN;
END IF;
-- Interpret delimiter
IF n_Dlim IS NULL THEN
l_Dlim := ',';
ELSE
l_Dlim := SUBSTR(n_Dlim,1,1);
END IF;
-- escape character for delimiter field (if the delimiter character itself part of the word)
l_list := replace(n_list, l_escape||n_Dlim , chr(9));
-- Loop for each element
LOOP
l_index := NVL(INSTR(n_List,l_Dlim,l_start),0);
IF l_index = 0 THEN
EXIT;
END IF;
l_count := l_count + 1;
-- n_Tab(l_count) := REPLACE(SUBSTR(n_List, l_start, l_index-l_start),'''','''''');
n_Tab(l_count) := SUBSTR(n_List, l_start, l_index-l_start);
n_Tab(l_count) := replace(n_Tab(l_count), chr(9), n_Dlim);
l_start := l_index + 1;
END LOOP;
-- Last element
n_Tab(l_count+1) := SUBSTR(n_List, l_start);
n_Tab(l_count+1) := replace(n_Tab(l_count+1), chr(9), n_Dlim);
n_TabLen := l_count+1;
END List_To_Table;
/
anyone can please run the above proc...it works fine
now i am trying to use this proc, into my situation,
as follows
create or replace procedure mydel(piid in number,pusid in varchar2)
as
l_Num NUMBER;
l_tt_List Dbms_Utility.uncl_array;
begin
List_To_Table(pusid, ',' ,l_Num, l_tt_List);
FOR i IN 1..l_Num LOOP
BEGIN
dbms_output.put_line(l_tt_List(i));
delete from sample where iid=piid and pusid = l_tt_List(i);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM);
end;
i am still unable to delete rows
SQL> select * from sample;
IID USID
---------- ----------
1 2
1 3
2 4
2 5
2 6
1 1
6 rows selected.
SQL> exec mydel(1,'1,2');
1
2
PL/SQL procedure successfully completed.
SQL> select * from sample;
IID USID
---------- ----------
1 2
1 3
2 4
2 5
2 6
1 1
6 rows selected.
|
|
|
|
Re: problem with my delete procedure [message #274514 is a reply to message #274490] |
Tue, 16 October 2007 04:51   |
soujanya_srk
Messages: 111 Registered: November 2006 Location: HYDERABAD
|
Senior Member |
|
|
create or replace procedure mydel(piid in number,pusid in varchar2)
as
l_Num NUMBER;
l_tt_List Dbms_Utility.uncl_array;
begin
List_To_Table(pusid, ',' ,l_Num, l_tt_List);
FOR i IN 1..l_Num LOOP
BEGIN
dbms_output.put_line(l_tt_List(i));
dbms_output.put_line(piid);
delete from sample where iid=piid and usid = l_tt_List(i);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM);
end;
this will do !!!
thanks, and i dont think there is a need to use
no_data_found for delete stmt
|
|
|
|
Goto Forum:
Current Time: Mon Feb 10 11:48:45 CST 2025
|