I'm new to Oracle DB and PL/SQL, trying to create a script to have a database generated.
I understand that when I try to access V_$PARAMETER with insufficient rights, I get a "table or view not found" exception.
To make my SQL file robust I tried to catch this and other corresponding exceptions:
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
-- exception definition when accessing [V_\$PARAMETER] with insufficient rights
unknown_identifier EXCEPTION;
PRAGMA EXCEPTION_INIT(unknown_identifier, -06550);
BEGIN
DECLARE
omfSet V_\$PARAMETER.VALUE%TYPE;
BEGIN
NULL;
END;
EXCEPTION
WHEN unknown_identifier THEN
DBMS_OUTPUT.PUT_LINE('-- Error.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('---- Error: ' || SQLCODE);
END;
/
But my exception handler is never reached.
Whenever I call the above script from SQL*Plus (with insufficient privileges), it plain errors into the error line:
omfSet V_$PARAMETER.VALUE%TYPE;
*
ERROR at line 7:
ORA-06550: line 7, column 12:
PLS-00201: identifier 'V_$PARAMETER.VALUE' must be declared
ORA-06550: line 7, column 12:
PL/SQL: Item ignored
What am I missing? What did I do wrong?
Your help is very much appreciated.