title: Pass SQL Server stored procedure to oracle pl/sql stored procedure
body: <p>I have two procedures in SQL Server I want to pass to Oracle:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[usp_GetSettingValueByIndividualKey] 
       @PortalID INT
       ,@SettingKey NVARCHAR(256)
  AS
  BEGIN
      IF EXISTS (SELECT *
                 FROM SettingValue
                 WHERE settingKey = @SettingKey
                   AND SettingTypeID = @PortalID)
      BEGIN
          SELECT SettingValue as Value
          FROM SettingValue
          WHERE settingKey = @SettingKey
            AND SettingTypeID = @PortalID
      END
      ELSE
      BEGIN
          SELECT SettingValue as Value
          FROM SettingKey
          WHERE settingKey = @SettingKey
            AND PortalID = 1
      END
  END
  </code></pre>
  
  <p>I call that stored procedure from this C# method:</p>
  
  <pre><code>public T ExecuteAsObject&lt;T&gt;(string StroredProcedureName, List&lt;KeyValuePair&lt;string, object&gt;&gt; ParaMeterCollection)
      {
          string orclConString = System.Configuration.ConfigurationManager.ConnectionStrings["orclConnString"].ConnectionString;
          OracleConnection oraConec = new OracleConnection(orclConString);
  
          try
          {
              //SqlDataReader SQLReader;
              //SqlCommand SQLCmd = new SqlCommand();
              //SQLCmd.Connection = SQLConn;
              //SQLCmd.CommandText = StroredProcedureName;
              //SQLCmd.CommandType = CommandType.StoredProcedure;
  
              oraConec.Open();
              OracleCommand oraCmd = new OracleCommand(StroredProcedureName, oraConec);
              oraCmd.CommandType = CommandType.StoredProcedure;
              oraCmd.BindByName = true;
              //Loop for Parameters
              for (int i = 0; i &lt; ParaMeterCollection.Count; i++)
              {
                  OracleParameter oracleParaMeter = new OracleParameter();
                  oracleParaMeter.IsNullable = true;
                  oracleParaMeter.ParameterName = ParaMeterCollection[i].Key;
                  oracleParaMeter.Value = ParaMeterCollection[i].Value;
                  oraCmd.Parameters.Add(oracleParaMeter);
              }
              //End of for loop
  
              OracleDataReader dr = oraCmd.ExecuteReader();
              dr.Read();
  
              //SQLConn.Open();
              //SQLReader = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection);
              //ArrayList arrColl = DataSourceHelper.FillCollection(SQLReader, typeof(T));
  
              List&lt;T&gt; mList = new List&lt;T&gt;();
              ArrayList arrColl = DataSourceHelper.FillCollection(dr, typeof(T));
  
              oraConec.Close();
  
              if (dr != null)
              {
                  dr.Close();
              }
  
              if (arrColl != null &amp;&amp; arrColl.Count &gt; 0)
              {
                  return (T)arrColl[0];
              }
              else
              {
                  return default(T);
              }
          }
          catch (Exception e)
          {
              throw e;
          }
          finally
          {
              oraConec.Close();
          }
      } 
  </code></pre>
  
  <p>The other stored procedure is :</p>
  
  <pre><code>ALTER PROCEDURE  [dbo].[sp_PortalGetList]
  AS
  BEGIN
      SELECT 
         Por.PortalID, [Name],
         LOWER(LTRIM(RTRIM(SEOName))) AS SEOName, 
         IsParent, Por.ParentID,
         (SELECT SEOName 
          FROM Portal AS port 
          WHERE Por.ParentID = port.PortalID) AS ParentPortalName,
         [dbo].[SettingValue].SettingValue AS DefaultPage
      FROM 
         dbo.Portal AS Por
      INNER JOIN 
         [dbo].[SettingValue] ON  Por.PortalID = [dbo].[SettingValue].settingtypeid 
      WHERE 
         SettingType='SiteAdmin' and SettingKey='PortalDefaultPage'
  END
  </code></pre>
  
  <p>I call that stored procedure from this C# method:</p>
  
  <pre><code>  public List&lt;T&gt; ExecuteAsList&lt;T&gt;(string StroredProcedureName)
    {
          string orclConString = "connectionstrig";
          OracleConnection oraConec = new OracleConnection(orclConString);
          try
          {
  
              oraConec.Open();
              OracleCommand oraCmd =
   new OracleCommand(StroredProcedureName,   oraConec);
              oraCmd.CommandType = CommandType.StoredProcedure;
              oraCmd.BindByName = true;
  
              OracleDataReader dr = oraCmd.ExecuteReader();
              dr.Read();
  
              //SQLConn.Open();
              //SQLReader = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection); //datareader automatically closes the SQL connection
              List&lt;T&gt; mList = new List&lt;T&gt;();
              mList = DataSourceHelper.FillCollection&lt;T&gt;(dr);
  
              if (dr != null)
              {
                  dr.Close();
              }
              oraConec.Close();
              return mList;
          }
          catch (Exception e)
          {
              throw e;
          }
          finally
          {
              oraConec.Close();
          }
      }
  </code></pre>