how can i pass information from ant to sql file or procedure

Question

i am creating a user by using ant target as below

<target name="create_user">
     <exec executable="sqlplus" dir="${basedir}/dbsetup">
          <arg value="system/oracle@orcl"/>
          <arg value="@create_user.sql"/>
     </exec>    
</target>

and the sql files is as follows........

create user test identified by password;

grant resource, connect to test ;

grant create view to test ;

grant create materialized view to test ;

grant create sequence to test ;

grant create procedure to test ;

grant create any directory to test ;

grant create database link to test ;

alter user test  quota unlimited on users; 

quit;

At present the user name is hard coded in create_user.sql Now i want to prompt for user name and password from ant and pass to sql file.

Please advice how can i achieve this or some other way by which i can achieve

Answer 1

What I've done for this problem is to create a template sql file that contains tokens.
The I've copied the template using ant copy with tokenizing which create a new file where the tokens have been replaced with the tokens provided.
the new copy is then executed using the ant sql task.
This can be repeated using various different token values if required.

For prompting I've used the ant input task which assigns the entered value to a variable,
the variable is then set as a token value before the copy.

Answer 2

One option is to use the &1 positional parameter in your sql file, e.g.:

create user &1 identified by password;

Prompt for the username with the ant input task and then pass that value as an arg to sqlplus in your target:

  <exec executable="sqlplus" failonerror="true">
        <arg value="${userid}/${password}@${tnsalias}"/>
        <arg value="yourFileNameGoesHere/>
        <arg value="theUserNameFromPromptGoesHere"/>
  </exec>

This has the advantage of keeping the sql file in a form that can be executed from other environments such as SQL*Plus and PL/SQL Developer