Execute sql file in Exception statement

oracleoracle-11g-r2plsql

Hi I'm trying to execute an SQL File in a Exception Statement when I'm executing an Spool file.

I have something like this:

column dt new_value _dt 
select to_char(sysdate,'yyyymmdd_hh24mi') dt from dual; 
set line 10000;
set pagesize 50000;
set serveroutput on;

spool .\backup\backup.sql

select dbms_metadata.get_ddl(object_type, object_name)
from user_objects
where object_type in ('FUNCTION')
and object_name = 'TEST_TABLE';

Spool Off

spool .\!Run_&_dt..txt

BEGIN
    raise_application_error( -20001, 'This is a custom error' );
EXCEPTION 
    WHEN OTHERS THEN
        Prompt ./backup/backup.sql
        @ ./backup/backup.sql
END;

Spool Off

And I get the next error:

>> BEGIN
     raise_application_error( -20001, 'This is a custom error' );
EXCEPTION 
    WHEN OTHERS THEN
        Prompt ./backup/backup.sql
        @ ./backup/backup.sql
END;


Spool Off
Error at line 18
ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol "/" when expecting one of the following:

    <an identifier> <a double-quoted delimited-identifier>
    current delete exists prior
ORA-06550: line 6, column 49:
PLS-00103: Encountered the symbol "SQL" when expecting one of the following:

    <an identifier> <a double-quoted delimited-identifier>
    current delete exists prior

I don't have any idea, thanks in advance.

Best regards.

Best Answer

So it's a bit confusing - you're writing a SQL*Plus script, which accepts both SQL*Plus commands as well as SQL commands and PL/SQL blocks.

SQL*Plus commands are executed locally on your client machine, so they have access to other scripts on your local filesystem.

Everything inside a BEGIN..END block is PL/SQL code, and it'll behave differently than the rest of your script because it's a different language. The entire block of code is sent to the server and executed there, and the database doesn't know how to interpret sqlplus commands like prompt and @.

There are ways to have a PL/SQL block execute scripts on the database server - see this answer for more details - but I don't think that's really what you want.

It's hard to tell what you want from your example, but I'd suggest looking into handling sqlplus errors using a shell script - split this into two sqlplus scripts, then run the first script, check the return code, and if you got an error, run your second script. Sqlplus itself doesn't really have much in the way of built-in exception handling.

Related Question