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 = 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
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.
