SQL*Plus
If you've ever worked on Oracle databases then you've probably heard of, and stay well away from SQL*Plus - unless you happen to be a DBA!
I totally understand your position. As a general querying tool, SQL*Plus is less than ideal For such work you should be using something like SQLTools++ or TOAD.
However for running SQL updates, inserts, and PL-SQL scripts, SQL*\Plus should be your tool of choice.
Here's a few reasons why:
- Everyone should have SQL*Plus (as part of the Oracle client)
- SQL*Plus options are set in a script, not somewhere in an IDE. So its more likely that your script will be run with consistent options.
- SQL*Plus is less forgiving. While this is often a source of frustration, it does mean you're not writing scripts where your IDE assumes certain things.
- SQL*Plus can be run from the command line, and it as far less resource intensive than any IDE, especially products like TOAD!
- SQL*Plus can spool a log file for tracking what your script did. While you can do this in other IDEs, this is something you write directly into your script.
There is one exception to this rule - PeopleSoft Data Mover. Data Mover scripts are a bit inflexible and lack logging capabilities. However the one thing that datamover does extremely well is exporting data to flat files (DAT files) and importing it back in. If you ever need to write a script where you first want to take a backup of the data in one or more tables or you want to import data from a flat file from somewhere else - then Data mover should be your tool of choice. Data mover is also the best choice if you need to support more than one database platform.
When you run any script, you should generate a log file to indicate what is happening. This is where things like spooling output to a file come into play.
To generate a basic log file, all you need to do is:
- Spool to a specified log file at the start of your script
- Add any custom messages through the
PROMPTcommand - Turn off spooling at the end of your script
Here's a simple example of a sqlplus script that spools output to a file and indicates what is happening.
SPOOL Example.log
PROMPT Script Starting
PROMPT Creating Example.log in the current directory
PROMPT Truncating PS_EXAMPLE_TBL
TRUNCATE TABLE PS_EXAMPLE_TBL;
PROMPT Inserting data into PS_EXAMPLE_TBL
INSERT INTO PS_EXAMPLE_TBL VALUES("TEST", 1);
COMMIT;
PROMPT Script Complete
SPOOL OFF
No Comments