Universal SQL Editor gives error, Rapid SQL and other editors don't (declare cursor)
Normally work with Embarcadero's Rapid SQL. Wanted a better editor, downloaded a free trial version of Universal SQL Editor - it's great! And I'd prefer it to Rapid SQL except I get an error in Univ. SQL Ed. that I don't get in Rapid SQL. It's associated with this code (very simple, not going to bother with formatting):
DECLARE CURSOR c IS SELECT * FROM MY_TABLE;
And this is the error:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
;
Which is strange, because there IS a ";" at the position referenced by the error.
I've asked for help from the developer himself, Ming, and so far he's suggested I change the editor's settings so that the end-of-statement character is "/" instead of ";" - but I still get the same error. This is very frustrating. All my other code runs exactly as it does in Rapid SQL, this is the only error. I've searched through SQL-discussion forums, haven't found a solution that fits.
(FYI, I'm connected to an Oracle 10.2 datasource).
Re: Universal SQL Editor gives error, Rapid SQL and other editors don't (declare cursor)
I think that if you are using the Create or Replace syntax, you can not use Declare. Declare is only for anonymous blocks that are not named. So either remove line 1 and create an anonymous block, or remove line 2 and create a named procedure.
Code:
CREATE OR REPLACE PROCEDURE "FYP"."TEST" is
DECLARE
CURSOR c_test IS
SELECT name
FROM fyp.location
WHERE name like "ho%";
v_name fyp.location.name%TYPE;
v_count BINARY_INTEGER:=0;
BEGIN
OPEN c_test;
LOOP
FETCH c_test INTO v_name;
EXIT WHEN c_test%NOT_FOUND;
v_count := v_count + 1;
END LOOP;
CLOSE c_test;
END;
Re: Universal SQL Editor gives error, Rapid SQL and other editors don't (declare cursor)
I'm not using create or replace syntax. This code runs without error in every SQL editor I've tried except Universal SQL Editor!! But I really like Universal SQL Editor, I want to figure out why it has a problem when there's absolutely nothing wrong with the code.
here's the distilled essence of my entire code (this doesn't actually do anything, but it contains all the elements and statements I'm using):
Code:
DECLARE
CURSOR C IS
SELECT 1 FROM dual;
BEGIN
FOR EVENT_REC IN C
LOOP
NULL;
END LOOP;
COMMIT;
END;
Re: Universal SQL Editor gives error, Rapid SQL and other editors don't (declare cursor)
A-ha! To run that batch sql code, I needed to do two things:
1. Tools --> Options--> Script, change Statement Delimiter from ';' to '/'
2. Place a single '/' symbol at the very end of my batch sql code.
Now it works!
Re: Universal SQL Editor gives error, Rapid SQL and other editors don't (declare cursor)
Now that I've gotten that one issue resolved, I can unreservedly say Universal SQL Editor is my favorite SQL editor! Love it. (And it's not very expensive!)