SQL Scripts : Using the SQL Scripts Maximize Efficiency

concatenate sqlSQL or Structured Query Language is a powerful and standard database computer language. It is used with all the RDBM’s to retrieve and manage data. There are three categories of commands in SQL. They are DDL(Data Definition Language), DML(Data Manipulation Language) and DCL(Data Control Language). Examples of Software that use SQL are MYSQL, Oracle, Sybase, SQL Server and more. Though SQL is very good at creating, retrieving and manipulating data, by itself, it lacks the basic programming features. PL/SQL is used to include SQL statements in data driven programs.

Today we walk you through the Graphical User Interface known as SQL Scripts, which help to manage script files. The latter contain the different SQL commands as well as PL/SQL blocks. If the script file contains SQL*Plus commands they are ignored at the time of execution. We assume that you are familiar with the different SQL commands and PL/SQL, if not you can take this course on how the essentials of SQL for PL/SQL programming.

What is a SQL script

A SQL script is a group of SQL commands saved a single file. This file can contain both SQL statements as well as PL/SQL blocks. You can learn more about SQL commands in this course.

What is SQL Scripts

The SQL Scripts is part of a Graphical User Interface from Oracle which helps to deal with all aspects of script files. SQL Scripts is an environment that enables you to create, edit, delete script files, and also execute them.

Advantages of using script files

  • No need for user action- Instead of executing a single SQL command at time from the command prompt, we can include several SQL commands in a single script file.
  • Saves time– When you run the script file, a number of SQL commands will execute sequentially. This is more convenient and saves time.

To truly appreciate the power of scripts, take this course on SQL server essentials. Lets go through each operation to understand how to use SQL Scripts better.

How to Access SQL Scripts

To access the SQL Scripts, you need to first log in to the home page of the Workspace.

Next step is to view the SQL Scripts, you have to select the SQL Workshop icon’s down arrow and choose the option SQL Scripts from the drop down menu.

How to create SQL Script

There are two methods. Here we look at the first method.

  1. Using the Script Editor
  • Go to the SQL Scripts page and select the create button. This opens the script editor.
  • You can enter the name of the script file in the Script Name field. Note that the extension names are not mandatory.
  • You can include, the SQL statements, PL/SQL blocks and SQL*Plus commands.
  • Finally, save the script file by clicking on the Save button.
  1. The second method is to upload from the local file system.
  • In the SQL Scripts page, click on the Upload button. This opens a dialog box.
  • You can either browse for the concerned file or enter the name and path of the file.
  • When you click on the Upload button, it uploads the file to the Script Repository.

What does the Script Editor do

You can add content to the existing file, edit existing content, execute or delete scripts stored in the Script repository.

How to Edit a SQL Script

  • Load an existing script file into the Script Editor. There are two ways to do it.
    1. You can click the desired script icon displayed in the Icons view.
    2. Another way is to click the Edit icon in the Details view.
  • If you do  any of the above mentioned actions, the Script Editor will open.
  • Then you can edit the script and save it to the repository.

How to delete a SQL Script

There are two ways to delete Scripts from the Scripts repository. Let’s take a look at the first method.

  1. First Method
  • On the SQL Scripts there is the View List. From the latter, select details.
  • Next click on Go item to select the scripts that is to be deleted.
  • You have the option to select the individual script or select all the scripts in the current page by clicking on the relevant check boxes.
  • You will be prompted to confirm the deletion. Once done the deletion is permanent and a message will be displayed.
  1. Second Method
  • Here the Script Editor is used to open the desired script. Next, click delete.
  • You will be prompted to confirm before the deletion.
  • Then the Script is permanently deleted from the Script repository after which a message is displayed.

How to copy a SQL Script

This can be done by saving the script in the Script Repository with a new name in the editor.

How to execute a SQL Script

  • On the View list of the SQL scripts page, select details.
  • Next click Go.
  • Then the details view will appear. Here click the Run icon of the desired script that you want to run.
  • Next the Run Script page is opened. This page will display the errors. In case there are errors, you need to click on Edit Script which is on the Run Script page. If there are no errors, click on run which is on the Run Script page. This will execute the script.
  • On the SQL Scripts page, go to the Tasks list and click on Manage results.
  • Then go to the Icons view, click on the Show Results click box and the desired Icon. This will display the Manage Script Results page, which lists the scripts available results.

How to view the SQL Scripts results

Hope this article on SQL Scripts gave you a clear insight about how to create and manage script file. Study the theory and implement the different operations to gain proficiency in this GUI editor. To know more about SQL recommend you to take this course on advanced SQL concepts.