Execute PL\SQL script in Oracle using Powershell -

i have following script written helps me execute pl\sql insert/update commands using powershell 1 file. if add select commands in same file won't work. script below,

cls # oracle read file   # load assembly add-type -path "c:\app\ssz\product\12.1.0\client_1\odp.net\managed\common\oracle.manageddataaccess.dll"  # production connexion string $compconstr = "data source=(description=(address_list=(address=(protocol=tcp)(host=host1)(port=1521)))(connect_data=(server=dedicated)(service_name=serv1)));user id=test1;password=test123;"  # connection object $oraconn= new-object oracle.manageddataaccess.client.oracleconnection($compconstr) $oraconn.open()  # requête sql $myquery = get-content "c:\panel_update.sql";   write-output $myquery  # command object $command1 = new-object oracle.manageddataaccess.client.oraclecommand($myquery, $oraconn) #$command1 = new-object oracle.manageddataaccess.client.oraclecommand #$command1.commandtext = $myquery #$command1.connection = $oraconn  # execution $reader1=$command1.executenonquery() #$reader1=$command1.executereader()  #while ($reader1.read()) #{ #  $dtable = $reader1["fild_name"]   #}  # fermeture de la conexion #$reader1.close() $oraconn.close() 

the content of file panel_update.sql are,

begin   update table1 set col1 = 'test1' col1 = 'test2';   update table1 set col2 = 'test1' col2 = 'test2';   update table1 set col3 = 'test1' col2 = 'test2';   select col1 table1 col1 = 'test1'; end 

here exception thrown powershell

exception calling "executenonquery" "0" argument(s): "ora-06550: line 1, column 319: pls-00428: clause expected in select statement" @ c:\users\ssz\desktop\oraclereadfile.ps1:27 char:5 +     $reader1=$command1.executenonquery() +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + categoryinfo          : notspecified: (:) [], methodinvocationexception + fullyqualifiederrorid : oracleexception 

if remove select query file works perfect me. please me change powershell script execute sql commands file..

thanks, saurabh

try use ref cursor if multi row output or can use if scalar output. hope helps.

-- 1st approach using refcusor  declare p_lst sys_refcursor; begin   update table1 set col1 = 'test1' col1 = 'test2';   update table1 set col2 = 'test1' col2 = 'test2';   update table1 set col3 = 'test1' col2 = 'test2'; open p_lst   select col1 table1 col1 = 'test1'; end; /  -- 2st approach using clause  declare p_lst varchar2(100); begin   update table1 set col1 = 'test1' col1 = 'test2';   update table1 set col2 = 'test1' col2 = 'test2';   update table1 set col3 = 'test1' col2 = 'test2';   select col1 p_lst table1 col1 = 'test1'; end; /