Conditional Execution

[Tinti]

Hello

I've following Code:

@C:\TEMP\RUN_PGM1.sql
@C:\TEMP\RUN_PGM2.sql

SET LINESIZE 0
SET COLWIDTH 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET TERMOUT OFF
SET ECHO OFF

SPOOL C:\TEMP\OUTPUT.txt

PROMPT TITEL

SELECT *
FROM ADH_ABO_7076_RM_WERTE_T
;

SPOOL OFF

/

@C:\TEMP\RUN_PGM3.sql
@C:\TEMP\RUN_PGM4.sql

If the query "Select * ..." find at least one record then should start RUN_PGM3.sql.

But if the query crash or there are no rows in the table should start RUN_PGM4.sql

How do I have to handle this?

Thanks a lot for every help
Joerg Braendli

 

[Marco Kalter]

I'm afraid there are no commands for this. This is only possible in PL/SQL, so maybe you can convert the scripts to PL/SQL.

 

[Tinti]

Is it possible to start a script (e.g. @C:\TEMP\RUN_PGM3.sql) directly from pl/sql

begin
 ..
 ..
  start @C:\TEMP\RUN_PGM3.sql  ?????
 ..
 ..
end;

Or is it possible to spool the DBMS_OUTPUT to a file (I don't get access to UTL_FILE)

 

[CTzen]

Tinti,

no, it's not possible to call SQL*Plus commands from PL/SQL, but you can spool DBMS_OUTPUT to a file.

cl scr
set serveroutput on size 999999
spool test.txt

begin
    for r in (select object_name obj from all_objects)
    loop
        dbms_output.put_line(r.obj);
    end loop;
end;
/
spool off

 

[Tinti]

Hi

I've tried it with this already. That's the way I will do it. But one last question. The file begins with a blank line. Is there a way to write the output without the first blank line?

Thanks and have a nice weekend
Joerg

 

[CTzen]

Joerg ,

I don't think you can get rid of it, but here is how to make conditional SQL*Plus script.

Idea is you check logic in PL/SQL code, then 'write' another script to execute after PL/SQL block via dbms_output.put_line.

Here is sample script

cl scr
set serveroutput on size 999999
set feedback off

spool c:\next_script.sql

declare
    l_result  number;
begin
    -- Just getting random number to simulate logic
    select  trunc(dbms_random.value * 2) + 1
      into  l_result
      from  dual;

    if l_result = 1
    then
        dbms_output.put_line('@c:\script1.sql');
    else
        dbms_output.put_line('@c:\script2.sql');
    end if;
end;
/

spool off

@c:\next_script.sql

And here is sample output

Started spooling to c:\next_script.sql

@c:\script2.sql

Executed in 0.063 seconds
Stopped spooling to c:\next_script.sql
This is script 2