How to convert MS SQL Stored Procedure to Oracle

Question

I am stuck with this query. I try to convert but, i new at Oracle 11 g.

This is the ms sql stored procedure to convert with Oracle 11 g.

CREATE PROCEDURE [dbo].[GetCustomers_Pager]
       @SearchTerm VARCHAR(100) = ''
      ,@PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[City]
      INTO #Results
      FROM [Customers]
      WHERE [ContactName] LIKE @SearchTerm + '%' OR @SearchTerm = ''
      SELECT @RecordCount = COUNT(*)
      FROM #Results

      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

      DROP TABLE #Results
END

I find this ms sql stored procedure to this link

This is my fail oracle stored procedure. I'm stuck with INTO syntax.

CREATE OR REPLACE
PROCEDURE GetCustomers_Pager
( SearchTerm IN VARCHAR2
, PageIndex IN NUMBER DEFAULT 1
, PageSize IN NUMBER DEFAULT 10
, RecordCount OUT NUMBER
) AS
BEGIN  
  SELECT  
          ROW_NUMBER() OVER( ORDER BY CustomerID ASC )AS RowNumber,
          ,CustomerID
          ,CompanyName
          ,ContactName
          ,City
  INTO Results
  FROM Customers
  WHERE ( Customers LIKE SearchTerm + '%' OR ContactName LIKE SearchTerm + '%') OR SearchTerm = '';
  SELECT RecordCount = COUNT(*)
  FROM Results;
  SELECT * FROM Results
  WHERE RowNumber BETWEEN(PageIndex -1) * PageSize + 1 AND(((PageIndex -1) * PageSize + 1) + PageSize) - 1;
  DROP TABLE Results;

END GetCustomers_Pager;

Can anyone please help me?

Answer 1

You can use only simple type variable in the SELECT INTO clause, for selecting into array use SELECT BULK COLLECT INTO. However you have to declare the variable you are selecting into and its type must match.

If you want to select the count only, use this:

SELECT COUNT(*) INTO RecordCount
FROM (SELECT ROW_NUMBER() OVER( ORDER BY CustomerID ASC )AS RowNumber,
             CustomerID,
             CompanyName,
             ContactName,
             City
     FROM Customers
     WHERE ( Customers LIKE SearchTerm + '%' OR ContactName LIKE SearchTerm + '%') OR SearchTerm = '') s
WHERE RowNumber BETWEEN (PageIndex-1)*PageSize + 1 AND PageIndex*PageSize;

If you want to return the records, you have to define the array type, add the output parameter Records and use SELECT * BULK COLLECT INTO Records and then RecordCount := Records.COUNT;.

But the preferred way to return data is a REF CURSOR output parameter, see http://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php.

Answer 2

Oracle procedure code with cursor as output parameter:

CREATE OR REPLACE PROCEDURE GetCustomers_Pager (
  SearchTerm IN VARCHAR2, PageIndex IN NUMBER DEFAULT 1, PageSize IN NUMBER DEFAULT 10, 
  RecordCount OUT NUMBER, Records out sys_refcursor ) AS

begin
  -- count all matching rows
  select count(1) into RecordCount from Customers
    where ContactName like SearchTerm||'%';

  -- get all matching rows into cursor "Records"
  -- and filter them including PageIndex and PageSize parameters
  open Records for 
  select rn, customerid, companyName, contactName, City 
    from (
      select row_number() over (order by customerID) rn,
          customerid, companyName, contactName, City
        from customers where ContactName like SearchTerm||'%' )
    where rn between (PageIndex-1) * PageSize
      and (((PageIndex-1) * PageSize + 1) + PageSize) - 1;
end GetCustomers_Pager;

Example C# code calling this procedure and filling DataGridView:

OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "getcustomers_pager";
cmd.Connection = CONNECTION.oconn;
cmd.Parameters.Add(new OracleParameter("SearchTerm", OracleType.VarChar, 3)).Value = "Mar";
cmd.Parameters.Add(new OracleParameter("PageIndex", OracleType.Number)).Value = 1;
cmd.Parameters.Add(new OracleParameter("PageSize", OracleType.Number)).Value = 5;
OracleParameter p = new OracleParameter("RecordCount", OracleType.Number);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
p = new OracleParameter("Records", OracleType.Cursor);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);

OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);

dgv.DataSource = ds.Tables[0];