While I would rather write Forms if given a choice, I often find myself with the task of writing some pretty hairy pl/sql procedures.  So here are some things I have found to be really helpful in getting the job done.

1.  You can change the working directory where SQL Plus looks for scripts to execute from within SQL Plus.  To do this, you use the File -> Open pull-down menu selection to get to the Open File window.  Use the features in this window to navigate to the directory you want to use. Then open a file -- I keep a little one with only several lines in it, named ~a.sql (so it is always first).  Once you open a file, the directory you are in becomes the current working directory.  You can run any .sql script in the directory simply by typing @NAME where NAME is a file named NAME.SQL.

If you create a small file to open, make sure its last line is a comment, beginning with two dashes, and ending with no Return at the end.  This way when you open the file, SQL Plus will return back to the SQL> prompt.
 

2.  Use the special SQL Plus copy/paste shortcut to quickly re-execute commands.  If you have a command on your SQL Plus window that you need to type in again, rather than re-typing it, just do this: Highlight the command using the standard Windows method -- Press the left mouse button, and drag the mouse across the text.  But before releasing the left button, click the right button.  This will copy and paste the text onto the command line.  If you pull the mouse down a line, it will also send a Return, causing SQL Plus to execute the command.  You can use this method repeatedly to copy and paste parts of a line or several lines at once on your screen.  It is a great aid to working with SQL Plus.
 

3.  Use a good editor.
 

4.  In PFE, turn on line numbering -- there is an icon on PFE's toolbar, or you can set preferences to always supply them.  (PFE does not store line numbers with the file.)  With PFE's line numbers and my "no blank lines" rule below, you can find the pl/sql errors easily.
 

5.  NEVER put blank lines in your script.  Always use two dashes as a comment line instead.  That way, when the PL/SQL compiler gives you a line/column where the error occurs, it corresponds to the number in file you are editing.  This works for me because I most always create a package or procedure, and the file starts with the "CREATE OR REPLACE PROCEDURE..." line, and ends with 3 lines: a period alone in column 1 (terminates SQL Plus edit mode), a slash (to run the script), and the command:  SHOW ERRORS PROCEDURE XXX

Synchronizing line numbers is really important when your procedure is hundreds or thousands of lines long.
 

6.  In SQL Plus, to cut or paste text using the keyboard keys, the standard Windows Ctrl-C and Ctrl-V keys do not work -- you get garbage when you try to paste.  Instead, the alternate Ctrl-Insert to copy and Shift-Insert to paste work great.
 

7.  In a SQL Plus script, if you ever run the script and it ends with the message "Input truncated to nn characters", you need to remove the blank spaces from the last line in the script.
 

Return to : Oracle Database, SQL, Application, Programming Tips