3 Replies Latest reply: Jul 22, 2011 1:19 PM by user130038 RSS

    How to execute a sql script file in toad

    A.T
      Hi friends

      I have a sql script file (sample.sql) file in which it has various sql command like this
      CREATE TABLE XXX
        AS (SELECT * FROM XYZ where 1=2);
      
      commit ;
      
      ALTER TABLE XXX ADD (
        CONSTRAINT FK_xxx 
       FOREIGN KEY (id) 
       REFERENCES  XYZ (id));
      
      commit;
      
      ALTER TABLE  XXX
      ADD Column_name  datatype
      
      commit;
      The task to create a table structure ,add constraint , add new column to the table ... All this is one script file sample.sql how can i execute and check in toad please suggest me

      Thanks
      AT

      Edited by: AT on Jul 22, 2011 8:31 AM
        • 1. Re: How to execute a sql script file in toad
          omaha66
          Of course - usual answer that this isn't an Oracle product etc.

          Press the yellow lightning bolt button (execute as script) and it will run your script. Results will be shown in bottom window (Messages).

          FYI - you don't need the "commit" statements, DDL statements don't need to be committed (they commit themselves).

          And, no / before the closing "code" word.
          • 2. Re: How to execute a sql script file in toad
            A.T
            Hi Friend

            Thanks for your suggestion , the script is in notepad and saved as sample.sql how to execute that in toad ?
            Press the yellow lightning bolt button (execute as script) and it will run your script// Can u please explain little bit i am not that much aware of toad .....
            I can run the sql script one by one in the toad but the requirement is not like that they need the sql script that should perform all the action please let me know how it can be done ....and please if u have any sample script means provide me so that i can modify the script according to my requirement ?

            Thanks In advance
            AT
            • 3. Re: How to execute a sql script file in toad
              user130038
              After you have run TOAD and connected to Oracle DB, TOAD will open a "SQL Editor" window. If it does not, you can open it by clicking the "SQL" or "Editor" button in the tool bar.

              You can open the script in TOAD's SQL Editor Window like this:

              Press "Open File" button (yellow file icon) from the tool bar [ or from File menu, click "Open File" option]
              Navigate to directory where you script is residing
              select the script file and press "Open" button
              Now you can press the yellow 'bolt' button to execute your script.

              Once you have run the script successfully, click on the "Schema Browser" button [ which is next to the "Editor" button ]. In the schema browser's "Tables" tab, you can look for the table you have created.


              Alternately, you can also use SQLPlus to run your script either after launching SQLPlus and connecting to DB or from the command line.

              >
              i am not that much aware of toad .....
              >
              To get familiar with the tool, you'll have to explore it yourself if you plan to continue using it.

              NOTE
              You do not need "commit" after DDL statements. I noticed "commit" statements in your script. You can remove them.


              HTH