Run multiple oracle queries by PowerShell

Question

I want to run oracle query through PowerShell. Single line query is running fine with no error. But now I have to run multi-line query.

set lines 400
select name,host_name,status,open_mode,to_char(startup_time,'DD-Mon-yy HH24:MI:SS') STARTUP_TIME from v$inst,v$db;

I tried this

$sql=@(
"set lines 400
select name,host_name,status,open_mode,to_char(startup_time,'DD-Mon-yy HH24:MI:SS') STARTUP_TIME from v`$inst,v`$db"
)

And multiple options but it is giving me error ORA-00922.

I am using ODP.net in PowerShell.

Please advise.

Update:

If I run this query in oracle(without PowerShell) then it is working fine.

Avshalom · Accepted Answer · 2015-08-13 10:01:26Z

1

Your $SQL Variable is an Array with only one line:

$sql.Count
1

Try create it in that way:

$sql = @()
$sql += 'set lines 400'
$sql += 'select name,host_name,status,open_mode,to_char(startup_time,''DD-Mon-yy HH24:MI:SS'') STARTUP_TIME from v`$inst,v`$db"'

Important Note:

When using 'Quotes' inside the string you should add ''Dobule Quoutes'' otherwise it will be illegal - like this: ''DD-Mon-yy HH24:MI:SS''

edited Aug 13, 2015 at 10:01

answered Aug 13, 2015 at 9:59

Avshalom

8,9471 gold badge29 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Roxx Over a year ago

Let me try. Voted up. Now i am getting this error. "ORA-00900: invalid SQL statement"

Roxx Over a year ago

ORA-00900 was due to syntax error. Now I follow the same instruction provided by you and it is giving me ORA-00922. After this line $reader=$command.ExecuteReader()

Roxx Over a year ago

If i simply check the $sql value then it is giving me query with no error

Roxx Over a year ago

Script is not going till there (foreach or Outline). It is giving me error at executereader(). However, I tried to run this code $sql=@() $sql += 'set lines 200' $sql += "select Username,User_ID from dba_users" but it is also giving me same error.

Roxx Over a year ago

If i simply run this $sql= "select Username,User_ID from dba_users" then it is giving me output.

|

Collectives™ on Stack Overflow

Run multiple oracle queries by PowerShell

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related