| PL/SQL Help [message #564259] |
Wed, 22 August 2012 06:52  |
 |
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! 
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  |
cookiemonster
Messages: 9290
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 #564284 is a reply to message #564275] |
Wed, 22 August 2012 08:43 |
flyboy
Messages: 1673
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 |
 |
Michel Cadot
Messages: 54712
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Convert it to a function that returns an integer and add
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 |
|
|
|
] 
Members
Search
Help
Register
Login
Home
