Start a new topic

Errors trying to run pl/sql script

[This topic is migrated from our old forums. The original author name has been removed]

I'm having no problems running highlighted sql but when I try to run a script, I get plenty of errors. Below is the script and below that are the errors. SET ECHO ON; set serveroutput on; set feedback on; set define off; set numformat 999999999999; set colsep ','; alter session set current_schema = LIFESMART; spool testv.log; DECLARE v_rfs_id VARCHAR(16) := 'testv'; v_expected_rows PLS_INTEGER := 0; lst_dml_exception EXCEPTION; PROCEDURE printELAHeader IS dbName v$database.name%TYPE; BEGIN dbms_output.enable(buffer_size => NULL); select name into dbName from v$database; dbms_output.put_line('-- START ' || v_rfs_id || ' fix on ' || dbName || ' at ' || SYSTIMESTAMP || ' by user ' || USER); dbms_output.put_line('-- Modifying data'); END printELAHeader; PROCEDURE printELAFooter IS dbName v$database.name%TYPE; BEGIN select name into dbName from v$database; dbms_output.put_line('-- Done Modifying data'); dbms_output.put_line('-- END ' || v_rfs_id || ' fix on ' || dbName || ' at ' || SYSTIMESTAMP || ' by user ' || USER); END printELAFooter; PROCEDURE checkExpectedRows(p_expected_rows NUMBER) IS v_rows PLS_INTEGER := 0; BEGIN v_rows := SQL%ROWCOUNT; if ( v_rows != p_expected_rows ) then dbms_output.put_line('-- ' || v_rows || ' ROWS MODIFIED. Expected ' || p_expected_rows || '. ROLLBACK'); RAISE lst_dml_exception; else dbms_output.put_line('-- ' || v_rows || ' ROWS MODIFIED as expected. Mark for COMMIT'); end if; END checkExpectedRows; BEGIN printELAHeader; dbms_output.put_line('-- No exceptions. COMMIT data.'); --commit; printELAFooter; EXCEPTION WHEN lst_dml_exception THEN dbms_output.put_line('-- ROLLBACK on Bad Data Modification'); rollback; WHEN OTHERS THEN dbms_output.put_line('-- ROLLBACK on Exception: ' || SQLCODE || '-' || SQLERRM); rollback; END; spool off; / ERRORS: 15:14:53 [SET - 0 row(s), 0.000 secs] [Error Code: 922, SQL State: 42000] ORA-00922: missing or invalid option 15:14:53 [SET - 0 row(s), 0.000 secs] [Error Code: 922, SQL State: 42000] ORA-00922: missing or invalid option 15:14:53 [SET - 0 row(s), 0.000 secs] [Error Code: 922, SQL State: 42000] ORA-00922: missing or invalid option 15:14:53 [SET - 0 row(s), 0.000 secs] [Error Code: 922, SQL State: 42000] ORA-00922: missing or invalid option 15:14:53 [SET - 0 row(s), 0.000 secs] [Error Code: 922, SQL State: 42000] ORA-00922: missing or invalid option 15:14:53 [SET - 0 row(s), 0.000 secs] [Error Code: 922, SQL State: 42000] ORA-00922: missing or invalid option 15:14:53 [ALTER - 0 row(s), 0.031 secs] Command processed. No rows were affected 15:14:53 [SPOOL - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:53 [DECLARE - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 2, column 35: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: * & = - + ; at in is mod remainder not rem or != or ~= >= and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ 15:14:54 [V_EXPECTED_ROWS - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [LST_DML_EXCEPTION - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [PROCEDURE - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [BEGIN - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 2, column 47: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := . ( % ; 15:14:54 [SELECT - 0 row(s), 0.000 secs] [Error Code: 905, SQL State: 42000] ORA-00905: missing keyword 15:14:54 [DBMS_OUTPUT.PUT_LINE('-- - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [DBMS_OUTPUT.PUT_LINE('-- - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [END - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [PROCEDURE - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [BEGIN - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 2, column 47: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; 15:14:54 [DBMS_OUTPUT.PUT_LINE('-- - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [DBMS_OUTPUT.PUT_LINE('-- - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [END - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [PROCEDURE - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [BEGIN - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 2, column 30: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: . ( * @ % & = - + ; at in is mod remainder not rem or != or ~= >= and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ 15:14:54 [IF - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:54 [RAISE - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [ELSE - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [END - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [END - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [BEGIN - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: line 2, column 18: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := . ( @ % ; 15:14:55 [DBMS_OUTPUT.PUT_LINE('-- - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [PRINTELAFOOTER - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [EXCEPTION - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [ROLLBACK - 0 row(s), 0.032 secs] Command processed. No rows were affected 15:14:55 [WHEN - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [ROLLBACK - 0 row(s), 0.031 secs] Command processed. No rows were affected 15:14:55 [END - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [SPOOL - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement 15:14:55 [/ - 0 row(s), 0.000 secs] [Error Code: 17439, SQL State: 99999] Invalid SQL type ... 39 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.094/0.000 sec [0 successful, 3 warnings, 36 errors] I have tried all manner of running the script with and without delimiters. They are set to default as --/ and / Needless to say, this same script runs flawlessly in Toad, squirrel and SQL Developer. I just bought a license for DbVisualizer just for this type of script, I hope it is able to run it and I haven't wasted my money. Thanks, Mel

[This reply is migrated from our old forums.]

Re: Errors trying to run pl/sql script
Mel, First, the script you are trying to execute contain statements that are not known by the Oracle database. More specifically these: SET ECHO ON; set serveroutput on; set feedback on; set define off; set numformat 999999999999; set colsep ','; These are SQL*Plus (or other Oracle client tools) specific and are not supported by DbVisualizer. To execute the SQL part of the script you will need to instruct DbVisualizer to not split the script into separate statements. Semicolon (";") is used by default to split the script. These are the options you have to run the script without splitting at semicolon: 1) Use Execute Buffer instead: http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId958847 2) Place the script within SQL Block delimiters (--/ and /) by default): http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId652495 3) Use the @delimiter command to temporarily change the statement delimiter: http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId437790 Please see the referenced sections of the Users Guide for details. Here is a revised version of your script using option 2) above: alter session set current_schema = LIFESMART; --/ DECLARE v_rfs_id VARCHAR(16) := 'testv'; v_expected_rows PLS_INTEGER := 0; lst_dml_exception EXCEPTION; PROCEDURE printELAHeader IS dbName v$database.name%TYPE; BEGIN dbms_output.enable(buffer_size => NULL); select name into dbName from v$database; dbms_output.put_line('-- START ' || v_rfs_id || ' fix on ' || dbName || ' at ' || SYSTIMESTAMP || ' by user ' || USER); dbms_output.put_line('-- Modifying data'); END printELAHeader; PROCEDURE printELAFooter IS dbName v$database.name%TYPE; BEGIN select name into dbName from v$database; dbms_output.put_line('-- Done Modifying data'); dbms_output.put_line('-- END ' || v_rfs_id || ' fix on ' || dbName || ' at ' || SYSTIMESTAMP || ' by user ' || USER); END printELAFooter; PROCEDURE checkExpectedRows(p_expected_rows NUMBER) IS v_rows PLS_INTEGER := 0; BEGIN v_rows := SQL%ROWCOUNT; if ( v_rows != p_expected_rows ) then dbms_output.put_line('-- ' || v_rows || ' ROWS MODIFIED. Expected ' || p_expected_rows || '. ROLLBACK'); RAISE lst_dml_exception; else dbms_output.put_line('-- ' || v_rows || ' ROWS MODIFIED as expected. Mark for COMMIT'); end if; END checkExpectedRows; BEGIN printELAHeader; dbms_output.put_line('-- No exceptions. COMMIT data.'); --commit; printELAFooter; EXCEPTION WHEN lst_dml_exception THEN dbms_output.put_line('-- ROLLBACK on Bad Data Modification'); rollback; WHEN OTHERS THEN dbms_output.put_line('-- ROLLBACK on Exception: ' || SQLCODE || '-' || SQLERRM); rollback; END; / To run it first start DBMS output handler in the SQL Commander and then simply run it with the SQL->Execute main menu choice or the green execute button in the SQL Commander toolbar. The output is listed in the DBMS Output tab. I hope this helps. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Errors trying to run pl/sql script
Thanks for the reply Roger, that helped a lot. Mel