Start a new topic

Errors trying to run pl/sql script

anonymous

started a topic almost 12 years ago

[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

2 Comments

Roger Bjärevall

said almost 12 years ago

[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

anonymous

said almost 12 years ago

[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

How can we help you today?

Errors trying to run pl/sql script

anonymous

Roger Bjärevall

anonymous