Home » SQL & PL/SQL » SQL & PL/SQL » Handling invalid identifier exception in plsql (oracle 11g)
Handling invalid identifier exception in plsql [message #663542] Wed, 07 June 2017 10:35 Go to next message
ynkr999
Messages: 7
Registered: May 2017
Junior Member
Hi Gazzag,

ERROR at line 6:
ORA-06550: line 6, column 13:
PL/SQL: ORA-00904: "ASD": invalid identifier
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored

i am getting above error in my plsql code.i want to hadle this error in exception block.is it possible to handle in exception block.
the following is my code.if any mistake is there please correct me.

DECLARE 
    v_ename VARCHAR2(20); 
    invalid_identifier_exception1 EXCEPTION; 
    PRAGMA EXCEPTION_INIT (invalid_identifier_exception, -06550); 
BEGIN 
    SELECT ename 
    INTO   v_ename 
    FROM   emp 
    WHERE  empno = &v_empno; 
EXCEPTION 
    WHEN no_data_found THEN 
      dbms_output.Put_line('this number does not exist with any employee'); 
    WHEN invalid_number THEN 
      dbms_output.Put_line('please enter valid number'); 
    WHEN invalid_identifier_exception1 THEN 
      dbms_output.Put_line ('Unexpected error'); 
    WHEN OTHERS THEN 
      dbms_output.Put_line('please contact your administrator'); 
END; 
Enter value for v_empno: uiy
old 8: where empno=&v_empno;
new 8: where empno=uiy;
PRAGMA EXCEPTION_INIT (invalid_identifier_exception, -06550);
*
ERROR at line 4:
ORA-06550: line 4, column 24:
PLS-00109: unknown exception name 'INVALID_IDENTIFIER_EXCEPTION' in PRAGMA EXCEPTION_INIT



*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read

*LF fixed topic title typo

[Updated on: Wed, 07 June 2017 13:14] by Moderator

Report message to a moderator

Re: Hnadling invalid identifier exception in plsql [message #663543 is a reply to message #663542] Wed, 07 June 2017 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
INVALID IDENTIFIER is a compile time error; not a run time error
Re: Hnadling invalid identifier exception in plsql [message #663545 is a reply to message #663542] Wed, 07 June 2017 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

And FEEDBACK in your previous topics BEFORE posting a new question.

Didn't you find the error message in the book gazzag pointed you to in your previous topic?
Or maybe you don't read the answers... in the same way you don't read the code you write?

[Updated on: Wed, 07 June 2017 10:57]

Report message to a moderator

Re: Handling invalid identifier exception in plsql [message #663547 is a reply to message #663542] Wed, 07 June 2017 15:02 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
ynkr999 wrote on Wed, 07 June 2017 10:35
Hi Gazzag,

ERROR at line 6:
ORA-06550: line 6, column 13:
PL/SQL: ORA-00904: "ASD": invalid identifier
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored

i am getting above error in my plsql code.i want to hadle this error in exception block.is it possible to handle in exception block.
the following is my code.if any mistake is there please correct me.

DECLARE 
    v_ename VARCHAR2(20); 
    invalid_identifier_exception1 EXCEPTION; 
    PRAGMA EXCEPTION_INIT (invalid_identifier_exception, -06550); 
BEGIN 
    SELECT ename 
    INTO   v_ename 
    FROM   emp 
    WHERE  empno = &v_empno; 
EXCEPTION 
    WHEN no_data_found THEN 
      dbms_output.Put_line('this number does not exist with any employee'); 
    WHEN invalid_number THEN 
      dbms_output.Put_line('please enter valid number'); 
    WHEN invalid_identifier_exception1 THEN 
      dbms_output.Put_line ('Unexpected error'); 
    WHEN OTHERS THEN 
      dbms_output.Put_line('please contact your administrator'); 
END; 
In addition to the other comments about compile-time vs. run-time errors, that EXCEPTION block is fundamentally flawed

- PL/SQL does not and cannot interact with the user. Those dbms_output messages will not be seen by the user until the procedure has completed and returned the buffer to the client to be processed. It will then be up to the client as to how or even whether the messages will be given to the user.

- Error messages of "unexpected error" and "please contact your administrator" are totally devoid of useful information. What is "administrator" supposed to do with a message like that? Better to have no exception block at all and let the entire error stack be returned to the client. At least then "administrator" will have some useful information to work with. If someone came to me with those messages I'd tell them to go find the guy that wrote the code.
Re: Handling invalid identifier exception in plsql [message #663552 is a reply to message #663547] Thu, 08 June 2017 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... In addition, read WHEN OTHERS to know other problems that reach using this clause.

Re: Handling invalid identifier exception in plsql [message #663577 is a reply to message #663542] Thu, 08 June 2017 16:17 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
When looking at the code i noticed that the following line was missing a "1" in the
name of the exception.

Current Line:

PRAGMA EXCEPTION_INIT (invalid_identifier_exception, -06550);

should be:

PRAGMA EXCEPTION_INIT (invalid_identifier_exception1, -06550);

Even with this change the code would cause the "ORA-06650 error when you entered
noting and hit the enter when prompted for the value of v_empno ("Enter value for v_empno:").
The specific exception was not raised. By adding a few Variables, Select Into,
and some If Logic. I was able to Raise the error when nothing is entered using
the following.

DECLARE 

    v_ename      VARCHAR2(20);
    lv_ck_empno  VARCHAR2(10);
    v_empno      NUMBER;
    invalid_identifier_exception1 EXCEPTION;
    PRAGMA EXCEPTION_INIT (invalid_identifier_exception1, -06650);

BEGIN

    SELECT '&v_empno' ||'*' INTO lv_ck_empno FROM DUAL;

    IF lv_ck_empno = '*' THEN
      RAISE invalid_identifier_exception1;
    END IF;

    SELECT ename
    INTO   v_ename 
    FROM   emp 
    WHERE  empno = v_empno;

EXCEPTION

    WHEN no_data_found THEN
      dbms_output.Put_line('this number does not exist with any employee'); 
    WHEN invalid_number THEN
      dbms_output.Put_line('please enter valid number'); 
    WHEN invalid_identifier_exception1 THEN
      dbms_output.Put_line ('Unexpected error');
    WHEN OTHERS THEN
      dbms_output.Put_line('please contact your administrator');

END;
/
Re: Handling invalid identifier exception in plsql [message #663580 is a reply to message #663577] Fri, 09 June 2017 00:54 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Good, we now know you have eyes.
Why not let OP learn let by himself?

Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Previous Topic: Different result when using FOR UPDATE
Next Topic: Update values "in groups"
Goto Forum:
  


Current Time: Fri Mar 29 03:46:01 CDT 2024