OracleClient.OracleException ORA-06550 with anonymous block taking no argument [modif

[C# Programming]

hello

This scripts runs okay on SQL*Plus and TOAD (using same account "dev") but not sure when I try to wire it down through "System.Data.OracleClient" I keep getting the following error:


ORA-06550: line 1, column 8:\nPLS-00103: Encountered the symbol \"\" when expecting one of the following:\n\n begin function package pragma procedure subtype type use\n ... System.Data.OracleClient.OracleException...


Here's the code:

DECLARE
count_item int;
BEGIN
SELECT count(1) into count_item FROM user_sequences WHERE sequence_name = 'AUDITLOGSEQUENCE';
IF count_item > 0 THEN
begin
dbms_output.put_line('drop sequence AUDITLOGSEQUENCE');
EXECUTE IMMEDIATE ('DROP SEQUENCE AUDITLOGSEQUENCE');
end;
ELSE
dbms_output.put_line('no need to drop AUDITLOGSEQUENCE');
END IF;

EXECUTE IMMEDIATE 'CREATE SEQUENCE AUDITLOGSEQUENCE
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20';
dbms_output.put_line('AUDITLOGSEQUENCE created');

SELECT count(1) into count_item FROM user_tables WHERE table_name = 'LOG';
IF count_item > 0 THEN
begin
dbms_output.put_line('drop table LOG');
EXECUTE IMMEDIATE ('DROP TABLE LOG');
end;
ELSE
dbms_output.put_line('no need to drop table LOG');
END IF;

EXECUTE IMMEDIATE '
CREATE TABLE LOG (
Id numeric(19,0) NOT NULL,
CreateDate date default sysdate NOT NULL,
Thread varchar (255) NOT NULL,
LogLevel varchar (50) NOT NULL,
Logger varchar (255) NOT NULL,
Message varchar (4000) NOT NULL,
InnerException varchar (2000) NULL,
CONSTRAINT PK_LOG PRIMARY KEY (Id)
)';

COMMIT;

dbms_output.put_line('table LOG created');

dbms_output.put_line('setup complete');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('*** setup exception detected! ***');
dbms_output.put_line('error code: ' || sqlcode);
dbms_output.put_line('stack trace: ' || dbms_utility.format_error_backtrace);
RAISE_APPLICATION_ERROR(-20000, 'AuditTrail.oracle.tables.sql - install failed');
END;


As you can see, the script is anonymous block taking no argument

And I looked at the code, can't see anything wrong.

oCmd = oConn.CreateCommand();
oCmd.CommandText = DBUtil.FixParameterNameForOracle(oContext.DefaultDBProvider, StringUtil.ReplaceSpecialTokens(SQL));
oCmd.CommandType = System.Data.CommandType.Text;

oDataSet = new DataSet(CmdFilePath);
oTable = new DataTable(Name);
oDataSet.Tables.Add(oTable);
oAdapter = oFactory.CreateDataAdapter();

oAdapter.SelectCommand = (DbCommand)oCmd;
oAdapter.Fill(oDataSet, Name);


oCmd is of type "System.Data.OracleClient.OracleCommand". And it takes no parameter or input argument. I googled a bit closest I found is this:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/579b5a98-ec74-4f6f-b5b4-6919d8bc3e7c/[^ (http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/579b5a98-ec74-4f6f-b5b4-6919d8bc3e7c/)]

But my script contains no parameter.

Also, using "oCmd.ExecuteNonQuery()" instead of dataAdapter.Fill didn't help.

Thanks

dev

modified on Tuesday, April 7, 2009 5:13 AM