Hi,
I am new to Oracle world, I would like to use a simple stored procedure with parameters and display the output when executed. I know I am missing basic concepts, kindly help.
----- Creating a Procedure ---
CREATE OR REPLACE PROCEDURE USP_TEST(startDate DATE, endDate Date)
--defining input parameters--
AS
startDateR DATE;
endDateR DATE;
BEGIN
startDateR := NVL(startDate,last_day(add_months(sysdate,-1))+1);
endDateR := NVL(endDate, last_day(sysdate));
---staging Raw query --
WITH CTE
AS
(
SELECT DISTINCT FIRSTNAME
, LASTNAME
, STREET_1
, SPOUSE_LASTNAME
, SPOUSE_FIRSTNAME
FROM TAB
WHERE contact_date BETWEEN startDateR AND endDateR
)
---Make changes/transformation to CTE query and display the output.
SELECT Concat(FirstNAME,', ') , Concat(LASTNAME,', ') FROM CTE;
END;
--------------------------
Apparently this doesn't work. Can someone please guide me the steps.