OracleScript not happy with statement

[ColinBo]

Just started to trial dotConnect and we're hitting a snag with OracleScript. When I try to run a script with the following block it fails:

DECLARE RowCount NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO RowCount FROM DBA_Tablespaces WHERE UPPER(Tablespace_Name) = 'TESTDATA';
IF (RowCount > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLESPACE TestData INCLUDING CONTENTS AND DATAFILES';
END IF;
END;

ORA-06550: line 1, column 30: PLS-00103: Encountered the symbol "" when expecting one of the following: begin function package pragma procedure subtype type use form current cursor

The same statement block works fine in SQL Developer.

Thoughts?

Thanks,
Colin

[ColinBo]

Bump. Anyone?

[Shalex]

Sorry for the delay. We cannot reproduce the problem with the latest 5.25.39 build of dotConnect for Oracle when executing the similar script via OracleScript. The possible reason can be incorrect assigning of the ScriptText property in runtime (please use only \n without \r). Please check the work of OracleScript in design time. Does the problem disappear?

[ColinBo]

Sorry for the delay. We cannot reproduce the problem with the latest 5.25.39 build of dotConnect for Oracle when executing the similar script via OracleScript. The possible reason can be incorrect assigning of the ScriptText property in runtime (please use only \n without \r). Please check the work of OracleScript in design time. Does the problem disappear?

I ended up solving it with the following approach:

Code: Select all

string scriptText = File.ReadAllText(filename).Replace("\r\n", "\n");
OracleScript script = new OracleScript(scriptText, connection);
script.Error += delegate(object sender, Devart.Common.ScriptErrorEventArgs e)
{
    Debug.WriteLine(string.Format("Error in SQL Script: {0} - {1} : line {2}, column {3} - '{4}'", e.Exception.Message, filename, e.LineNumber, e.LinePosition, e.Text));
    e.Ignore = false;
};
script.Execute();

It looks like using the Stream is useless as there is no way to intercept and replace the carriage returns. I tried to using the SqlStatementExecute event but the command text is read only. Perhaps a better design is in order to deal with an Oracle-ism?

Cheers,
Colin