OracleScript not happy with statement

OracleScript not happy with statement

Postby 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

Postby ColinBo » Wed 02 Sep 2009 18:08

Bump. Anyone?
ColinBo
 
Posts: 9
Joined: Thu 27 Aug 2009 17:27
Location: Toronto, Canada

Postby 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?
Shalex
Devart Team
 
Posts: 7371
Joined: Thu 14 Aug 2008 12:44

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


Return to dotConnect for Oracle