Sys_RefCursor

[sonia sardana]

I want to convert the following SP to SQL, but I am not able to do ::-

ORACLE SP

create or replace
PROCEDURE           "USP_TEST" 
(
p_CURPRODUCTTYPE out sys_refcursor,
p_PROD_ID IN TBL_PROD_MASTER.PROD_ID%TYPE :=NULL
) is
  begin
  open p_CURPRODUCTTYPE for
SELECT PROD_ID,
  PROD_CODE,
  PROD_NAME,
  PRIMARY_UOM,
  SECONDARY_UOM
FROM TBL_PROD_MASTER
WHERE TBL_PROD_MASTER.PROD_ID =NVL(p_PROD_ID,TBL_PROD_MASTER.PROD_ID)
ORDER BY TBL_PROD_MASTER.PROD_NAME;
end;

SQL SP : -

CREATE PROCEDURE "USP_TEST"(p_recordset OUT SYS_REFCURSOR , 
p_PROD_ID IN TBL_PROD_MASTER.PROD_ID%TYPE :=NULL
                      ) AS 
BEGIN 
  OPEN p_recordset FOR
   SELECT PROD_ID,
  PROD_CODE,
  PROD_NAME,
  PRIMARY_UOM,
  SECONDARY_UOM
FROM TBL_PROD_MASTER
WHERE TBL_PROD_MASTER.PROD_ID =NVL(p_PROD_ID,TBL_PROD_MASTER.PROD_ID)
ORDER BY TBL_PROD_MASTER.PROD_NAME; 

END ;
/

But I am getting the following ERRORs : -
Msg 102, Level 15, State 1, Procedure USP_TEST, Line 1
Incorrect syntax near 'p_recordset'.
Msg 156, Level 15, State 1, Procedure USP_TEST, Line 5
Incorrect syntax near the keyword 'FOR'.
Msg 195, Level 15, State 10, Procedure USP_TEST, Line 12
'NVL' is not a recognized built-in function name.