oracle - 使用Powershell在Oracle中执行PL\SQL脚本

Question

我编写了以下脚本，可以帮助我使用 powershell 从一个文件执行 PL\SQL 插入/更新命令。如果我在同一文件中添加任何选择命令，它将不起作用。脚本如下，

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()

文件 PANEL_UPDATE.sql 的内容是，

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

这是 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

如果我从该文件中删除 SELECT 查询，它对我来说非常适合。请帮助我更改将执行文件中所有 SQL 命令的 powershell 脚本..

谢谢，索拉布

Answer 1

如果是多行输出，则尝试使用引用游标；如果是标量输出，则可以使用 INTO。希望这会有所帮助。

-- 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;
/