Using Stored procedure in Cognos with Oracle

Hi Guys ,

Below is a stored procedure in oracle that i am using in cognos to return the output.Its a simple procedure to know basic of how to use procedure in oracle

CREATE OR REPLACE
PROCEDURE get_emp_rs (prod_line IN varchar2 ,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT product_line_code
FROM sls_product_line_lookup
WHERE product_line_en = prod_line;

END get_emp_rs;

Note - Though the procedure is returning the out parameter ,There is no need to define out parameter in framework while creating query subject from stored procedure .Cognos automatically detects the out parameter .


I have supplied the value camping equipement so that some output is returned .Usually this value will be passed from the prompts . Like a value prompt parameter .

Note -Here in oracle you will have to use a cursor to return the data not like ms sql where we just write a select statement .Notice that i have kept the curson open have not closed it .