SQL Developer Code Templates Autocomplete

Using the short keyword to fill complex SQL in Oracle SQL Developer to make your work more efficient.

Follow those simple steps to complete this setup in 5 minutes:

How to use Code Template in Oracle SQL Developer
- Open Oracle SQL Developer.
- Connect to any database and open SQL Query window.
- Type "ssf", you will be suggest to input select table commnad.
- This is the built-in Code Template. Let us create some more.
Create Code Templates
- Click Menu -> "Settings"
- Expand "Code Editor"
- Select "Code Templates"
- Click "Add Template"
- There will be a new empty record at the end, input the keyword in the Id column and your query in the Template column.
- When inputting the template, click the Pencil button to open a larger edit window.
- Use [ ] for dynamic content which will be replaced, like the table name. Anyway, you only can use one [ ] in the template.
Code Templates Example

Id	Usage	Template
ss	Search keyword in stored procedures	SELECT * FROM USER_SOURCE WHERE lower(TEXT) LIKE lower('%[keyword]%');
st	Search tables by keyword	SELECT table_name FROM user_tables WHERE table_name LIKE '%[keyword]%';
sc	Search tables by column keyword	SELECT table_name, column_name FROM all_tab_columns WHERE UPPER(column_name) LIKE UPPER('%item_id%') order by UPPER(column_name), upper(table_name);
tochar	Convert date to char	to_char([column], 'YYYY-MM-DD HH24:MI:SS')
todate	Convert char to date	to_date('[2024-01-30]','yyyy-mm-dd')
alter	Alter table column	ALTER TABLE [table_name] MODIFY (column_name datatype);
analyze	Analyze table	analyze table [table name] compute statistics;
datafile	List data files	SELECT file_name, tablespace_name, bytes/1024/1024\|\|'M' FROM dba_data_files;
kill	Kill a dead lock query	SELECT 'ALTER SYSTEM KILL SESSION '''\|\|sid\|\|','\|\|serial#\|\|''';', s.username, s.program, s.sql_id, s.sql_child_number, s.status, t.sql_text FROM v$session s left join v$sql t on s.sql_id=t.sql_id WHERE s.type != 'BACKGROUND' and users_executing>0;
merge	Merge into table	MERGE INTO [target_table] t USING () f ON (t.item_id = f.item_id and t.loc_id=f.loc_id) WHEN MATCHED THEN UPDATE SET t. = f., t. = f. WHEN NOT MATCHED THEN INSERT (item_id,loc_id) VALUES (f.item_id,f.loc_id);
space	Query table space usage	SELECT a.tablespace_name, total, free, (total - free) rest, ROUND((total - free) / total * 100, 2) \|\| '%' used FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ORDER BY (total - free) DESC;
adddatafile	Add a data file to table space	ALTER TABLESPACE [name] ADD DATAFILE 'name and path.DBF' SIZE 2000M AUTOEXTEND ON;