Testing SQL Query on ORACLE SQL Developer - beginner

Question

I'm new to Oracle SQL Developer and SQL in general. I have the following questions:

1) I'm connected to my company's database. I have access to the views. How do test out SQL query on Oracle SQL Developer as I'm trying to ensure that the result is in line with what I expect or that it even works. For example, when I write VBA code in Excel I a) debug, b) debug.print variable to immediate window to instropect them. I want to find out how to do that in Oracle SQL Developer.

2) How do I bring up results window in Oracle SQL Developer?

3) I tried to run the following SQL query but I got the following error message.

spool "C:\myfolder\TEST_sql.csv";
SELECT /*csv*/ CLIENT_NAME, ORDER_ID, PRODUCT, 
QUANTITY, to_char(CREATION_DATE,'mm/dd/yyyy 
hh24:mi:ss') as CREATION_DT
FROM reports.REPORT_ORDER
ORDER BY ORDER_ID
FETCH FIRST 5 ROWS;
spool off;

Error message:

Error starting at line : 2 in command -
SELECT /*csv*/ CLIENT_NAME, ORDER_ID, PRODUCT, 
QUANTITY, to_char(CREATION_DATE,'mm/dd/yyyy 
hh24:mi:ss') as CREATION_DT
FROM reports.REPORT_ORDER
ORDER BY ORDER_ID
FETCH FIRST 5 ROWS;
Error at Command Line : 4 Column : 10
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

are you on a 12c database? fetch first syntax won't work on 7, 8, 9, 10, or 11 databases — thatjeffsmith
– thatjeffsmith, Commented Nov 20, 2018 at 12:30

thatjeffsmith · Accepted Answer · 2018-11-20 12:45:31Z

You have two ways to execute a query in SQL Developer.

As a script:

spool c:\users\jdsmith\so.csv
select /*csv*/ first_name,
       last_name,
       to_char(hire_date,'mm/dd/yyyy hh24:mi:ss')  hire_date
  from employees
  order by employee_id
  fetch first 5 rows only;
spool off

Execute with F5 (or use the 2nd button on the Worksheet toolbar)

This will walk the contents of your SQL Worksheet (unless you have highlighted text, and then only that), and put it through our script (SQL*Plus) engine. That's how the SPOOL commands will be executed - those happen on the client, and not by the database.

Your other option is to just execute the query.

Your SINGLE query will be executed, and the first page of results will be returned to a data grid. As you scroll through the results, more rows will be fetched, until they are all back.

You are getting an error with your code, because either:

you're not on at least Oracle Database 12c, where FETCH FIRST syntax was added
AND you forgot the 'ONLY' keyword on your FETCH

Collectives™ on Stack Overflow

Testing SQL Query on ORACLE SQL Developer - beginner

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related