sqlplus

Description

Executes SQL*Plus Scripts by using Oracle's sqlplus command-line query tool.

SQL*Plus statements can either be read from a source file using the start attribute or from between the enclosing <sqlplus> tags (preferably within a CDATA section)

Attributes

Attribute Description Required
logonConnection string.

syntax: {username[/password] [@connect_identifier] | /} [AS {SYSOPER | SYSDBA}]| /NOLOG 		No. Defaults to '/nolog'
dirThe directory in which the command should be executed. No. Defaults to projects basedir
silenttrue or false - Suppresses all SQL*Plus information and prompt messages No. Defaults to 'false'
startName of a script and arguments to execute syntax: @{url|file_name[.ext]} [arg ...] No.
failOnErrortrue or false - Indicates whether the build will continue if the command exits with an error code No. Defaults to 'false'
resultpropertyName of a property in which the return code of the command should be stored. Only of interest if failonerror=false. No.
failOnSpoolMatchA regular expression pattern that will be used to find matches within the SQL*Plus output stream. If a match was found, the task fails with an exception. For instance one can scan the output stream for SQL*Plus SP2 errors and issue an build exception (see example section). No.

Nested elements

<arg>

The <sqlplus> task supports nested <arg> elements that will be passed as start parameters to the sql script. Optionally start parameters can be included directly within the start attribute of the sqlplus task.

Attribute Description Required
valueParameter value Yes

<ora:sqlplus start="${sql.dir}/pass-parameters.sql"
   <arg value="scotch"/>                
   <arg value="whiskey"/>                
</ora:sqlplus>
has the same effect as writing

<ora:sqlplus start="${sql.dir}/pass-parameters.sql scotch whiskey"/>
with a SQL script: 
prompt var1 = &1
prompt var2 = &2
it produces an output: 
[sqlplus] SQL*Plus: Release 10.1.0.3.0
[sqlplus] Copyright (c) 1982, 2004, Oracle.  All rights reserved.
[sqlplus] var1 = scotch
[sqlplus] var2 = whiskey

<propertyset>

Corresponds to Ant's nested <propertyset> element. A propertyset can be used to pass properties from Ant to SQL*Plus. So it's possible to use Ant properties directly inside SQL scripts as a defined variable.

Note: SQL*Plus has rigid variable naming conventions. E.g. dots are not allowed within variable names. Therefore the <sqlplus> task automatically replaces all dot characters by underlines.

For examples, assume a property named db.local (referenced as variable db_local inside SQL) 

<ora:sqlplus>
    <propertyset>
        <propertyref prefix="db"/>    
    </propertyset>
    <![CDATA[
        prompt &&db_local
    ]]>
</ora:sqlplus>

This is working within scripts inside the sqlplus tag, as well as within external script files.

Examples

Shutdown an Oracle instance. If the instance is not available, set the property 'shutdown.exitvalue' to the value of the SQL error code. 

<ora:sqlplus 
   resultproperty="shutdown.exitvalue"
   logon="sys/syspswd@localdb as sysdba"
   silent="true"
   failonerror="false">
     <![CDATA[
      WHENEVER SQLERROR EXIT SQL.SQLCODE
      select status from v$instance;
      shutdown immediate
     ]]>
</ora:sqlplus>
Let the build abort if ARCHIVELOG mode is disabled for that instance. 
<ora:sqlplus 
   logon="sys/syspswd@localdb as sysdba"
   silent="true"
   failonerror="true">
     <![CDATA[
      WHENEVER SQLERROR EXIT SQL.SQLCODE
      declare
        v_logmode varchar2(30);
      begin	
        select log_mode into v_logmode from v$database;
        if v_logmode != 'ARCHIVELOG' then
          raise_application_error (-20101, 'ARCHIVELOG not enabled');
        end if; 
      end;
      /
     ]]>
</ora:sqlplus>
Note that after PL/SQL blocks a forward slash (/) is required to tell SQL*Plus that the PL/SQL block is complete and should be transmitted to the database. 

<ora:sqlplus logon="${db.userid}" silent="true" 
   failOnSpoolMatch=".*SP2-[0-9]{4}.*">
    <![CDATA[
    	xyz
    ]]>
</ora:sqlplus>
This sample tasks scans the SQL*Plus output for SP2 errors and issue a build exception.