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>
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
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