[Bug]: Execution failure when executing multiple TYPE creation SQLs at the same time

[smallsheeeep]

ODC version

4.2.3

OB version

3.2.4.3

What happened?

When I executing following SQLs in SQL console, executing failed:

delimiter ;
create or replace type ty is object(a int, b varchar2(10));
create or replace type ty1 is table of ty;

But if I executing these SQLs one by one, it will execute successfully.

What did you expect to happen?

SQL executing successfully.

How can we reproduce it (as minimally and precisely as possible)?

Executing above SQLs in SQL console.

Anything else we need to know?

No response

Cloud

No response

[smallsheeeep]

I can reproduce this issue by executing:

create or replace type ty is object(a int, b varchar2(10));
create or replace type ty1 is table of ty;

or

CREATE OR REPLACE PROCEDURE SAY_HELLO AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello!');
END
  SAY_HELLO;

CREATE OR REPLACE PROCEDURE SAY_BYE AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Bye!');
END
  SAY_BYE;

The root cause is that SqlSplitter will recognize create [or replace] type or create [or replace] procedure as the start of a PL, and then the delimiter ; will not take affect for splitting SQLs (The defaut pl end delimiter is /). So, the two SQL will be splitted to one SQL, and will executing failed when submit to OBServer by JDBC.

[smallsheeeep]

More information:

• These two SQLs can execute successfully in DMS.
• You can walk around by declare delimiter as following:

delimiter //
create or replace type ty is object(a int, b varchar2(10)) //
create or replace type ty1 is table of ty //
delimiter ;