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?
executing pl/sql anonymous block
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: executing pl/sql anonymous block
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.
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.
Re: executing pl/sql anonymous block
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?
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?
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: executing pl/sql anonymous block
Removing only the CHR(13) characters will be enough, as Oracle interprets the CHR(10) character correctly.
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.
We offer you the following solution:lewis wrote:How can i resolve that?
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.
Re: executing pl/sql anonymous block
Thanks sr, for the answer i going to check this solution and i let you know about it later.