Working with Procedures

CREATE OR REPLACE PROCEDURE archive_orders
(cust_id IN NUMBER
,retention IN NUMBER) IS
BEGIN
DELETE orders
WHERE customer = cust_id
AND order_date < SYSDATE - retention;
INSERT INTO maint_log
(action, action_date, who) VALUES
('archive orders '||retention||' for '||cust_id
,SYSDATE ,USER);
END;

EXEC DBMS_OUTPUT.PUT_LINE('Hello world!');
CALL DBMS_OUTPUT.PUT_LINE('Hello world!');

Working with Functions

CREATE OR REPLACE FUNCTION is_weekend(
check_date IN DATE DEFAULT SYSDATE)
RETURN VARCHAR2 AS
BEGIN
CASE TO_CHAR(check_date,'DY')
WHEN 'SAT' THEN
RETURN 'YES';
WHEN 'SUN' THEN
RETURN 'YES';
ELSE
RETURN 'NO';
END CASE;
END;

ldd command - shared libraries

ldd command: prints the shared libraries required by each program or shared library specified on the command line.

Sort the null or blank value in SQL

ORDER BY
CASE
WHEN #sort_column IS NULL THEN '2'
WHEN #sort_column = '' THEN '1'
ELSE '0'
END,
#sort_column [ASC or DESC]