Siddharth Shanker Mishra

In this short tutorial I will show you how easily you can run or debug your PL/SQL Oracle Stored procedures in Oracle SQL Developer tool.

Let’s say you have a package named “SalesReport” and in this you have a stored procedure named “Get_Sales_Data”. Here is the code implementation:

create or replace PACKAGE BODY SALES_REPORT_PACKAGE AS
	-- Used in reports
	PROCEDURE GET_SALES_DATA(
		p_YEAR IN NUMBER,
		p_MONTH IN NUMBER,
		SALES_CURSOR OUT T_CURSOR
	)
	AS
    BEGIN
		OPEN SALES_CURSOR FOR
		SELECT * FROM sales WHERE to_char(created_datetime,'MM')=to_char(p_MONTH, 'FM900') 
        AND to_char(created_datetime,'YYYY')=to_char(p_YEAR);
	END GET_SALES_DATA;
END SALES_REPORT_PACKAGE;

Here in this SQL, we have two input parameter for entering month and year, and we have one output parameter with type of cursor, which contains the result data set.

To run this in SQL developer, you can use below query:

var mycur refcursor;
exec SALES_REPORT_PACKAGE.GET_SALES_DATA(2023, 4, :mycur);
print mycur;

If you want to debug your script, then you can use DBMS_OUTPUT.PUT_LINE() this to print some values. Like below:

create or replace PACKAGE BODY SALES_REPORT_PACKAGE AS
	-- Used in reports
	PROCEDURE GET_SALES_DATA(
		p_YEAR IN NUMBER,
		p_MONTH IN NUMBER,
		SALES_CURSOR OUT T_CURSOR
	)
	AS
    BEGIN
		DBMS_OUTPUT.PUT_LINE(to_char(p_YEAR));
		OPEN SALES_CURSOR FOR
		SELECT * FROM sales WHERE to_char(created_datetime,'MM')=to_char(p_MONTH, 'FM900') 
        AND to_char(created_datetime,'YYYY')=to_char(p_YEAR);
	END GET_SALES_DATA;
END SALES_REPORT_PACKAGE;

Now if you want to see these values in the output, then you must set serveroutput variable on by running set serveroutput on; command. After that you can see the data. For example:

set serveroutput on;
var mycur refcursor;
exec SALES_REPORT_PACKAGE.GET_SALES_DATA(2023, 4, :mycur);
print mycur;

Let me know if you have any questions in comments.