OracleScript not happy with statement

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
ColinBo
Posts: 9
Joined: Thu 27 Aug 2009 17:27
Location: Toronto, Canada

OracleScript not happy with statement

Post by ColinBo » Thu 27 Aug 2009 17:30

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
Posts: 9
Joined: Thu 27 Aug 2009 17:27
Location: Toronto, Canada

Post by ColinBo » Wed 02 Sep 2009 18:08

Bump. Anyone?

Shalex
Devart Team
Posts: 8072
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 03 Sep 2009 12:44

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
Posts: 9
Joined: Thu 27 Aug 2009 17:27
Location: Toronto, Canada

Post by ColinBo » Thu 03 Sep 2009 18:00

Shalex wrote: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

Post Reply