md.Rayad 14 Newbie Poster

I have a oracle query and i would like to run it by powershell using ODP.Net.
Below is the query.

set pagesize 100                                                                                
set linesize 200                                                                                
break on report                                                                                      
compute sum of free_mb on report                                                                
compute sum of act_size on report                                                               
compute sum of used_mb on report                                                                
col %used format 999.99                                                                              
col %Free format 999.99                                                                              
col act_size format 99,99,999.99                                                                     
col free_mb format 99,99,999.99                                                                      
col used_mb format 99,99,999.99                                                                      
col file_name for a60                                                                                
col largest_extent_MB format 99,99,999.99                                                            
col tablespace for a25                                                                               

select SUBSTR(a.tablespace_name,1,20) "TABLESPACE",act_size,free_mb,                                 
free_mb*100/act_size "%FREE",                                                                        
act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "%USED",largest_extent_MB                 
from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,                                           
max(bytes)/1024/1024 largest_extent_MB                                                               
FROM DBA_FREE_SPACE group by tablespace_name) a,                                                     
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM                                           
DBA_DATA_FILES group by tablespace_name) b                                                           
where a.tablespace_name(+)=b.tablespace_name                                                         
order by 1

Problem is i can run single line query but when it comes like this i am getting error ORA-Error-00922.

    [Reflection.Assembly]::LoadFile("E:\oracle\product\11.2.0\ODP.NET\bin\2.x\Oracle.DataAccess.dll") 

    $constr = "User Id=system;Password=password;Data Source=APIH" 

    $conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr) 
    $conn.Open() 
    $sql="select Username,User_ID from dba_users" 
    $command = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn) 
    $reader=$command.ExecuteReader() 

    $someArray = @() 
    #read all rows into a hash table 
    while ($reader.Read()) 
    { 
    $row = @{} 
    for ($i = 0; $i -lt $reader.FieldCount; $i++) 
    { 
    $row[$reader.GetName($i)] = $reader.GetValue($i) 
    } 
    #convert hashtable into an array of PSObjects 
    $someArray += new-object psobject -property $row 
    } 
    $conn.Close() 
    $someArray | Format-Table -AutoSize
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.