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; |
文章评论