Problems while executing PL/SQL script block in C#

I am trying to execute a PL/Sql block by using the OracleClientProvider in .Net . The language I am using is c#, the DB is oracle10g

What I am actually doing is the following :

//ConnectionSting is the connection String
     OracleConnection connection = new OracleConnection(connectionString);
     OracleCommand cmd = new OracleCommand();
     cmd.Connection = connect开发者_如何学Cion;
     //queryFile contains the PL/SQL Script I am trying to execute;
     String queryFile = ConfigurationManager.AppSettings["MasterDbScript"];
     String dataInFile = new StreamReader(queryFile).ReadToEnd();
     cmd.CommandText = dataInFile;
     // Open the connection
     connection.Open();
     cmd.ExecuteNonQuery(); 
     //close the connection
 connection.close();

The PL/SQL block executes properly when I run it through ORACLE client but over here it throws an error ORA-00922: missing or invalid option

I want to ask : 1. Are the scripts executed in a different way from the normal query? 2. What am I doing wrong ?

Suggestions/Answers

The Script :

    Set serveroutput on
  Declare
    tableSize    varchar2(200);
         insertStatement varchar2(200) := 'Insert Into TableAndSize  values (:1,:2,:3,:4) ';
         rowupdateStatement varchar2(200) := 'select count(*) from   :0';
      numOfRows number  := 0;
      dataType       nvarchar2(200);

  queryConstruct varchar2(10000);
      tableNameInUserTabColumns nvarchar2(4000);
      num NUMBER :=0;
      zero number := 0 ;
      nums nvarchar2(4000);
      fields nvarchar2(1000);
      fieldSet nvarchar2(1000);
      columnName nvarchar2(1000);
      dummy nvarchar2(1000);
      Cursor Table_Names is  select Distinct TABLE_NAME from user_tab_columns where TABLE_NAME not like 'BIN%' and table_name != 'TABLEANDSIZE' order by table_name;

begin
execute immediate 'truncate table tableandsize';
open Table_Names;
  fetch Table_Names into tableNameInUserTabColumns;

