0
select * from test where test like '%test%'

This returns the expected data using sqldeveloper, but from the command line using sqlcl, when i type this command and hit enter, i just see a 2. If I hit enter again I see a 3.

What am I missing here? I see nothing in the sqlcl docs that covers this.

Ben
  • 519
  • 1
  • 7
  • 14
  • I'm guessing the 2, and 3, are prompting me to enter additional queries? – Ben Jun 16 '16 at 18:56
  • More of the same query, not additional queries. A new line doesn't indicate that the statement is complete - otherwise multiline (and nicely formatted) queries would be a problem. – Alex Poole Jun 16 '16 at 19:15

3 Answers3

2

It's behaving the same as SQL*Plus does. You've entered one line of text into the command buffer but you haven't told the client that it's a complete statement or told it to execute it. It's showing the line number prompt to show it's expecting more input.

You can either end the statement with a semicolon, or put a slash (/) on a new line on its own; from that documentation:

Ending a SQL Command
You can end a SQL command in one of three ways:

  • with a semicolon (;)
  • with a slash (/) on a line by itself
  • with a blank line

A semicolon (;) tells SQL*Plus that you want to run the command. Type the semicolon at the end of the last line of the command ... and press Return or click Execute. SQL*Plus processes the command and also stores the command in the SQL buffer. ... If you mistakenly press Return before typing the semicolon, SQL*Plus prompts you with a line number for the next line of your command. Type the semicolon and press Return again or click Execute to run the command.

A slash (/) on a line by itself also tells SQL*Plus that you wish to run the command. Press Return at the end of the last line of the command. SQL*Plus prompts you with another line number. Type a slash and press Return again or click Execute. SQL*Plus executes the command and stores it in the buffer.

SQL Devleoper supports these too, using a semicolon (by default) as the SQL termination character, and allowing that or a slash to terminate a statement. It allows you to omit it from the last statement in a script though, which SQL*Plus doesn't - the statement will never be run in that case. I think SQLcl behaves the same as SQL*Plus for that too.

(I realise SQLcl is not exactly the same, but most of the SQL*Plus documentation applies to SQL Developer and to its more recent sibling SQLcl. You can read more about it at ThatJeffSmith's blog.)

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
1

I suspect you need to terminate your query with a semicolon.

select * from test where test like '%test%';
Juan Tomas
  • 4,905
  • 3
  • 14
  • 19
1

You are loading the buffer and the tool is prompting you for the next line. To execute what is in the buffer, type a slash ('/') and press enter.

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • Why are you voting for this answer? Alex Poole has the most comprehensive and informative answer and deserves your vote more! – Gary_W Jun 16 '16 at 19:28
  • Because I can't vote for my own answer, and yours is useful too (and more succinct) *8-) – Alex Poole Jun 17 '16 at 09:17
  • @Alex Poole - :-) Looks like we voted for each others answers. I was considering deleting this answer as yours is more complete. – Gary_W Jun 20 '16 at 15:23