469,338 Members | 8,424 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

PS/SQL script needed to update and prompt user to commit or rollback

Hi, i am new to PL/SQL scripting, and need a script to have our operators who run our end of day processes to run their own Oracle PL/SQL and then based on the results of the SQL be prompted to commit or rollback. The basic logic would look like (this would allow us to save alot of overtime cost paid to the supporting DBA staff):-

SQL> update tablea set c = d;

1 row updated

display "Commit? " prompt
accept user's response
if user's response is yes
then
commit
exit /* i want the script to exit from the SQL session here */
else
rollback
exit /* i want the script to exit from the SQL session here */
end
Jan 11 '08 #1
9 11510
Dave44
153 100+
Hi, i am new to PL/SQL scripting, and need a script to have our operators who run our end of day processes to run their own Oracle PL/SQL and then based on the results of the SQL be prompted to commit or rollback. The basic logic would look like (this would allow us to save alot of overtime cost paid to the supporting DBA staff):-

SQL> update tablea set c = d;

1 row updated

display "Commit? " prompt
accept user's response
if user's response is yes
then
commit
exit /* i want the script to exit from the SQL session here */
else
rollback
exit /* i want the script to exit from the SQL session here */
end
the only way i know of to do it is to have a variable with the "&" in front of it.
However it would be quite crude.
Expand|Select|Wrap|Line Numbers
  1. [159]SQL> ed
  2. Wrote file afiedt.buf
  3.  
  4.   1* select * from dual where dummy = &var
  5. [159]SQL> /
  6. Enter value for var: 'A'
  7. old   1: select * from dual where dummy = &var
  8. new   1: select * from dual where dummy = 'A'
  9.  
  10. no rows selected
  11.  
what tool would the users be using to do this? SQLPLUS or something else?



It seems like you'd want a procedure that could be called to perform the operation and then allow the user to see the results somehow and then have them be able to pass either the commit or rollback command based on what they see.
Jan 11 '08 #2
Thanks for the reply. Yes i would be using SQLPLUS and yes i nned a procedure to effect this. Just tell you how the process works. The operator does a telnet login to a UNIX server and gets a menu. One of the menu option is run End Of Day SQL. This opens a editor (e.g, vi) session and the user copy & paste the SQL to be run, e.g. ( a SQL which is frequently run):-

Expand|Select|Wrap|Line Numbers
  1.  update sol set SOL_EOD_STATUS = 'N', DB_STAT_CODE = ‘C’;
  2.  
Upon writing and saving this to a pre-determiend file( say eod.sql), using UNIX shel script i take this file and transform it thus:-

Expand|Select|Wrap|Line Numbers
  1.   update sol set SOL_EOD_STATUS = 'N', DB_STAT_CODE = ‘C’;
  2.   accept response prompt 'Commit Transacton y/n: '
  3.   if &response = 'y'
  4.   then
  5.      commit;
  6.      exit
  7.   else
  8.      rollback;
  9.      exit;
  10.   endif
  11.  
  12.  
Finally, i run this file using sqlplus:-

sqlplus user/password @oed.sql

I found this sql on the net and hoped that i could modify it but it did not work:-

[NET CODE START]
Expand|Select|Wrap|Line Numbers
  1.  
  2. SET SERVEROUTPUT ON
  3. set feedback on
  4.  
  5. DECLARE
  6.  
  7. i INTEGER;
  8.  
  9. BEGIN
  10. SELECT COUNT(*)
  11. INTO i
  12. FROM all_tables;
  13.  
  14. IF i > 100 THEN
  15. DBMS_OUTPUT.PUT_LINE('There Are More Than 100 Tables');
  16. ELSE
  17. DBMS_OUTPUT.PUT_LINE('There Are Less Than 100 Tables');
  18. END IF;
  19. END;
  20. /
  21.  
[NET CODE END]

[ NET code output start]
There Are More Than 100 Tables

PL/SQL procedure successfully completed.
[ net coe output end]

[ MY MODIFIED NET CODE STARTS HERE ]
Expand|Select|Wrap|Line Numbers
  1.  
  2. SET SERVEROUTPUT ON
  3. set feedback on
  4.  
  5. DECLARE
  6.  
  7. i INTEGER;
  8.  
  9. BEGIN
  10.  
  11. update sol set SOL_EOD_STATUS = 'N', DB_STAT_CODE = ‘C’;
  12.  
  13. accept res prompt 'Enter Response: '
  14. IF &res = 'y' THEN
  15.    commit;
  16. ELSE
  17.    rollback;
  18. END IF;
  19. END;
  20. /
  21.  
