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; |
0 Comments