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