[ MY MODIFIED NET CODE ENDS HERE ]

[ my modified net code execution results starts ]
Enter value for res: y
old 10: IF &res = 'y' THEN
new 10: IF y = 'y' THEN
accept res prompt 'Enter Response: '
*
ERROR at line 9:
ORA-06550: line 9, column 8:
PLS-00103: Encountered the symbol "RES" when expecting one of the following:
:= . ( @ % ;
[ my modified net code execution results end]

Please can you tell how to precceed. I am not experienced at wring the code to achieve the desired results.
Jan 12 '08 #3
Dave44
153 100+
Ok. First prompting in the manner you want is difficult because the first thing sqlplus does is resolve the variables. note below how the dbms_output line comes after the prompt for res:
Expand|Select|Wrap|Line Numbers
  1. [143]dave@MYORADB> begin
  2.   2  
  3.   3     dbms_output.put_line('You must enter ''Y'' to commit or ''N'' to rollback');
  4.   4     
  5.   5     if upper(&res) = 'Y' then
  6.   6        dbms_output.put_line('Its Y');
  7.   7     else 
  8.   8        dbms_output.put_line('Its not Y');
  9.   9     end if;
  10.  10  end;
  11.  11  /
  12. Enter value for res: 'N'
  13. old   5:    if upper(&res) = 'Y' then
  14. new   5:    if upper('N') = 'Y' then
  15. You must enter 'Y' to commit or 'N' to rollback
  16. Its not Y
  17.  
  18. PL/SQL procedure successfully completed.
  19.  
I think we need to take a step back and look at the whole process. what is the point of running the update statement you show and then prompting for a commit or rollback? before they commit or rollback is there some other procedure that they have ran? I just dont understand why they would want to rollback if its just a simple update that they "want" (they pasted in) to run.

the code snipet you found on the net is very different from yours in that you have the sqlplus variable in there whereas the first piece of code uses a pl/sql variable. two different things. It is possible to select from a table into a pl/sql variable and then transfer that into a sqlplus variable. I wont display that unless you reply that you want it as this reply is getting long winded already.

is oracle on the unix box they telnet into? if so just set up the unix oracle users ORACLE_HOME env variable correctly so that they can just open up sqlplus and let them paste their sql directly in sqlplus and run it. then they can query it and if they like it commit it that way. its not all that different from what you are suggesting.

Have you looked at setting up SQL Developer (free oracle tool) for the users? its a nicer tool for viewing query results and they dont have to telnet in that way.
Im gonna wait for your reply before putting forth more ideas
Jan 12 '08 #4
Hi, i am new to PL/SQL scripting, and need a script to have our operators who run our end of day processes to run their own Oracle PL/SQL and then based on the results of the SQL be prompted to commit or rollback. The basic logic would look like (this would allow us to save alot of overtime cost paid to the supporting DBA staff):-

SQL> update tablea set c = d;

1 row updated

display "Commit? " prompt
accept user's response
if user's response is yes
then
commit
exit /* i want the script to exit from the SQL session here */
else
rollback
exit /* i want the script to exit from the SQL session here */
end

I have faced the similar problem and solved it in following manner:

1. Perpared a Visual Basic program to do all the things that earlier my PL/SQL procedure was doing.

2. Did not place any commit in the program.

3. After everything is over, I showed a dialog box to the user confirming his/her actions.

4. I also logged his details like username, password, date and time when commit was asked for etc.

5. Filnally, with all of user's consent, I put a commit statement for the connection.

My boss was more than happy with this.
Jan 12 '08 #5
I have faced the similar problem and solved it in following manner:

1. Perpared a Visual Basic program to do all the things that earlier my PL/SQL procedure was doing.

2. Did not place any commit in the program.

3. After everything is over, I showed a dialog box to the user confirming his/her actions.

4. I also logged his details like username, password, date and time when commit was asked for etc.

5. Filnally, with all of user's consent, I put a commit statement for the connection.

My boss was more than happy with this.
Vikas, thanks for your VB suggestion. However, i am looking for a sqlplus solution to incorporate into an existing UNIX menu interface that the operators use e.g, to shutdown the database in order to do a cold backup.

Dave, this is how the system works. The operators who run the end of day process do this entirely from a custom menu running on UNIX servers. When an error occurs, the operators contact the on-call programmer who diagnose the problem and e-mails an sql script to be run to resolve this problem. The operatos do not have the ability to run sql's so they forward this to the on-call dba who runs the sql.
management want to cut out the overtime cost for the dba (who simply execute the sql). i have two options. Option 1) Build up a library of sqls run to date that the operators can select from a menu and execute whenever that problem occurs again. This option is easy as i can put the commit in the sql's and do not require the operator to confirm the commit. For this option, the dba's overtime cost would come only from new sql. Option 2) have the operators run sqls on the fly (the ones set by the on-call programmer). Here is where i would want to have the operator conform before they commit because the sql that the on-call programmer supply may have logical errors ( or may not have been tested properly), such as, updating/delete/etc more records that expected and before commiting the operator may inform the on-call programmer for confirmation. If by chance the operator paste a commit in the sql provided by the on-call programmer, my pre-processor would remove the commit before running the sql. I know that this second option is more dangerous as operators can update the database outside of the on-call progrmmer directive and can possible corrupt the database, but i will be putting in controls to mitigate against this such as a) supervisor's password protection before the eod sql is run and b) all sql's run are e-mail to the dba's on a daily basis. Both options will be presented to management for their preference.

