Oracle SQL
  • LICENSE

SQL*Plus tips #4: Branching execution

Posted on April 17, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips

Today I’ll show a trick how we can use branching execution of SQLPlus scripts in SQL*Plus.
Although I previously showed the conditional execution of scripts and it really can be used for branching, but today I’ll show how to do it without splitting the script into several smaller scripts. In contrast to the conditional script execution, I’ll use a different method.

It is very simple, as usual – if you want to execute only one part of script, you can just comment out all unnecessary. So depending on the conditions, we can execute a script which will start a comment.

Suppose we need to create a script, which, depending on the input parameter will be required to execute a specific query.
See how this can be done:
1. “test.sql”:

def param = &1

@if &param=1
   select 1 from dual;
/* end_if */

@if &param=2
   select 2 from dual;
/* end_if */

@if &param=3
   select 3 from dual;
/* end_if */

2. “if.sql”:

col do_next new_val do_next noprint;
select 
      case 
         when &1 then 'inc/null'
         else 'inc/comment_on'
      end as do_next
from dual;
@&do_next

3. “inc/comment_on.sql” contains only 2 chars:

/*

4. “inc/null.sql” is the same as in the previous examples – just empty file.

Ok, lets test it:

SQL> @test 1

         1
----------
         1


SQL> @test 2


         2
----------
         2


SQL> @test 3


         3
----------
         3

As you see, we got what we wanted. Please note that we have to close the multiline comments in the right places only(/* end_if */). So we cannot use in these parts another “*/”. But you can use it in another child scripts.

Same way we can make an analogue of switch/case:
“test2.sql”:

@switch &1

   @when 1 then
      select 1 from dual;
   /* end when */

   @when 2 then
      select 2 from dual;
   /* end when */

   @when 3 then
      select 3 from dual;
   /* end when */

/* end switch */
switch.sql
def switch_param=&1

[collapse]

when.sql

col do_next new_val do_next noprint;
select 
      case 
         when &1 = &switch_param then 'inc/null'
         else 'inc/comment_on'
      end as do_next
from dual;
@&do_next

[collapse]

Example:

SQL> @test2 2

         2
----------
         2

SQL> @test2 3

         3
----------
         3
SQL*Plus
« SQL*Plus tips #3: Iterators and recursions
SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL