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

我正在尝试使用. net中的OracleClientProvider来执行PL/Sql块。我使用的语言是c#， DB是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 = connection;
     //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

当我在ORACLE客户端上运行PL/SQL块时，它会正确执行，但是在这里它会抛出一个错误的ORA-00922:缺失或无效的选项

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

我想问:1。脚本的执行方式是否与普通查询不同?2。我做错了什么?

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.

谢谢。

1 个解决方案