Posts
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.
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!
22 comments:
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!
Excellent. It's useful for me to know what's useful to you!
Sue
Thank you so much Sue!!!
I want to know how you would test a function in SQL Developer?
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?
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
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
Javier,
(In response to your run script comment) This is not true for release 1.2.1.32.13 (current production release)
Sue
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
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"
Thanks for the 411. The grayed out buttons were mystifying.
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
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
Thanks Sue..that definitely helped!
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!
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.
Thiws is already marked as an enhancement request on the Exchange - you should add your vote there.
Sue
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
Sue, Lorna here again, I was able to run the plb using the F5 key - sorry to bother you - and thanks! Lorna
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.
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
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
Post a Comment