Home » SQL & PL/SQL » SQL & PL/SQL » whats wrong with this delete code?
whats wrong with this delete code? [message #273395] Wed, 10 October 2007 05:21 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i am trying to delete particular data from table prv_properties,
prv_info is its parent table(prv_id is the primary key)...and so i want to delete property name 'CHECK_SERVICES_LABEL' for all the prv_ids in prv_properties, note that there are many rows with
this property in prv_properties...ofcourse with different
prv_ids....

the below code works fine


Begin
    delete from prv_properties 
    where upper(property_name) =upper('CHECK_SERVICES_LABEL') 
    and prv_id in (select prv_id from prv_info);
    
    dbms_output.put_line('Deleted the prv_property CHECK_SERVICES_LABEL');
    
Exception
    When others then
    RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM);
end;
/





but i now i want to check whether prv_properties has any
row with 'CHECK_SERVICES_LABEL' or not, and then delete,
i tried the below code...doesnt work


Declare
    v_cnt number;

Begin
    select count(*) into v_cnt
    from prv_properties where upper(property_name) =upper('CHECK_SERVICES_LABEL');
    dbms_output.put_line(v_cnt);

    if v_cnt > 0 then
        delete from prv_properties 
        where upper(property_name) =upper('CHECK_SERVICES_LABEL') 
        and prv_id in (select prv_id from prv_info);
    
        dbms_output.put_line('Deleted the prv_property CHECK_SERVICES_LABEL');
    else
	dbms_output.put_line('control at else part');
        dbms_output.put_line('There is no CHECK_SERVICES_LABEL property');
    
Exception
    When others then
    RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM);
end;
/




get this error

SQL> @codes;
Exception
*
ERROR at line 19:
ORA-06550: line 19, column 1:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the
following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
ORA-06550: line 22, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
end not pragma final instantiable order overriding static
member constructor map



any alternative for this? that i want to first verify
whether property_name column of prv_properties has data
with CHECK_SERVICES_LABEL....and if there? only then delete
for all the prv_ids...?


Re: whats wrong with this delete code? [message #273397 is a reply to message #273395] Wed, 10 October 2007 05:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A forgotten "end if" ?
Re: whats wrong with this delete code? [message #273416 is a reply to message #273395] Wed, 10 October 2007 06:52 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
yes thanks
Re: whats wrong with this delete code? [message #273418 is a reply to message #273416] Wed, 10 October 2007 07:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can skip the SELECT entirely, and just check SQL%ROWCOUNT after the delete - that will tell you how many records were deleted.
Re: whats wrong with this delete code? [message #273420 is a reply to message #273395] Wed, 10 October 2007 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also skip the exception clause that in fact just hide:
- the line where the error comes form
- the full error message

Regards
Michel
Re: whats wrong with this delete code? [message #273422 is a reply to message #273420] Wed, 10 October 2007 07:18 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
And if prv_info is a "true" parent tabel for prv_properties (like dept is for emp), than you can skip
and prv_id in (select prv_id from prv_info);

too.
Because there will be no prv_id's in prv_properties that are NOT in prv_info (that would be like emp's inventing there own dept_id's).
Re: whats wrong with this delete code? [message #273429 is a reply to message #273395] Wed, 10 October 2007 07:46 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And the UPPER is unecessary:
=upper('CHECK_SERVICES_LABEL')
Previous Topic: find and delete records without parent key
Next Topic: Select query in procedure!
Goto Forum:
  


Current Time: Fri Dec 02 12:16:51 CST 2016

Total time taken to generate the page: 0.05467 seconds