Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Help
PL/SQL Help [message #564259] Wed, 22 August 2012 06:52 Go to next message
sufs2000
Messages: 4
Registered: August 2012
Junior Member
Hello, I need some help modifying a procedure. I have created the procedure below which will, when logged in as the wipe user, wipe a table belonging to a particular user, providing wipe has been granted the priviledges. What I want to do however is modify it to first check how many rows are currently in the table to be truncated and then if this number is greater than zero to prompt the user "are you sure you want to truncate this table?". Then if the user confirms the table will be truncated. Can anyone help with this? Any help would be much appreciated! Smile
 
create or replace procedure wipe_table (p_owner_name varchar2, p_table_name varchar2)
        is begin execute immediate
                'delete from '||p_owner_name||'.'||p_table_name;
                 commit;
end;

[Updated on: Wed, 22 August 2012 06:58]

Report message to a moderator

Re: PL/SQL Help [message #564265 is a reply to message #564259] Wed, 22 August 2012 07:07 Go to previous messageGo to next message
cookiemonster
Messages: 11072
Registered: September 2008
Location: Rainy Manchester
Senior Member
PL/SQL procedures are not interactive. You'll need a client program to do that - shell script, java, c... whatever.
Re: PL/SQL Help [message #564275 is a reply to message #564265] Wed, 22 August 2012 08:24 Go to previous messageGo to next message
sufs2000
Messages: 4
Registered: August 2012
Junior Member
Oh ok, thanks, I didn't know that. Is there some way instead then where if the Table has more than one row it will complete the procedure otherwise some kind of dbms output saying "there are no rows to delete"?
Re: PL/SQL Help [message #564284 is a reply to message #564275] Wed, 22 August 2012 08:43 Go to previous messageGo to next message
flyboy
Messages: 1775
Registered: November 2006
Senior Member
Most probably, changing the procedure to function and returning the value of SQL%ROWCOUNT after calling DELETE statement would be the most proper option for getting count of affected rows.

Of course, you would have to treat it to the in whatever environment calling that function to "say" it.

About DBMS_OUTPUT: note that it is not the correct way for communication between PL/SQL and the client (whatever it is). DBMS_OUTPUT just fills the buffer with some text. The caller may be unable to display it. It may be disabled on the client as well. Now what? It will not be shown at all.

Treat any interaction with the end user on the client, not inside PL/SQL. Period.
Re: PL/SQL Help [message #564285 is a reply to message #564275] Wed, 22 August 2012 08:44 Go to previous message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Convert it to a function that returns an integer and add
return SQL%ROWCOUNT;

which returns the number of delete rows.
BUT
1/ Do NOT commit inside a procedure, ONLY the caller knows if it wants to commit or not.
2/ You realize that you can delete all data in your database with such a procedure.

Regards
Michel

[Updated on: Wed, 22 August 2012 08:44]

Report message to a moderator

Previous Topic: Unable to view package body in SQLplus using DBA_SOURCE table
Next Topic: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK
Goto Forum:
  


Current Time: Wed Oct 22 03:44:19 CDT 2014

Total time taken to generate the page: 0.16352 seconds