4 Replies Latest reply: Nov 21, 2007 8:48 AM by 608591 RSS

    SQLPlus  command line parameters

      I would like to pass parameters to a sqlscript with the &&
      so that one variable is defined for the entire script
      sqlplus scott/tiger @script foo
      I tried everything and read and still it prompts me for the foo
      variable... sometimes it does work ( set verify off ) and just
      gives me the results... in others how do I pass variables
      to a sql script through sqlplus without having sqlplus prompt
      me for what the variable is, especially since I pass it.

        • 1. Re: SQLPlus  command line parameters
          --  ==== x.sql ===============
          def PARAM1=&1
          def PARAM2=&2
          select * from tab where  tname = '&PARAM1' and cname='&PARAM2' ;
          -- ========================

          sqlplus scott/tiger @x.sql  EMP DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                       
          • 2. Re: SQLPlus  command line parameters
            Just thought I would add this in

            You can bypass the prompts for values associated with substitution variables by passing values to parameters in a script through the START command.
            You do this by placing an ampersand (&) followed by a numeral in the script in place of a substitution variable. Each time you run this script, START replaces each &1 in the file with the first value (called an argument) after START filename, then replaces each &2 with the second value, and so forth.

            For example, you could include the following commands in a script called MYFILE:

            WHERE JOB_ID='&1'
            AND SALARY='&2';

            In the following START command, SQL*Plus would substitute PU_CLERK for &1 and 3100 for &2 in the script MYFILE:

            START MYFILE PU_CLERK 3100

            Is it possible to do this from the command line with sqlplus

            sqlplus scott/tiger @myfile PU_CLERK 3100

            the reason why I want to do this is I would like to cursor through
            a list of tables in a schema and dynamically generate sql loader control files.
            • 3. Re: SQLPlus  command line parameters
              WHERE JOB_ID='&1'
              AND SALARY='&2';

              Ugly. Reason: does not use bind variables.

              Not using bind variables is said to be the #1 reason for poor performing Oracle applications.

              Granted, using bind variables in SQL*Plus from input is difficult as the assignment statement itself needs to be executed by using an anonymous Oracle PL/SQL block - and that will cause duplicate assignment statements in the Shared Pool as these are non-sharable.

              SQL> -- define a bind variable
              SQL> var salValue number
              SQL> -- assign the input value (this results in a non-sharable SQL)
              SQL> exec :salValue = &1;
              SQL> -- execute the SQL using bind variables, resulting in a sharable SQL
              SQL> select * from emp_details where salary = :salValue;

              Even so, this approach shows an understanding and appreciation of using bind variables and firing off sharable SQL at Oracle. And in most other languages, the assignment statement can be done by the local language itself, instead of having to use PL/SQL as SQL*Plus has to - which means this approach will result in proper sharable SQL.

              The same technique should be followed in SQL*Plus - as this is the correct technique in other languages. Especially since SQL*Plus is often used as testing ground before implementing code in other languages.
              • 4. Re: SQLPlus  command line parameters
                -- ==== x.sql ===============def PARAM1=&1def PARAM2=&2select * from tab where tname = '&PARAM1' or cname='&PARAM2' ;-- ========================sqlplus scott/tiger @x.sql EMP DEPT

                worked thankyou very much I knew that this was possible but
                forgot how to do and looked everywhere... the books went on and
                on about prompting for variables but nothing about how to pass
                command line arguments silently... thanks again!