When compiling an Oracle stored procedure using the Aquadata procedure editor compilations fails with two error rows. Both are ORA-00900: invalid SQL statement.
This is an Oracle 11g instance. Seems to be a recent regression. Also as a result of this bug I have lost code.
It's easy to reproduce. Just have Aquadata create a new procedure (it will create a PROC_HELLO_WORLD procedure). The error will occur during compilation.
52 KB
74 KB
There are two less desirable workarounds.
I suspect the bug is that the procedure editor parses up to the first occurrence of a semicolon. The is what is committed to the database. Any code following the first semi-colon is lost.
I suspect the bug is that the procedure editor parses up to the first occurrence of a semicolon. The is what is committed to the database. Any code following the first semi-colon is lost.
Aqaudata 16.0.1 appears to work correctly. Patches prior to 16.0.1 may have created the regression.
Aqaudata 16.0.1 appears to work correctly. Patches prior to 16.0.1 may have created the regression.
AFScriptParser class seems to be setting the _bSemiColonSep variable to true even when its false. This will definitely break the parser as the procedure has ";" in it. Trying to pinpoint where the change happened.
The issue is with a change in CommonSchemaUtil class at line: 486
Here the passed value for bSemiColonSep in the AFScriptParser class constructor changed from false to true. Will reassign to kin
AFScriptParser class seems to be setting the _bSemiColonSep variable to true even when its false. This will definitely break the parser as the procedure has ";" in it. Trying to pinpoint where the change happened.
The issue is with a change in CommonSchemaUtil class at line: 486
Here the passed value for bSemiColonSep in the AFScriptParser class constructor changed from false to true. Will reassign to kin
See attached SQL Log where you can see the SQL statement is ended where the ";" was. The change happened in SVN # 45565, 45564 by Kin-Hong. You can see the changes to CommonSchemaUtil.java.
See attached SQL Log where you can see the SQL statement is ended where the ";" was. The change happened in SVN # 45565, 45564 by Kin-Hong. You can see the changes to CommonSchemaUtil.java.
Verified in ADS v16.0.9. We have released the 16.0.9 patch which addresses the critical fix for Oracle Procedure Editor and a few other defects in Aqua Data Studio.
Patch: http://www.aquafold.com/download/v16.0.0/ads-16.0.9-patch.zip
Update Instructions: http://www.aquafold.com/support-update.html#v16
Changelog: http://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/changelog/page/Version-16.0/Version-16-0
Verified in ADS v16.0.9. We have released the 16.0.9 patch which addresses the critical fix for Oracle Procedure Editor and a few other defects in Aqua Data Studio.
Patch: http://www.aquafold.com/download/v16.0.0/ads-16.0.9-patch.zip
Update Instructions: http://www.aquafold.com/support-update.html#v16
Changelog: http://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/changelog/page/Version-16.0/Version-16-0
This issue is not specific to just the Oracle procedure editor. It applies to all procedure editors.
This issue is not specific to just the Oracle procedure editor. It applies to all procedure editors.
Verified in ADS v16.0.9-2, 17.0.0-rc-11 and ADS 18-dev-2
Verified in ADS v16.0.9-2, 17.0.0-rc-11 and ADS 18-dev-2
Testing Steps:
1. In Oracle 11g Server- Expand ADS Schema tree node, Expand Schema.
2. Right click on Procedure node, select ‘Create Procedure in Editor’
A tab opens with below default ‘Hello World’ create procedure script
CREATE PROCEDURE "SYS"."PROC_HELLO_WORLD" (MYARG IN OUT NOCOPY NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello world!');
END;
3. Click on ‘Compile’ button.
Procedure is created
Need to Execute above 3 steps with below options setting scenario
Steps to enable/disable option setting:
1. In Menubar, Go to File ->Options -> General
‘/’ Statement Separator
‘/’ at End of Line Terminates Statement
2. Enable/Disable this option , Click on OK.
Scenario 1:
-- / Statement Separator = Disabled
-- / at end of line terminates statement = Disabled
Scenario 2:
-- / Statement Separator = Enabled
-- / at end of line terminates statement = Enabled
Scenario 3:
-- / Statement Separator = Enabled
-- / at end of line terminates statement = Disabled
Scenario 4:
-- / Statement Separator = Disabled
-- / at end of line terminates statement = Enabled
Also create a procedure with all data types, when setting is done according to above scenarios
Procedure with all Data types
CREATE PROCEDURE "c##project"."PROC_ADT"
(a IN NUMBER, b OUT VARCHAR2,
c IN NUMBER, d OUT BINARY_FLOAT,
e IN BINARY_DOUBLE, f OUT UROWID,
g IN CHAR ,h OUT VARCHAR,
i IN DATE,j OUT FLOAT,
k IN INTERVAL DAY TO SECOND,l OUT VARCHAR2 ,
m IN NCHAR,n OUT ROWID,
o IN NUMBER,p OUT NVARCHAR2,
q IN RAW,r OUT TIMESTAMP WITH LOCAL TIME ZONE,
s IN TIMESTAMP,t OUT LONG,
u IN BLOB,v OUT CLOB,
w IN NCLOB, x OUT BFILE
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello world!');
END;
-> Able to create procedure with all data types in all mentioned scenarios with the combination of different setting
Testing Steps:
1. In Oracle 11g Server- Expand ADS Schema tree node, Expand Schema.
2. Right click on Procedure node, select ‘Create Procedure in Editor’
A tab opens with below default ‘Hello World’ create procedure script
CREATE PROCEDURE "SYS"."PROC_HELLO_WORLD" (MYARG IN OUT NOCOPY NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello world!');
END;
3. Click on ‘Compile’ button.
Procedure is created
Need to Execute above 3 steps with below options setting scenario
Steps to enable/disable option setting:
1. In Menubar, Go to File ->Options -> General
‘/’ Statement Separator
‘/’ at End of Line Terminates Statement
2. Enable/Disable this option , Click on OK.
Scenario 1:
-- / Statement Separator = Disabled
-- / at end of line terminates statement = Disabled
Scenario 2:
-- / Statement Separator = Enabled
-- / at end of line terminates statement = Enabled
Scenario 3:
-- / Statement Separator = Enabled
-- / at end of line terminates statement = Disabled
Scenario 4:
-- / Statement Separator = Disabled
-- / at end of line terminates statement = Enabled
Also create a procedure with all data types, when setting is done according to above scenarios
Procedure with all Data types
CREATE PROCEDURE "c##project"."PROC_ADT"
(a IN NUMBER, b OUT VARCHAR2,
c IN NUMBER, d OUT BINARY_FLOAT,
e IN BINARY_DOUBLE, f OUT UROWID,
g IN CHAR ,h OUT VARCHAR,
i IN DATE,j OUT FLOAT,
k IN INTERVAL DAY TO SECOND,l OUT VARCHAR2 ,
m IN NCHAR,n OUT ROWID,
o IN NUMBER,p OUT NVARCHAR2,
q IN RAW,r OUT TIMESTAMP WITH LOCAL TIME ZONE,
s IN TIMESTAMP,t OUT LONG,
u IN BLOB,v OUT CLOB,
w IN NCLOB, x OUT BFILE
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello world!');
END;
-> Able to create procedure with all data types in all mentioned scenarios with the combination of different setting
Issue #13773 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build 16.0.8-8, 17.0.0-rc-9, 18.0.0-dev-2 |
No time estimate |
1 issue link |
is broken by #13715
Issue #13715Add option "/" Statement separator with default value of Enabled |
There are two less desirable workarounds.