Thanks.
Jan 12 '08 #6
Dave44
153 100+
Vikas, thanks for your VB suggestion. However, i am looking for a sqlplus solution to incorporate into an existing UNIX menu interface that the operators use e.g, to shutdown the database in order to do a cold backup.

Dave, this is how the system works. The operators who run the end of day process do this entirely from a custom menu running on UNIX servers. When an error occurs, the operators contact the on-call programmer who diagnose the problem and e-mails an sql script to be run to resolve this problem. The operatos do not have the ability to run sql's so they forward this to the on-call dba who runs the sql.
management want to cut out the overtime cost for the dba (who simply execute the sql). i have two options. Option 1) Build up a library of sqls run to date that the operators can select from a menu and execute whenever that problem occurs again. This option is easy as i can put the commit in the sql's and do not require the operator to confirm the commit. For this option, the dba's overtime cost would come only from new sql. Option 2) have the operators run sqls on the fly (the ones set by the on-call programmer). Here is where i would want to have the operator conform before they commit because the sql that the on-call programmer supply may have logical errors ( or may not have been tested properly), such as, updating/delete/etc more records that expected and before commiting the operator may inform the on-call programmer for confirmation. If by chance the operator paste a commit in the sql provided by the on-call programmer, my pre-processor would remove the commit before running the sql. I know that this second option is more dangerous as operators can update the database outside of the on-call progrmmer directive and can possible corrupt the database, but i will be putting in controls to mitigate against this such as a) supervisor's password protection before the eod sql is run and b) all sql's run are e-mail to the dba's on a daily basis. Both options will be presented to management for their preference.

Thanks.
ok, I have to recommend using option 1. when you have tested and proven SQL code you want to keep it in packages that are properly version controlled. This drastically reduces the chance of human error in pasting in the wrong or bad code. Another thing is that as the data volume grows, you cant trust that a human will be able to spot logical errors right. checking 10 rows is easy.. checking 10000 rows would suck, especially in sql plus.

As for your comment about the programmers sql may have logical errors in it... of course it might. thats why we have unit testing and integration testing before its promoted to production. I do hope that the written code is tested before its run against production data.

Just a thought but would it be possible to set up a scheduled oracle job that checks a table entry each night to see if it needs to run the end of day process or not and if so calls the stored procedures necessary, then calls another procedure that verifies the data and then based on the results of the verifying procedure commits or rollsback Have the whole process log messages to a log table to track the steps; how long they take and whether they were successful. this way you dont need the DBAs to run the code and the operators only need to login when the process is done and check the log messages to see how the process ran. I assume you already paid for Oracle so make it do as much as possible for you
Jan 12 '08 #7
ok, I have to recommend using option 1. when you have tested and proven SQL code you want to keep it in packages that are properly version controlled. This drastically reduces the chance of human error in pasting in the wrong or bad code. Another thing is that as the data volume grows, you cant trust that a human will be able to spot logical errors right. checking 10 rows is easy.. checking 10000 rows would suck, especially in sql plus.

