Parsing error in Oracle create function declaration-block (as/is)
- Dominant language
- Java
- Stars
- 51.8k
- Forks
- 4.4k
- Avg merge
- 3d 9h
- Merged PRs (30d)
- 189
Description
### Description
XMLTYPE breaks the parsing:
Simple Ctrl+Enter-Parsing and scriptexecution (alt+x) stops at line 4 just before the ; and creates an invalid function in the database.
Works with marked block and Ctrl+Enter but not as part of a change script.
Parsing is fine when commenting the line.
create or replace FUNCTION XML_KFA (XML_DATA_CLOB CLOB)
RETURN CLOB
AS
XML_DATA XMLTYPE;
min_date date := to_date('01/01/1901','MM/DD/YYYY');
BEGIN
RETURN 'abc';
END;
Works fine with annonymous block:
declare
XML_DATA XMLTYPE;
min_date date := to_date('01/01/1901','MM/DD/YYYY');
BEGIN
NULL;
END;
/
### DBeaver Version
Community Edition Version 22.1.4.202208051447
### Operating System
Windows 10 Pro
### Database and driver
Oracle Oracle Database 11g Release 11.2.0.4.0 - 64bit Production, Oracle Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
Oracle JDBC driver 12.2.0.1.0
### Steps to reproduce
create or replace FUNCTION XML_KFA (XML_DATA_CLOB CLOB)
RETURN CLOB
AS
XML_DATA XMLTYPE;
min_date date := to_date('01/01/1901','MM/DD/YYYY');
BEGIN
RETURN 'abc';
END;
/
--- SQL-Fehler [900] [42000]: ORA-00900: Ungültige SQL-Anweisung
--- Error : 900, Position : 0, Sql = min_date date := to_date('01/01/1901','MM/DD/YYYY'), OriginalSql = min_date date := to_date('01/01/1901','MM/DD/YYYY'), Error Msg = ORA-00900: Ungültige SQL-Anweisung
--- ORA-00900: Ungültige SQL-Anweisung
SELECT XML_KFA(dummy) FROM dual; ---- -> SQL-Fehler [6575] [65000]: ORA-06575: Package bzw. Funktion XML_KFA in ungültigem Zustand
### Additional context
_No response_
Contributor guide
Research direction
Reproduce the issue with the provided Oracle function declaration and compare it with the working anonymous block, using Ctrl+Enter and script execution. Trace the SQL parsing entry point that splits the declaration at the semicolon in the variable section. Done means the complete function is submitted as one statement and creates successfully, while the existing anonymous-block behavior remains intact.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases, tooling
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100