executing pl/sql anonymous block

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
lewis
Posts: 23
Joined: Thu 13 Sep 2012 15:59

executing pl/sql anonymous block

Post by lewis » Wed 24 Oct 2012 15:18

When i use this.oracleCommand.ExecuteNonQuery() or oracommand.BeginExecuteNonQuery in order to execute a simple pl/sql anonymous block like this :

begin
null;
end;

this the code :

this.oracleCommand = new OracleCommand();
this.oracleCommand.Connection = this.oracleConnection;
this.oracleCommand.CommandText = @"begin null; end;";
this.oracleCommand.CommandType = CommandType.Text;
this.oracleCommand.ExecuteNonQuery();


I got the error:

---------------------------

---------------------------
ORA-06550: line 1, column 6:
PLS-00103: Encountered the symbol "" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
---------------------------
OK
---------------------------

The question is, how can i execute a pl/sql anonymous block by using dotconnect?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: executing pl/sql anonymous block

Post by Pinturiccio » Thu 25 Oct 2012 14:07

dotConnect for Oracle supports PL/SQL blocks.

Oracle 10g throws this error when it meets carriage-return character CHR(13);

When you use a verbatim string literal as a source of the CommandText property in C#, every new line is preceeded by the CHR(13) and CHR(10) characters.

Most likely CommandText contains CHR(13) characters. Please check your CommandText and remove all the CHR(13) characters.

This problem does not occur with Oracle 11g.

lewis
Posts: 23
Joined: Thu 13 Sep 2012 15:59

Re: executing pl/sql anonymous block

Post by lewis » Thu 25 Oct 2012 16:55

Thanks for your answer, however my problem is: users can type any statement in the way they want :

eg.
1-
begin null; end;
2-
begin
null;
end;

So i could change chr(10) and CHR(13) characters before the execution but changing de original query text, so when i try to highlight the error position (in case it exists), the error position will be different to the expected. How can i resolve that?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: executing pl/sql anonymous block

Post by Pinturiccio » Fri 26 Oct 2012 12:23

Removing only the CHR(13) characters will be enough, as Oracle interprets the CHR(10) character correctly.
lewis wrote:How can i resolve that?
We offer you the following solution:
1. When you come across the new line character CRLF CHR(13)+CHR(10) in the text, delete only the CHR(13) carriage return character.
2.If you get an error, you can get the position of the incorrect symbol within the SQL statement in the Offset property of OracleException;
3. Count the number of CHR(10) line feed characters preceding the position where the exception occurred. You should count this in the changed text containing no CHR(13) carriage return characters;
4. Add the number you got at step 3 to the Offset property value of the OracleException object, this is the position in the text entered by the user.

lewis
Posts: 23
Joined: Thu 13 Sep 2012 15:59

Re: executing pl/sql anonymous block

Post by lewis » Fri 26 Oct 2012 13:45

Thanks sr, for the answer i going to check this solution and i let you know about it later.

Post Reply