1 Reply Latest reply: Nov 28, 2012 10:44 PM by Lannie Liberty RSS

    Output parameter in plsql Block fails

    CRoberts
      Hello. I am testing using an anonymous Plsql block with one in and out parameter. The in parameter work fine but when I add the out, the plsql block fails.

      This is my environment on a Windows 7 client attaching to a Solaris server. I am using Powershell 3.0, but it is very similar to #c. I have a .net framework of 4.5.
      PS L064217>    $GAC = $Env:Oracle_Home + "\" + "ODP.NET\bin\4\Oracle.DataAccess.dll"
      PS L064217>    [Void] [Reflection.Assembly]::LoadFile($Gac)
      PS L064217> [Reflection.Assembly]::LoadFile($Gac)
      
      GAC    Version        Location
      ---    -------        --------
      True   v4.0.30319     C:\windows\Microsoft.Net\assembly\GAC_64\Oracle.DataAccess\v4.0_4.112.3.0__89b4
      
      SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 19:09:57 2012
      
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Here is a sample of my test code:
      # Define Plsql Anonymous Block
      $Table1 = 'AdvSearch_Statutes'
      $Table2 = ($Table1).Substring(0, $Table1.Length-1) + '_Docs'
      
      $Caml_Doc_Id = 'CHP201500010'
      
      $Sql =  " DECLARE "
      $Sql += "   vCamlId      VARCHAR2(30)  := ':Param1' "
      $Sql += "   vPath_Tx     VARCHAR2(200); "
      $Sql += "   vDelRows_Nr  PLS_INTEGER := 0; "
      $Sql += "   CURSOR Docs_Cur IS "
      $Sql += "       SELECT XPath "
      $Sql += "       FROM $Table2 "
      $Sql += "       WHERE Caml_Doc_Id = vCamlId; "
      $Sql += " BEGIN "
      $Sql += "   OPEN Docs_Cur; "
      $Sql += "   LOOP "
      $Sql += "     FETCH Docs_Cur INTO vPath_Tx; "
      $Sql += "     EXIT WHEN Docs_Cur%NOTFOUND; "
      $Sql += "     IF (DBMS_XDB.ExistsResource(vPath_Tx)) "
      $Sql += "     THEN "
      $Sql += "       DelRows_Nr := DelRows_Nr + 1; "
      $Sql += "       DBMS_XDB.DeleteResource(vPath_Tx, DBMS_XDB.DELETE_RECURSIVE_FORCE); "
      $Sql += "     END IF; "
      $Sql += "   END LOOP; "
      $Sql += "   DELETE FROM $Table2 WHERE Caml_Doc_Id = vCamlId; "
      $Sql += "   vDelRows_Nr := vDelRows_Nr + SQL%ROWCOUNT; "
      $Sql += "   DELETE FROM $Table1 WHERE Caml_Doc_Id = vCamlId; "
      $Sql += "   vDelRows_Nr := vDelRows_Nr + SQL%ROWCOUNT; "
      $Sql += "   SELECT vDelRows_Nr INTO :Param2 FROM Dual; "
      $Sql += " EXCEPTION " 
      $Sql += "   WHEN OTHERS THEN ROLLBACK; "
      $Sql += " END; "
      
      # Set up the Connection and command objects using the prior defined information.  Ensure that
      # Bind by name is used.
      $Conn = New-Object Oracle.DataAccess.Client.OracleConnection($Connect_Str)
      $Cmd  = New-Object Oracle.DataAccess.Client.OracleCommand($Sql, $Conn)
      $Cmd.BindByName = $True
      
      #Set up the parameters for use with the Sql command.
      $Param1 = New-Object Oracle.DataAccess.Client.OracleParameter
      $Param2 = New-Object Oracle.DataAccess.Client.OracleParameter
      
      $Param1.DbType = 'AnsiString'
      $Param1.OracleDbType = 'Varchar2'
      $Param1.Direction = 'Input'
      $Param1.ParameterName = ':Param1'
      $Param1.Value = $Caml_Doc_Id
      
      $Param2.DbType = 'Int32'
      $Param2.OracleDbType = 'Int32'
      $Param2.Direction = 'Output'
      $Param2.ParameterName = ':Param2'
      
      [Void] $Cmd.Parameters.Add($Param1)
      [Void] $Cmd.Parameters.Add($Param2)
      
      # Open connection to database and execute the command
      Try
        {
         $Conn.Open();
         $Result = $Cmd.ExecuteNonQuery();
      
         If ($Param2.Value -Eq $Null) {$Counts = 0}
         Else {$Count  = ($Param2.Value).ToString()}
      
         Write-Host "Return Code:  $Result"
         Write-Host "Rows Deleted:  $Counts"
        }
      Catch [System.Exception]
       {
        $Param2
        Write-Host $_.Exception.ToString() -ForeGroundColor "Red"
       }
      Finally
       {
        $Conn.Close();
        $Conn.Dispose();
        "`nSuccessful end of script"
       }
      I have some output. It all seems to be okay. I am following a book rather closely, but after several attempts changing one thing or another, I still cannot find a way to send an out value. I have commented out the " SELECT vDelRows_Nr INTO :Param2 FROM Dual; " and that is the problem statement. Thank you for your help. The first portion is the plsql being echoed back, then I list out what Param2 is defined at. The last bit is the error returned.
      PS L064217> .\Test_Odp_PLsqlBlock.ps1
       DECLARE    vCamlId      VARCHAR2(30)  := ':Param1'    vPath_Tx     VARCHAR2(200);    vDelRows_Nr  PLS_INTEGER := 0;
       CURSOR Docs_Cur IS        SELECT XPath        FROM AdvSearch_Statute_Docs        WHERE Caml_Doc_Id = vCamlId;  BEGIN
        OPEN Docs_Cur;    LOOP      FETCH Docs_Cur INTO vPath_Tx;      EXIT WHEN Docs_Cur%NOTFOUND;      IF (DBMS_XDB.ExistsR
      esource(vPath_Tx))      THEN        DelRows_Nr := DelRows_Nr + 1;        DBMS_XDB.DeleteResource(vPath_Tx, DBMS_XDB.DEL
      ETE_RECURSIVE_FORCE);      END IF;    END LOOP;    DELETE FROM AdvSearch_Statute_Docs WHERE Caml_Doc_Id = vCamlId;    v
      DelRows_Nr := vDelRows_Nr + SQL%ROWCOUNT;    DELETE FROM AdvSearch_Statutes WHERE Caml_Doc_Id = vCamlId;    vDelRows_Nr
       := vDelRows_Nr + SQL%ROWCOUNT;    SELECT vDelRows_Nr INTO :Param2 FROM Dual;  EXCEPTION    WHEN OTHERS THEN ROLLBACK;
       END;
      
      
      DbType                  : Int32
      SourceColumnNullMapping : False
      Direction               : Output
      IsNullable              : False
      Offset                  : 0
      OracleDbTypeEx          : Int32
      OracleDbType            : Int32
      ParameterName           : :Param2
      Precision               : 0
      Scale                   : 0
      Size                    : 0
      ArrayBindSize           :
      SourceColumn            :
      SourceVersion           : Current
      Status                  : Success
      ArrayBindStatus         :
      CollectionType          : None
      Value                   :
      UdtTypeName             :
      
      Oracle.DataAccess.Client.OracleException ORA-01036: illegal variable name/number    at Oracle.DataAccess.Client.OracleEx
      ception.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object sr
      c, String procedure, Boolean bCheck)
         at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
         at CallSite.Target(Closure , CallSite , Object )
      Edited by: CRoberts on Nov 28, 2012 7:30 PM