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

[edwardscm]

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

[Dave44]

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.

[159]SQL> ed
Wrote file afiedt.buf
 
  1* select * from dual where dummy = &var
[159]SQL> /
Enter value for var: 'A'
old   1: select * from dual where dummy = &var
new   1: select * from dual where dummy = 'A'
 
no rows selected
 

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.

[edwardscm]

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):-

 update sol set SOL_EOD_STATUS = 'N', DB_STAT_CODE = ‘C’;
 

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:-

  update sol set SOL_EOD_STATUS = 'N', DB_STAT_CODE = ‘C’;
  accept response prompt 'Commit Transacton y/n: '
  if &response = 'y'
  then
     commit;
     exit
  else
     rollback;
     exit;
  endif
 
 

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]

 
SET SERVEROUTPUT ON
set feedback on
 
DECLARE
 
i INTEGER;
 
BEGIN
SELECT COUNT(*)
INTO i
FROM all_tables;
 
IF i > 100 THEN
DBMS_OUTPUT.PUT_LINE('There Are More Than 100 Tables');
ELSE
DBMS_OUTPUT.PUT_LINE('There Are Less Than 100 Tables');
END IF;
END;
/
 

[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 ]

 
SET SERVEROUTPUT ON
set feedback on
 
DECLARE
 
i INTEGER;
 
BEGIN
 
update sol set SOL_EOD_STATUS = 'N', DB_STAT_CODE = ‘C’;
 
accept res prompt 'Enter Response: '
IF &res = 'y' THEN
   commit;
ELSE
   rollback;
END IF;
END;
/
 

[ 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.

[Dave44]

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:

[143]dave@MYORADB> begin
  2  
  3     dbms_output.put_line('You must enter ''Y'' to commit or ''N'' to rollback');
  4     
  5     if upper(&res) = 'Y' then
  6        dbms_output.put_line('Its Y');
  7     else 
  8        dbms_output.put_line('Its not Y');
  9     end if;
 10  end;
 11  /
Enter value for res: 'N'
old   5:    if upper(&res) = 'Y' then
new   5:    if upper('N') = 'Y' then
You must enter 'Y' to commit or 'N' to rollback
Its not Y
 
PL/SQL procedure successfully completed.
 

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

[vikas000000a]

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.

[edwardscm]

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.

[Dave44]

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

[edwardscm]

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.

DECLARE
   res varchar(1);
BEGIN

-- the on-call programmers code goes here which the operators paste

   delete from save_changes;
   insert into save_changes values ('&y_or_n');
   SELECT response INTO res FROM save_changes;
   IF res = 'y' or res = 'Y'
   then
      commit;
   ELSE
      rollback;
   END IF;
END;
/
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?

[MWRA ENQUAD]

If a naughty operator types CTL-C then a COMMIT is issued as the session disconnects.

CREATE OR REPLACE
PROCEDURE confirm_commit( p_response IN VARCHAR2 )
IS
   l_response VARCHAR2( 255 );
BEGIN
   l_response := TRIM( UPPER( p_response ) );
   DBMS_OUTPUT.PUT_LINE
      ( 'You answered: ' || p_response );
 
   IF l_response IN ( 'COMMIT', 'COMMIT;' ) 
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE( 'Commit completed.' );
   ELSE
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE( 'Rollback completed.' );
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE( 'Rollback completed.' );
END confirm_commit;
 

-- Add the following to the end of each
-- operator script or script template:

--

SET ECHO OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
PROMPT *******************************************************
PROMPT || Enter "COMMIT" now if you want to commit 
PROMPT || your changes. Do NOT type CTL-C
PROMPT ||
PROMPT || Typing anything else and pressing the ENTER PROMPT || key will cause your changes to be rolled-back.
ACCEPT confirm_commit PROMPT '|| Enter your response: ' 
SET ECHO ON
EXEC confirm_commit( '&confirm_commit' );
SPOOL OFF

--

[amitpatel66]

Try something like this:

SET SERVEROUTPUT ON
UPDATE emp set empid = 1;
ACCEPT c_flag PROMPT 'Commit (Y/N)?:';
DECLARE
my_flag VARCHAr2(100) := NULL;
BEGIN
my_flag := &&c_flag;
IF my_flag = 'Y' THEN
COMMIT;
ELSE
ROLLBACK
END IF;
END;
undefine c_flag
 

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.