As for your comment about the programmers sql may have logical errors in it... of course it might. thats why we have unit testing and integration testing before its promoted to production. I do hope that the written code is tested before its run against production data.

Just a thought but would it be possible to set up a scheduled oracle job that checks a table entry each night to see if it needs to run the end of day process or not and if so calls the stored procedures necessary, then calls another procedure that verifies the data and then based on the results of the verifying procedure commits or rollsback Have the whole process log messages to a log table to track the steps; how long they take and whether they were successful. this way you dont need the DBAs to run the code and the operators only need to login when the process is done and check the log messages to see how the process ran. I assume you already paid for Oracle so make it do as much as possible for you
Dave, Thanks for the suggestion, but based on how our end of day process works it will be difficult to implement. Magement has indicated that they want to use option 2. I have done some further testing and the following code works ok.
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.    res varchar(1);
  3. BEGIN
-- the on-call programmers code goes here which the operators paste
Expand|Select|Wrap|Line Numbers
  1.    delete from save_changes;
  2.    insert into save_changes values ('&y_or_n');
  3.    SELECT response INTO res FROM save_changes;
  4.    IF res = 'y' or res = 'Y'
  5.    then
  6.       commit;
  7.    ELSE
  8.       rollback;
  9.    END IF;
  10. END;
  11. /
  12. exit
If i wanted to have when the above code runs, to get the normal SQL screen messages displayed such as '1 row deleted' or 1 row 'inserted' or the 'commit completed' messages, how do i do this?
Jan 16 '08 #8
If a naughty operator types CTL-C then a COMMIT is issued as the session disconnects.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE
  2. PROCEDURE confirm_commit( p_response IN VARCHAR2 )
  3. IS
  4.    l_response VARCHAR2( 255 );
  5. BEGIN
  6.    l_response := TRIM( UPPER( p_response ) );
  7.    DBMS_OUTPUT.PUT_LINE
  8.       ( 'You answered: ' || p_response );
  9.  
  10.    IF l_response IN ( 'COMMIT', 'COMMIT;' ) 
  11.    THEN
  12.       COMMIT;
  13.       DBMS_OUTPUT.PUT_LINE( 'Commit completed.' );
  14.    ELSE
  15.       ROLLBACK;
  16.       DBMS_OUTPUT.PUT_LINE( 'Rollback completed.' );
  17.    END IF;
  18.  
  19. EXCEPTION
  20.    WHEN OTHERS THEN
  21.       ROLLBACK;
  22.       DBMS_OUTPUT.PUT_LINE( 'Rollback completed.' );
  23. END confirm_commit;
  24.  
-- Add the following to the end of each
-- operator script or script template:

--
Expand|Select|Wrap|Line Numbers
  1. SET ECHO OFF
  2. SET VERIFY OFF
  3. SET SERVEROUTPUT ON
  4. PROMPT *******************************************************
  5. PROMPT || Enter "COMMIT" now if you want to commit 
  6. PROMPT || your changes. Do NOT type CTL-C
  7. PROMPT ||
  8. PROMPT || Typing anything else and pressing the ENTER PROMPT || key will cause your changes to be rolled-back.
  9. ACCEPT confirm_commit PROMPT '|| Enter your response: ' 
  10. SET ECHO ON
  11. EXEC confirm_commit( '&confirm_commit' );
  12. SPOOL OFF
--
Oct 6 '10 #9
amitpatel66
2,367 Expert 2GB
Try something like this:

Expand|Select|Wrap|Line Numbers
  1. SET SERVEROUTPUT ON
  2. UPDATE emp set empid = 1;
  3. ACCEPT c_flag PROMPT 'Commit (Y/N)?:';
  4. DECLARE
  5. my_flag VARCHAr2(100) := NULL;
  6. BEGIN
  7. my_flag := &&c_flag;
  8. IF my_flag = 'Y' THEN
  9. COMMIT;
  10. ELSE
  11. ROLLBACK
  12. END IF;
  13. END;
  14. undefine c_flag
  15.  
Place the above code in .sql file and execute the same. Immediately after update statement runs, program will ask user to either commit or rollback and depending on the input passed the program would either commit or rollback.
Oct 7 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Martin Staael | last post: by
5 posts views Thread by Paul M | last post: by
11 posts views Thread by Gregor Kovač | last post: by
10 posts views Thread by Luigi | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.