Create view using OracleScript class

[devmar12]

Hi,

I am using the OracleScript class to try to execute a DML command sequence. The script file contains, among others, the command to create a view:

Code: Select all

CREATE OR REPLACE VIEW test_schema.view1 AS 
WITH
  FUNCTION func1 RETURN VARCHAR2 IS 
  BEGIN
    RETURN 'test';
  END;
(
  SELECT col1, func1 FROM test_schema.table1;
);
/

Unfortunately, as a result of execution I get the error:

Code: Select all

ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset

I think it is related to the use of syntax that allows to define functions and procedures in the view body.
This functionality is available from Oracle 12.

Some tips on how to run this script correctly?
(I am using Devart.Data.Oracle v.9.9.867 and Oracle 12c2)

[Shalex]

We have reproduced the issue and are investigating it. We will notify you about the result.

[Shalex]

The bug with defining functions and procedures in the view body specified in OracleScript.ScriptText for Oracle 12c and higher is fixed in v9.11.980: viewtopic.php?f=1&t=40466.

Valid scripts:

Code: Select all

      script.ScriptText = @" 
CREATE OR REPLACE VIEW view1 AS 
WITH
  FUNCTION func1 RETURN VARCHAR2 IS 
  BEGIN
    RETURN 'test';
  END;
  SELECT dname, func1 FROM dept
/
";

Code: Select all

      script.ScriptText = @" 
CREATE OR REPLACE VIEW view1 AS 
WITH
  FUNCTION func1 RETURN VARCHAR2 IS 
  BEGIN
    RETURN 'test';
  END;
(
  SELECT dname, func1 FROM dept
)
/
";

[devmar12]

Hi guys,

I just looked here and saw your fix.
I will check that as soon as possible.
Thank you.