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 Go to next message
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 #274492 is a reply to message #274490] Tue, 16 October 2007 04:17 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this link. It explains exactly what you want.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Regards

Raj
Re: problem with my delete procedure [message #274495 is a reply to message #274492] Tue, 16 October 2007 04:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, thanks for providing clear scripts showing what you were trying to do. That really makes things easier.
Re: problem with my delete procedure [message #274505 is a reply to message #274490] Tue, 16 October 2007 04:40 Go to previous messageGo to next message
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 #274509 is a reply to message #274505] Tue, 16 October 2007 04:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
soujanya_srk wrote on Tue, 16 October 2007 11:40

delete from sample where iid=piid and pusid = l_tt_List(i);

Shouldn't that be:

delete from sample where iid=piid and usid = l_tt_List(i);

MHE
Re: problem with my delete procedure [message #274514 is a reply to message #274490] Tue, 16 October 2007 04:51 Go to previous messageGo to next message
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

Re: problem with my delete procedure [message #274536 is a reply to message #274514] Tue, 16 October 2007 06:17 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Lose the WHEN OTHERS - all it does is remove the details of where the error happened from the error stack.
Previous Topic: Facing problem using rank function.........
Next Topic: JOB scheduler.
Goto Forum:
  


Current Time: Tue Dec 06 06:32:52 CST 2016

Total time taken to generate the page: 0.16745 seconds