01 August 2006

Run a File in SQL Developer: Easing the Pain

At some point in your working day, assuming you potter with databases, you're probably going to want to run a script. Typically we might use SQL*Plus for this and copy and paste the full directory path. I tend to start the SQLPLUS.exe, and once logged in locate and drag the file to the SQL> prompt and run it. (By using drag n drop, I get the full path and file name.) If I start SQLPLSW.exe, either I type in the full path and file name or resort to various copy and paste steps.

A colleague sitting nearby made one of those "Hey Sue, I really like this feature" comments today. So I wandered over to see her 'feature of the moment'. She was in SQL Developer and running a bunch of SQL scripts. SQL Developer has really eased the pain of doing this task. Maybe the following will help you to.

Start with SQL Developer and open the file you want to run. I'll use demobld as it's short and sweet and pretty safe to execute. (demobld drops and creates EMP and DEPT and is typically used to refresh SCOTT's tables. To find demobld.sql, do a search on your database directory or find another simple script to run.) If you look at the image below, I have circled File ->New, as this is where you can start to open a file. You can see that I have previously opened demobld and so can use the "reopen" menu option. [click image for larger view]


Of course, if you have associated SQL Developer with .sql files, then just double click the file and it'll open in SQL Developer.

Now the first thing to notice is the name of the tab, is the file name. If you have a bunch of tabs open this is useful. The next thing, which is often a great frustration to our users, is the Run related commands are all grayed out.

That's because you have not yet associated the file with a user! Use the droplist on the right-hand side to select your user.

I selected the SCOTT connection. As soon as a connection is selected, the run commands are available. You should use Run Script (F5), as F9 only executes a single statement. This will run the file for your selected user.

By running the script using F5, you run all the commands as the connected user and the output is printed to the window below. If it's a long script with lots of feedback, you can scroll up and down through the window. SQL*Plus can be maddening like that if you don't spool everything out to a file.
...and of course, if you want to rerun the script for another user? Just change the user by using the droplist!

23 comments:

Anonymous said...

Thanks a lot for this tip Sue! As a new user of SQL Developer tips like these are invaluable and make the learning curve much more pleasurable to tackle!

Franco Soldera said...

Thanks for the useful hint!
I was still sticking with TOAD, just because I thought it was better working with files... (well, still I miss the status "VALID/INVALID" of the package/function/procedure when I edit it without compiling) but...
today I gave a try to your hint and I've decided to switch to SQL Developer premanently ;-)

Sue said...

Excellent. It's useful for me to know what's useful to you!

Sue

Anonymous said...

Thank you so much Sue!!!

Ian Parkin said...

I want to know how you would test a function in SQL Developer?

Javier said...

Use this feature carefully: commented code is also executed when running "Run script":

/*
delete from customer;
*/

delete from customer2;
commit;

Anyone knows how to avoid it?

Anonymous said...

Great tip.

I was trying to copy/paste 100Mo of queries and that tool kept on freezing. With the file import mode, it works fine.

Thanks

Sue said...

Hi Anonymous,

Yes there is an issue with large set of data like this. It's a performance issue we hope to address in the next release.

Sue

Sue said...

Javier,

(In response to your run script comment) This is not true for release 1.2.1.32.13 (current production release)

Sue

Bev said...

I'm switching from Toad to SqlDeveloper and have the need to execute a lot of scripts.
When I run the below sql in SQL Developer, I get the following in the script output tab:

-- Statement to select current date/time
SELECT to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;
-- Statement to select current DB
SELECT name from v$database;

Output tab: (how do I get it to spool the sql statment before the results? in SQL Developer).

TO_CHAR(SYSDATE,'MM/DD/YYYYHH24:MI:SS')
---------------------------------------
02/11/2008 19:59:16

1 rows selected

NAME
---------
TSTXXPRD

1 rows selected

Jason Ennor said...

Or you can simply point to the file like this:

(To combat the Spaces in Windows Folder names, enclose the Path in double Quotes)

@"C:\Documents and Settings\jasonennor\My Documents\Scripts\runtime.pkg"

Anonymous said...

Thanks for the 411. The grayed out buttons were mystifying.

LorHecto said...

Hi! Sue
I have scripts that connect and disconnect from many databases and schemas. I can't run it because connection scripts. What format have connect strings in SqlDeveloper?

Kind regards
H├ęctor

Sue said...

Hector,

I have scripts that I run that first connects as system and creates users then connects as each user and creates objects for those users.

If I want to connect to "sue" in the script, I use
connect sue/&&the_Password@&&your_Connect_String

Sue

Vikram Shu said...

Thanks Sue..that definitely helped!

Anonymous said...

Thanks so much Sue. I was using a book meant for SQL*Plus and/or iSQL*Plus. However, I had SQL developer installed on my system. Your instructions on how to run scripts on SQL developer really saved the day for me.
Thanks a lot!

Anonymous said...

Hi Sue,

Could you please let me know, how to customize / handle ERRORs while executing a script in SQL Developer.

My concern is, I dont want to continue further execution if there are any ERRORs (Syntax,Logical,Missing Expr..etc) in the script during execution only.

Sue said...

Thiws is already marked as an enhancement request on the Exchange - you should add your vote there.

Sue

lornadoone1972 said...

Hi Sue, I have been using this tool on a client connection and found it is fine for running my standard sql scripts. However, i am trying to run a plb file today and cannot get this to run. I have tried pasting an @ command pointing to the directory with the file. Going to file open and browsing to the plb file?! Any ideas on how to run this? Thanks, Lorna

lornadoone1972 said...

Sue, Lorna here again, I was able to run the plb using the F5 key - sorry to bother you - and thanks! Lorna

Anonymous said...

Hello Sue,

My Company already have SQL Navigator and PLSQL Developer licenses ... and now management dont want to renew those licenses and stick with using SQL Developer.

I am using SQL developer since long and I find it more user friendly than TOAD and SQL Navigator but I never use PLSQL developer.

Is there any pros and cons of SQL Developer over PLSQL Develoepr? Or you can share any link where I can compare these tools?

Please Advice!.

Thank you.

Sue said...

I'm biased! I love SQL Developer and think it provides a wide range of tools and utilities. Users of PL/SQL Developer love that product, in particular the PL/SQL support. Why not read the different forums and see what they say.

Sue

Srdjan said...

Hi Sue
We are trying to move to SQL Developer from Toad. One of the difference is that PL/SQL scripts don't need to be separated by '/' in Toad, and I can still run them without error. Is there any feature in SQL Developer to mimic this, I mean that I don't need to put / between scripts. Each script is ended with ;
Thanks