2 Replies Latest reply: Aug 9, 2010 8:33 AM by SJM RSS

    Running sql with SET DEFINE OFF

    SJM
      We are receiving sql files (insert statements) from a third party, via an automatic process. I want to load these files using sql*plus but also want to SET DEFINE OFF to prevent variable substitution. We can't alter the sql files in any way. I'm using:

      sqlplus user/pass@db @file.sql

      Is there anyway I can set the parameter in the command line? I don't particularly want to use glogin.sql as other scripts I run will require DEFINE to be on. Thanks.
        • 1. Re: Running sql with SET DEFINE OFF
          Frank Kulash
          Hi,
          SJM wrote:
          We are receiving sql files (insert statements) from a third party, via an automatic process. I want to load these files using sql*plus but also want to SET DEFINE OFF to prevent variable substitution. We can't alter the sql files in any way. I'm using:

          sqlplus user/pass@db @file.sql

          Is there anyway I can set the parameter in the command line? I don't particularly want to use glogin.sql as other scripts I run will require DEFINE to be on. Thanks.
          Can you add the "SET DEFINE OFF" command to the beginning of file.sql?

          If you can't change file.sql, then create a script called define_off_and_run.sql, like this:
          --  define_off_and_run.sql - turns off substitution variables, then runs &1.sql
          
          SET  DEFINE  OFF
          
          @&1
          Then invoke sqlplus like this:
          sqlplus  usernm/pass@db   @define_off_and_run file.sql
          If you can't do any of the above, maybe you could use login.sql instead of glogin.sql.

          Should this user always have SET DEFINE OFF? (E.g., is this user used only for this upload process, or can you create a user to be used only for this upload process?)
          If so, you can make test to see who the user is in glogin.sql (or login.sql) and SET DEFINE OFF only if it's the special user.
          • 2. Re: Running sql with SET DEFINE OFF
            SJM
            Thanks. I just created another sql file with:

            set define off
            @file.sql

            and then ran that.