How to compile and Run PL/SQL code from Textpad

[Nasar]

How to compile and Run PL/SQL code from Textpad. Would somebody please provide steps on how to add the PL/SQL run and compile tabs to the tools menu. And if I have to download any files, where should I download it from and what his its name.

Thanking You
Nasar

[Matthias Martin]

I use the following setup to compile PL/SQL code from TextPad:

Tool Settings (use Add Program...):
Command: C:\WINNT\compile.bat
Parameters: $File orcl scott tiger
Initial Folder: $FileDir
Save all documents first: checked
Capture Output: checked
Regular Expression to match output: ^\([0-9]+\)/\([0-9]+\) (note there is a blank at the end)
Registers:
File: empty
Line: 1
Column: 2

The batch file compile.bat has the following contents:

@ECHO OFF
REM
REM Compile a PL/SQL-Script.
REM
REM Usage: Compile PL/SQL-Script DB-SID DB-User DB-Password
if "%1" == "" goto invparam
if "%2" == "" goto invparam
if "%3" == "" goto invparam
if "%4" == "" goto invparam
if not "%5" == "" goto invparam
if not exist %1 goto noscript
REM Generate temporary PL/SQL-Script for compilation.
echo connect %3/%4@%2 > %TEMP%\compile.sql
type %1 >> %TEMP%\compile.sql
echo show errors >> %TEMP%\compile.sql
echo prompt >> %TEMP%\compile.sql
echo exit >> %TEMP%\compile.sql
REM Compile PL/SQL-Script.
sqlplus /nolog @%TEMP%\compile.sql
del %TEMP%\compile.sql
goto end
:invparam
echo Usage: Compile PL/SQL-Script DB-SID DB-User DB-Password
goto end
:noscript
echo PL/SQL-Script %1 not found
goto end
:end

Sqlplus is the command line version of SQL*Plus. It has to be on your search path.

You have to add a separate tool for every combination of database/user you want to use for compilation or you can use Tool Parameter Macros (i.e. $Prompt and $Password) or check Prompt for parameters.

You can use the same setup to execute SQL-Scripts, but I do not recommend that. Other tools like OraTool, Toad or SQL*Plus are better suited for that task.