Convert a stored procedure in SQLServer to Oracle

If you had Oracle 12, the conversion would be much simpler. Because from that version is able to handle the following 2 aspects that would be useful:

Code in Oracle 12

If you can upgrade your database to version 12, then you can write the stored procedure in the following way:

create procedure MostrarCategorias(numPagina int, registrosPorPagina int)
as
  c1 sys_refcursor;
begin
  open c1 for
    select id_categoria as "ID", codigo as "CÓDIGO", nombre_categoria as "NOMBRE", descripcion as "DESCRIPCIÓN"
      from Produccion.Categorias
     order by "NOMBRE", "ID"
    offset (numPagina - 1) * registrosPorPagina rows
    fetch next registrosPorPagina rows only;

  dbms_sql.return_result(c1);
end;

... that you can execute like this:

begin
  MostrarCategorias(numPagina => 1, registrosPorPagina => 10);
end;

... or like this:

execute MostrarCategorias(numPagina => 1, registrosPorPagina => 10);

Code in Oracle 11

But if you do not have the luxury of being able to upgrade your database, the pagination should be done using ROWNUM or the window function ROW_NUMBER() , the latter being the easiest option.

The other problem is that as I mentioned above, in Oracle 11, there is no convenient way to return a query result from a stored procedure. There are ways, using a ref cursor as an output parameter, but they are very inconvenient in their use.

If I were in your place, I would choose to use a PIPELINED TABLE FUNCTION . It has the disadvantage that you need to define 2 TYPE that represent the fields of the records that you are going to return. But once defined the TYPE and the function, its use is very simple.

For example, I do not know the exact structure of your Categorias table, but I'm going to assume that id_categoria is numeric and that the others are text. Assuming this, you would have to define the following TYPE in order to define the function (adjust the types of the fields to reflect your structure):

create type CategoriaRow as object (
  "ID" int,
  "CÓDIGO" varchar2(100),
  "NOMBRE" varchar2(100),
  "DESCRIPCIÓN" varchar2(100)
);

create type CategoriaTable is table of CategoriaRow;

Now you can define the following function (note the use of ROW_NUMBER() to perform paging):

create function MostrarCategorias(numPagina int, registrosPorPagina int)
return CategoriaTable pipelined as
begin
  for rec in (
    select *
      from (select id_categoria, codigo, nombre_categoria, descripcion,
                   row_number() over (order by nombre_categoria, id_categoria) as rn
              from Produccion.Categorias)
     where rn > (numPagina - 1) * registrosPorPagina
       and rn <= numPagina * registrosPorPagina
     order by rn
  ) loop
    pipe row(CategoriaRow(rec.id_categoria, rec.codigo, rec.nombre_categoria, rec.descripcion));
  end loop;
end;

Now you can execute the function conveniently as if you were consulting a normal table:

select * 
  from table(MostrarCategorias(numPagina => 1, registrosPorPagina => 10))

I use Oracle 11g and always paginate my queries with ROWNUM and a COUNT (*) OVER () . Specifically for your example it would be something like this:

CREATE OR REPLACE PROCEDURE Produccion.MostrarCategorias (
    NumeroPagina IN NUMBER,
    RegistrosPorPagina IN NUMBER,
    consulta OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN consulta FOR
        SELECT k.*
        FROM (
            SELECT j.*,
                CEIL(j.total_reg / RegistrosPorPagina) AS total_pag,
                ROWNUM AS rid
            FROM (
                SELECT id_categoria AS ID, 
                    codigo AS CÓDIGO, 
                    nombre_categoria AS NOMBRE, 
                    descripcion AS DESCRIPCIÓN,
                    COUNT(*) OVER () AS total_reg
                FROM Produccion.Categorias
                ORDER BY NOMBRE,
                    ID
            ) j
        ) k
        WHERE k.rid BETWEEN RegistrosPorPagina * (NumeroPagina - 1) + 1 AND RegistrosPorPagina * NumeroPagina;
END;