while Table_Names%found
          loop
              DBMS_OUTPUT.PUT_LINE(tableNameInUserTabColumns);
                  queryConstruct := 'select nvl( sum (';
                  --DBMS_OUTPUT.PUT('select sum (');
                          Declare
                              detailsOftableInScope user_tab_columns%rowtype;
                              cursor tableDetails is select * from user_tab_columns where table_name = tableNameInUserTabColumns;

                          Begin
                              open tableDetails;
                              fetch tableDetails into detailsOftableInScope;
                                  while tableDetails%found
                                      loop
                                          dataType := detailsOftableInScope.DATA_TYPE;
                                              if dataType = 'CLOB' then
                                                    fields := 'nvl(DBMS_LOB.GETLENGTH(' || detailsOftableInScope.COLUMN_NAME ||'),0)';
                                              elsif dataType = 'BLOB' then
                                                      fields := 'nvl(DBMS_LOB.GETLENGTH('|| detailsOftableInScope.COLUMN_NAME ||'),0)';
                      elsif dataType = 'LONG' then
                                                          fields := 'nvl(VSIZE(''''),0)';
                                                  else
                                                      fields := 'nvl(vsize(' || detailsOftableInScope.COLUMN_NAME || '),0)';
                                              end if;
                                                  --DBMS_OUTPUT.PUT('*****'||fields);
                                          fetch tableDetails into detailsOftableInScope;
                                                  if tableDetails%found then
                                                  --DBMS_OUTPUT.PUT(''||fields||'+');
                                                  queryConstruct := queryConstruct || fields||'+';
                                                  else
                                                  --DBMS_OUTPUT.PUT(''|| fields);
                                                  queryConstruct := queryConstruct || fields;
                                                  DBMS_OUTPUT.ENABLE(100000);
                                                  end if;
                                      end loop;
                                  close tableDetails;
                          end;
                                  --DBMS_OUTPUT.PUT_LINE(') as sizeOfTable from  ' ||tableNameInUserTabColumns);
                                  queryConstruct := queryConstruct || '),0) as sizeOfTable from  ' ||tableNameInUserTabColumns;
                                  DBMS_OUTPUT.PUT_LINE('Query Being Fired To calculate the size of Data in Table  '||tableNameInUserTabColumns || ' --->  '||queryConstruct);
               execute immediate queryConstruct  into num;
                                  execute immediate insertStatement using tableNameInUserTabColumns,num,zero,zero;
                                  --DBMS_OUTPUT.PUT_LINE(num);
                  fetch Table_Names into tableNameInUserTabColumns;
          end loop;
  close Table_Names;
----finding number of rows and size of index
declare
      sumTotal number := 0;
      tableNameInUserTabColumns nvarchar2(4000);
      updateStatement varchar2(400);
  cursor Table_Names is select distinct(table_name) from user_tab_columns  where TABLE_NAME not like 'BIN%' and table_name != 'TABLEANDSIZE' order by table_name;
begin
open Table_Names;
dbms_output.put_line('*********************finding index size and number of rows******************************');
  fetch Table_Names into tableNameInUserTabColumns;
while Table_Names%found
          loop
          dbms_output.put_line('TABLE ---  '||tableNameInUserTabColumns);
              declare
                  Index_Name nvarchar2(4000);
                  size1 number := 0 ;
                  size2 number := 0 ;
                  tableIndexSize number := 0;
                  sizeOfIndexInTable number :=0 ;
                  sql_statement varchar2(1000) := 'select nvl(USED_SPACE,0) from index_stats';
                  stat1 varchar2(1000) := 'analyze index ';
                  stat2 varchar2(1000) := '  validate structure';
                  stat3 varchar2(2000) := '';
                  sum1 number := 0;
                  sum2 number := 0 ;
                  cursor indexOnTable is select Index_name from user_indexes where table_name = tableNameInUserTabColumns and index_type = 'NORMAL';
                  begin
                  open indexOnTable;
                  fetch indexOnTable into Index_Name;
                  while indexOnTable%found
                  loop
                      dbms_output.put_line('****  '||Index_Name);
                      DBMS_OUTPUT.ENABLE(100000);
                                          stat3 := stat1 || Index_Name || stat2;
                                          execute immediate stat3;
                                          execute immediate  sql_statement into size1;
                                          dbms_output.put_line('Query being fired to find size of index = '|| Index_Name || 'in table = ' || tableNameInUserTabColumns || '===>>' || stat3 ||' And ' ||sql_statement);
                                          tableIndexSize := tableIndexSize + size1;
                                          --dbms_output.put_line(size1);
                                          sumtotal:= sumtotal + size1  ;
                                          --dbms_output.put_line(sumtotal);
                      fetch indexOnTable into Index_Name;
                  end loop;
                          rowupdateStatement := 'select count(*) from  '|| tableNameInUserTabColumns;
            dbms_output.put_line('Query to find number of rows in '|| tableNameInUserTabColumns || ' --> '||rowupdateStatement);
                          execute immediate rowupdateStatement into numOfRows;
                          --dbms_output.put_line('----'||numofrows ||'-----');
                      updateStatement := 'update tableAndsize set indexsize = :0, RowNumber = :1 where tablename = :2';
                          --    dbms_output.put_line('----'||tableIndexSize ||'*****'||tableNameInUserTabColumns);
                     execute immediate updateStatement using tableIndexSize,numOfRows,tableNameInUserTabColumns ;
                      --dbms_output.put_line(tableIndexSize);
                      --dbms_output.put_line(updateStatement);

                  close indexOnTable;
              end;
          fetch Table_Names into tableNameInUserTabColumns;
          end loop;
close Table_Names;
end;
end;

Thanks.

Just a guess, but I noticed the connection is being opened twice, try removing one of the lines. If that doesn't work, can you post a sample of the query you're looking to run?

Is it possible the file is using encoded as Unicode (UTF-16, or UTF-8), and the stream is trying to read it as ASCII?

Have you looked at the contents of the string to ensure there are no "funny" characters?

The issue is the very top of your SQL script: "Set serveroutput on"

All PL/SQL specific directives had to be removed for me to run my script via C#.