I have several queries unioned together, all of which have the same date parameter in the where clause. I'd like to make this easy to update. I've written T-SQL variables but PL SQL is a bit different. Trying to figure out how to execute something like this:
DECLARE
l_DATE DATE:= '01-JAN-18';
BEGIN
SELECT
ORDER_NUMBER, ORDER_DATE
FROM
T.ORDERS
WHERE
ORDER_DATE >= DBMS_OUTPUT.put_line(l_DATE);
END;
I'm using Oracle SQL Developer Version 17.2.0.188 Oracle Database 11g 11.2.0.40.0
You should refer variable directly:
DECLARE
l_DATE DATE:= '01-JAN-18';
BEGIN
SELECT ORDER_NUMBER, ORDER_DATE
-- BULK COLLECT INTO collection
-- Oracle does not return directly from stored procedure like SQL Server
FROM T.ORDERS
WHERE ORDER_DATE >= l_DATE;
END;
Oracle 12c:
DECLARE
l_cur SYS_REFCURSOR;
l_DATE DATE:= '01-JAN-18';
BEGIN
OPEN l_cur FOR
SELECT ORDER_NUMBER, ORDER_DATE
FROM T.ORDERS
WHERE ORDER_DATE >= l_DATE;
DBMS_SQL.RETURN_RESULT(l_cur);
END;
