Adding Custom Snippets in VS Code for SQL Developer

Snippets..

We ship some for you. You can see them on the bottom left panel under your connections, and they’re the same we have in SQL Developer desktop.

VS Code is quite generous about matching these…maybe TOO generous? But the best suggestions are sorted to the top.

Now, I’ve always found code templates to be more interesting than code snippets. But in VS Code these two SQL Developer ideas have been merged into one, glorious animal.

Old School –

And when you go to use these in SQL Developer –

Yes, I know I’ve been using BEER examples for a very, very long time.

Ok, so how do we get these in VS Code, today?

By editing a JSON file.

StackOverflow covers this very well here.

But basically…

1. Use Command Palette to get to Snippets Editor

2. Be sure to pick the RIGHT set of snippets

You want the oracle_sql.json file – which will look like this –

I’ve noticed that since these are stored in AppData, that removing an old extension, won’t clean these up, so you might find more than one ‘Oracle’ or ‘SQL’ file to edit.

The VS Code developers were sweet enough to seed the snippet file with a bit of doc and example, and it was basically enough for me to do the damage you see below.

The cool thing (and this is true for VS Code in general) is that as SOON as you start editing and saving the file, the Snippets IMMEDIATELY become available in your code editors.

3. Follow the samples, put in your own snippets.

Note: this is a JSON file and must contain valid JSON, you can’t just blindly copy and paste your previous SQL Developer snippets or code templates into here. The code below is just a portion of the entire file, so you won’t see the enclosing {} pair to start and end the list.

VS Code will kindly let you know if your JSON is malformed – check this if you’re not seeing the snippets appear in your editor.

I’ll share two –

	"SSF": {
		"prefix": "SSF",
		"body": [
			"select *"
		   "from ${0:table}",
			"fetch first 25 rows only;"
		],
		"description": "SELECT * FROM"
	}
,
	"HELLO": {
		"prefix": "hello",
		"body": [
			"begin",
		    " dbms_output.put_line('hello, ' || '${1:name}');",
			" dbms_output.put_line('goodbye, ' || '${1:name}');",
			"end;",
			"/"
		],
		"description": "dbms output hello world"
	}

SSF – replaces this string with a SELECT * FROM snippet of code, but puts the cursor right where I need to put in the table or view name.

Ok the other one is just a tad bit more interesting.

HELLO – prints out a ‘hello world’ via DBMS_OUTPUT, but uses a template tab stop, twice…see what I mean below?

I have a feeling there’s much, much more interesting things you can do with Snippets in VS Code (Docs), but I’m still learning. When I find cool stuff, I’ll share it! For example, it supports tab stops, so you could setup a snippet to create table with generic column names, col1, col2, …n, and then have snippet tab stops set for each datatype.

Oh heck, let’s just code it.

	"CREATETABLE": {
		"prefix": "CT",
		"body": [
			"create table ${1:name}(",
		    " col1 integer,",
			" col2 $2,",
			" col3 $3,",
			" col4 $4,",
			" CONSTRAINT ${1:name}_pk PRIMARY KEY (col1));"
		],
		"description": "create table with PK"
	}

So we can use the templates to carry the table name over into the PK constraint name below, and then we can use the tab stops to quickly go between the data types for columns 2 through 4.

Read those docs, since $ is a reserved character, you’ll need to escape it if you want to deal with say, v\\$session.

Wait, could this be even easier?

I don’t know, please tell us! For example, we could spend the time to build a user interface to define and edit these snippets for our extension. But to be honest, it wasn’t all that bad to just ‘hack’ up the JSON myself.

We have a ton of features to build, so it may be awhile before we get to building the GUI to support Snippet editing.