CREATE OR REPLACE PROCEDURE loc_var AS
stmt_no NUMBER;
name VARCHAR2(100);
BEGIN
stmt_no := 1; -- designates 1st SELECT statement
SELECT table_name INTO name
FROM user_tables
WHERE table_name LIKE 'ABC%';
stmt_no := 2; -- designates 2nd SELECT statement
SELECT table_name INTO name
FROM user_tables
WHERE table_name LIKE 'XYZ%';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
('Table name not found in query ' || stmt_no);
END;
/
CALL loc_var();