Execute PL\SQL script in Oracle using Powershell

Question

I have the following script written that helps me to execute PL\SQL Insert/Update commands using powershell from one file. If I add any select commands in the same file It won't work. The Script is as below,

cls
# Oracle Read File 

# Load the good 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 the file PANEL_UPDATE.sql are,

BEGIN
  UPDATE TABLE1 SET COL1 = 'TEST1' WHERE COL1 = 'TEST2';
  UPDATE TABLE1 SET COL2 = 'TEST1' WHERE COL2 = 'TEST2';
  UPDATE TABLE1 SET COL3 = 'TEST1' WHERE COL2 = 'TEST2';
  SELECT COL1 FROM TABLE1 WHERE COL1 = 'TEST1';
END

Here is the exception thrown from Powershell

Exception calling "ExecuteNonQuery" with "0" argument(s): "ORA-06550: line 1, column 319:
PLS-00428: an INTO clause is expected in this SELECT statement"
At C:\Users\ssz\Desktop\OracleReadFile.ps1:27 char:5
+     $reader1=$command1.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : OracleException

If I remove the SELECT query from that file it works perfect for me. Please help me to change powershell script that will execute all the SQL commands from the file..

Thanks, Saurabh

Answer 1

Try to use ref cursor if its a multi row output or can use INTO if it is a scalar output. Hope this helps.

-- 1st approach using refcusor

DECLARE
p_lst sys_refcursor;
BEGIN
  UPDATE TABLE1 SET COL1 = 'TEST1' WHERE COL1 = 'TEST2';
  UPDATE TABLE1 SET COL2 = 'TEST1' WHERE COL2 = 'TEST2';
  UPDATE TABLE1 SET COL3 = 'TEST1' WHERE COL2 = 'TEST2';
Open P_LST FOR
  SELECT COL1 FROM TABLE1 WHERE COL1 = 'TEST1';
END;
/

-- 2st approach using INTO clause

DECLARE
p_lst varchar2(100);
BEGIN
  UPDATE TABLE1 SET COL1 = 'TEST1' WHERE COL1 = 'TEST2';
  UPDATE TABLE1 SET COL2 = 'TEST1' WHERE COL2 = 'TEST2';
  UPDATE TABLE1 SET COL3 = 'TEST1' WHERE COL2 = 'TEST2';
  SELECT COL1 INTO p_lst FROM TABLE1 WHERE COL1 = 'TEST1';
END;
/