SQL Developer : Add Custom SQL Query as Template.

Daily for SQL plan reading and analysis, using below command quite often from SQL Developer Tool.

explain plan for statement_id = '';
select * from gv$sql where sql_text like '%%';
select * from table(dbmx_xplan.display);
select * from table(dbmx_xplan.display_cursor('',null,'ALLSTATS_LAST'));

Wouldn’t it be great, if we can have some template for all above command and executing queries on ease.

One of friend during our usual tea talk mention about code template options for SQL Developer and its quite amazing.
let us walk through same.

In SQL Developer follow below Options.

Tools –> Preference –> Database –> SQL Editor Code Template –> Explore the magic.


We can give a short name(ID) for each helper queries we execute for day to day jobs.

Now once SQL code template are added, now we can quickly run same,
Just type in the short name or ID we had given while adding template within query editor and let Autocomplete help you with save Query.


Thats Pretty cool!.
We might need to restart SQL Developer console after adding any query template.
To increase Autocomplete time for Code template, we can check below options as well.
If required.

Tools –> Preferences –> Code Editor –> Completion Insight

Enable Popup Speed to faster option.


About Deepak Mahto

Having 9+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s