12 Replies Latest reply: Mar 6, 2013 2:38 PM by madamovi RSS

    Is there an "if" statement for SQLplus scripts?

    user559463
      Is there something like an "if" statement which I can use in SQLplus scripts?

      I want e.g. to check if the value of a certain variable is equal to "123".
      If yes an ALTER TABLE command should be executed otherwise a DROP TABLE command.

      Peter
        • 1. Re: Is there an "if" statement for SQLplus scripts?
          Arun Kumar Gupta
          You can not use IF in SQL statement but you can do this in PLSQL using EXECUTE IMMEDIATE (Dynamic SQL).


          Regards
          Arun
          • 2. Re: Is there an "if" statement for SQLplus scripts?
            659537
            Am not clear for the requirement..

            you want a single sql statement which should behave like if - else, and based on condition drop the table or alter table...

            is this your requirement ?

            Edited by: Kapil Aradhye on 17-Feb-2010 01:49
            • 3. Re: Is there an "if" statement for SQLplus scripts?
              561093
              You can use CASE and DECODE function in SQL queries like this:
              SQL> select * from t1;
              
                      ID
              ----------
                     123
                     222
              
              SQL> select decode(id, 123, 999, id) from t1;
              
              DECODE(ID,123,999,ID)
              ---------------------
                                999
                                222
              
              SQL> select case when id=123 then 999 else id end from t1;
              
              CASEWHENID=123THEN999ELSEIDEND
              ------------------------------
                                         999
                                         222
              
              SQL>
              However, you cannot perform DDL operation in SQL queries. You may achieve the same using PL/SQL using EXECUTE IMMEDIATE.

              Asif Momen
              http://momendba.blogspot.com
              • 4. Re: Is there an "if" statement for SQLplus scripts?
                user559463
                No. I meant an "if" for something like this (in an SQLplus script):


                ....
                accept mychoice prompt Enter operation you would like to perform=

                if mychoice==123 then (
                ALTER TABLE ....
                ) else (
                DROP TABLE ....)
                ....

                Edited by: user559463 on Feb 17, 2010 2:12 AM
                • 5. Re: Is there an "if" statement for SQLplus scripts?
                  561093
                  Its simple like this:
                  SQL> create or replace procedure my_test_proc(p_val in number) is
                    2  begin
                    3    if p_val = 1 then
                    4      dbms_output.put_line('Input Value is One');
                    5    elsif p_val = 2 then
                    6      dbms_output.put_line('Input Value is Two');
                    7    else
                    8      dbms_output.put_line('Input Value is other than 1 and 2 --> ' || p_val);
                    9    end if;
                   10  end;
                   11  /
                  
                  Procedure created.
                  
                  SQL> set serveroutput on
                  SQL>
                  SQL>
                  SQL> exec my_test_proc(1);
                  Input Value is One
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL>
                  SQL>
                  SQL> exec my_test_proc(2);
                  Input Value is Two
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL>
                  SQL>
                  SQL>
                  SQL> exec my_test_proc(123);
                  Input Value is other than 1 and 2 --> 123
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL>
                  SQL>
                  SQL>
                  Under the IF conditions use EXECUTE IMMEDIATE command to perform DDL actions.


                  Asif Momen
                  http://momendba.blogspot.com
                  • 6. Re: Is there an "if" statement for SQLplus scripts?
                    Billy~Verreynne
                    user559463 wrote:
                    Is there something like an "if" statement which I can use in SQLplus scripts?
                    Simple answer. No.

                    Complex answer. Yes, it can be emulated (a tad of a dirty hack though).

                    Your typical approach would have been something like this:
                    IF <condition> THEN
                       @<script1>
                    ELSE
                      @<script2>
                    END-IF
                    In SQL*Plus you need to test the condition using SQL - as it does not have the means to test the condition itself. The SQL response need to be the name of the SQL script to execute. This is placed into a substitution variable in SQL*Plus - and this variable is then executed as the script. So it looks something as follows:
                    SQL> set define on
                    SQL> set verify off
                    SQL>
                    SQL> -- we first bind a substitution variable to the output of the SQL statement
                    SQL> col SCRIPT_COL new_val SCRIPT
                    SQL>
                    SQL> -- use SQL to evaluate the condition and return the name of the script
                    SQL> select
                      2          case
                      3                  when 1=1 then 'script1.sql'
                      4          else
                      5                  'script2.sql'
                      6          end                     as SCRIPT_COL
                      7  from       dual;
                    
                    SCRIPT_COL
                    ---------------------------------
                    script1.sql
                    
                    SQL>
                    SQL> -- we now have substitution variable SCRIPT containing the name of the
                    SQL> -- script to execute
                    SQL> prompt Result is &SCRIPT
                    Result is script1.sql
                    SQL>
                    SQL> -- so let's execute it
                    SQL> @ &SCRIPT
                    SQL> prompt
                    
                    SQL> prompt ==[ SCRIPT1.SQL ]===
                    ==[ SCRIPT1.SQL ]===
                    SQL> prompt Hello from script1. Condition is TRUE.
                    Hello from script1. Condition is TRUE.
                    SQL> prompt ...code....
                    ...code....
                    SQL> prompt ==[ SCRIPT1.SQL ]===
                    ==[ SCRIPT1.SQL ]===
                    • 7. Re: Is there an "if" statement for SQLplus scripts?
                      user559463
                      This all is NOT working! Given the following SQLplus script code:

                      accept myvalue prompt 'Enter value=' Default 1
                      if myvalue = 1 then
                      prompt Value is one
                      else
                      prompt value is not one
                      end-if

                      the result is as follows:

                      Enter value=2
                      SP2-0734: unknown command beginning "if myvalue..." - rest of line ignored.
                      Value is one
                      SP2-0042: unknown command "else" - rest of line ignored.
                      value is not one
                      SP2-0042: unknown command "end-if" - rest of line ignored.

                      So any other suggestion?

                      Peter
                      • 8. Re: Is there an "if" statement for SQLplus scripts?
                        Billy~Verreynne
                        user559463 wrote:
                        This all is NOT working!
                        It is. Read my posting again.

                        Do not confuse the template of how an IF structure looks like logically, with how it needs to be implemented physically in SQL*Plus.

                        Perhaps have some more coffee first, Peter?
                        • 9. Re: Is there an "if" statement for SQLplus scripts?
                          fosterk
                          Is there a way to have the sql echo a message and issue an exit if a case statement is met or not?
                          • 10. Re: Is there an "if" statement for SQLplus scripts?
                            950305
                            Now THAT is a very useful technique. I wouldn't have come up with that on my own. Thanks!
                            • 11. Re: Is there an "if" statement for SQLplus scripts?
                              EdStevens
                              947302 wrote:
                              Now THAT is a very useful technique. I wouldn't have come up with that on my own. Thanks!
                              Well, he did say it was " (a tad of a dirty hack though)." Meaning (correct me if I mis-read you, Billy) "yes, it can be done, it was an interesting challenge to work it out, but that in no way means it is a good idea."

                              Just because something can be done doesn't mean it should be done. The correct solution is to use PL/SQL, which of course can be executed by SQLPlus but is not to be confused with SQL*Plus itself and it's own command set.
                              • 12. Re: Is there an "if" statement for SQLplus scripts?
                                madamovi
                                Billy  Verreynne  wrote:

                                Simple answer. No.

                                Complex answer. Yes, it can be emulated (a tad of a dirty hack though).

                                In SQL*Plus you need to test the condition using SQL - as it does not have the means to test the condition itself. The SQL response need to be the name of the SQL script to execute. This is placed into a substitution variable in SQL*Plus - and this variable is then executed as the script. So it looks something as follows:
                                SQL> set define on
                                SQL> set verify off
                                SQL>
                                SQL> -- we first bind a substitution variable to the output of the SQL statement
                                SQL> col SCRIPT_COL new_val SCRIPT
                                SQL>
                                SQL> -- use SQL to evaluate the condition and return the name of the script
                                SQL> select
                                2          case
                                3                  when 1=1 then 'script1.sql'
                                4          else
                                5                  'script2.sql'
                                6          end                     as SCRIPT_COL
                                7  from       dual;
                                
                                SCRIPT_COL
                                ---------------------------------
                                script1.sql
                                
                                SQL>
                                SQL> -- we now have substitution variable SCRIPT containing the name of the
                                SQL> -- script to execute
                                SQL> prompt Result is &SCRIPT
                                Result is script1.sql
                                SQL>
                                SQL> -- so let's execute it
                                SQL> @ &SCRIPT
                                SQL> prompt
                                
                                SQL> prompt ==[ SCRIPT1.SQL ]===
                                ==[ SCRIPT1.SQL ]===
                                SQL> prompt Hello from script1. Condition is TRUE.
                                Hello from script1. Condition is TRUE.
                                SQL> prompt ...code....
                                ...code....
                                SQL> prompt ==[ SCRIPT1.SQL ]===
                                ==[ SCRIPT1.SQL ]===
                                Thanks for this solution.

                                I would like to conditionally display second ACCEPT prompt for a substitution variable based on the value of the first one. Do you have any "creative" solution for that problem? I would prefer not to spool into new script, then run generated script.