convert script to PL/SQL procedure

Question

I am new to Oracle PL SQL. For the following script, how to make it a procedure? I need to convert the script below to a procedure so that I can call it from an application (SSRS 2005). The application uses Oracle client driver and can't run the script below but can run procedures. So I am thinking to convert the script to a procedure.

If without the Exec dbms_application_info.set_client_info('81'); The script can be run by the application no problem. P.S I can run the below script in Toad, no problem.

Exec dbms_application_info.set_client_info('81');

SELECT 
    ooh.order_number Order_number
    , ool.ordered_item
    ,ooh.ordered_date
FROM     apps.oe_order_headers_all ooh
    , apps.oe_order_lines_all ool
WHERE 1 = 1
    AND ooh.header_id = ool.header_id
    AND ooh.order_number = '27889292'
    --AND ooh.ordered_date BETWEEN  TO_DATE('28-FEB-2013 00:00:00', 'DD-MON-YYYY HH24:MI:SS')  AND  TO_DATE('28-FEB-2013 23:59:59', 'DD-MON-YYYY HH24:MI:SS')-- 1.3
    and ooh.ordered_date >= TO_DATE('28-FEB-2013 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
    and ooh.ordered_date < TO_DATE('01-MAR-2013 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

Answer 1

Without parameters:

create or replace function GetOrderInfo return sys_refcursor 
as
  vRet sys_refcursor;
begin

  dbms_application_info.set_client_info('81');

  open vRet for 
    select  
      ooh.order_number Order_number,
      ool.ordered_item,
      ooh.ordered_date
    from 
      apps.oe_order_headers_all ooh,
      apps.oe_order_lines_all   ool
    where
       ooh.header_id = ool.header_id
       AND ooh.order_number = '27889292'
       --AND ooh.ordered_date BETWEEN  
              TO_DATE('28-FEB-2013 00:00:00', 'DD-MON-YYYY HH24:MI:SS')  
              AND  
              TO_DATE('28-FEB-2013 23:59:59', 'DD-MON-YYYY HH24:MI:SS')-- 1.3
       and ooh.ordered_date >= TO_DATE('28-FEB-2013 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
       and ooh.ordered_date < TO_DATE('01-MAR-2013 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
  ;

  return vRet;

end;

With set of parameters:

create or replace function GetOrderInfo(
  pClientId    in varchar2,
  pFromDate    in date,
  pToDate      in date, 
  pOrderNumber in varchar2
) return sys_refcursor 
as
  vRet sys_refcursor;
begin

  dbms_application_info.set_client_info(pClientId);

  open vRet for 
    select  
      ooh.order_number Order_number,
      ool.ordered_item,
      ooh.ordered_date
    from 
      apps.oe_order_headers_all ooh,
      apps.oe_order_lines_all   ool
    where
       ooh.header_id = ool.header_id
       and ooh.order_number = pOrderNumber
       --and ooh.ordered_date BETWEEN pFromDate AND pToDate-- 1.3
       and ooh.ordered_date >= pFromDate
       and ooh.ordered_date < pToDate
  ;

  return vRet;

end;