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

1 Response to SQL Developer : Add Custom SQL Query as Template.

  1. John P says:

    I’d love to be able to bulk add individual templates–around 75 of them–without having to add an entry, enter text in a column, enter text in another column, and so on. Is there a file or directory where I can add multiple entries at once?


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