CODEWITHSUNDEEP
oracle-databasesqlfiddle
user1816353
user1816353

Reputation: 17

How to run Oracle in SQLFiddle

I am new to Oracle sql. I got a piece of code from the web, and paste it into sqlfiddle (http://sqlfiddle.com/):

For the schema, I created a temp table, which will be used in the sql query:

CREATE Global Temporary TABLE temp
 (id number
 ,x number)
 ,y CHAR(50))
 ON COMMIT DELETE ROWS;

I clicked build schema, which tells me "Schema Ready".

Then I paste the following query which is from Oracle official website on the right pane:

-- available online in file 'sample1'
DECLARE
   x NUMBER := 100;
BEGIN
   FOR i IN 1..10 LOOP
      IF MOD(i,2) = 0 THEN     -- i is even
         INSERT INTO temp VALUES (i, x, 'i is even');
      ELSE
         INSERT INTO temp VALUES (i, x, 'i is odd');
      END IF;
      x := x + 100;
   END LOOP;
   COMMIT;
END;

When I press run sql, and it returns errors:

ORA-06550: line 3, column 18: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset

Upvotes: 0

Views: 3066

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You need to change default query terminator([;]) to sth else than ; and you may need to add this separator and new line between blocks of code:

enter image description here

SqlFiddleDemo

DECLARE
   x NUMBER := 100;
BEGIN
   FOR i IN 1..10 LOOP
      IF MOD(i,2) = 0 THEN     
         INSERT INTO temp VALUES (i, x, 'i is even');
      ELSE
         INSERT INTO temp VALUES (i, x, 'i is odd');
      END IF;
      x := x + 100;
   END LOOP;
   COMMIT;
END;

What's up with that [ ; ] button under each panel?

This obscure little button determines how the queries in each of the panels get broken up before they are sent off to the database. This button pops open a dropdown that lists different "query terminators." Query terminators are used as a flag to indicate (when present at the end of a line) that the current statement has ended. The terminator does not get sent to the database; instead, it merely idicates how I should parse the text before I execute the query.

Oftentimes, you won't need to touch this button; the main value this feature will have is in defining stored procedures. This is because it is often the case that within a stored procedure's body definition, you might want to end a line with a semicolon (this is often the case). Since my default query terminator is also a semicolon, there is no obvious way for me to see that your stored procedure's semicolon isn't actually the end of the query. Left with the semicolon terminator, I would break up your procedure definition into incorrect parts, and errors would certainly result. Changing the query terminator to something other than a semicolon avoids this problem.

Upvotes: 4

Related Questions