2

I created a procedure as follows 

 create or replace procedure "GETCUSTNAMEZZz"
(cust_identifier1 IN NVARCHAR2(10))
is
begin
DBMS_OUTPUT.PUT_LINE (FNAME || ' ' || LNAME FROM customer WHERE  cust_id=cust_identifier1)
end;

I then try calling the procedure

BEGIN

GETCUSTNAMEZZz('C2')  ;  


END;

This sql gives me an error that identifier 'GETCUSTNAMEZZZ' must be declared ORA-06550: line 3, column 5: PL/SQL: Statement ignored"

This is the exact procedure that I created, and the parameter is correct.

How exactly do i declare the identifier??

Thanks in advance.

Daniel o Keeffe
  • 578
  • 2
  • 10
  • 25
  • You should be able to call it as shown, but remember to enter `SET SERVEROUTPUT ON SIZE 1000` (or whatever size) before executing. The problem is your proc doesn't exist because it didn't compile. I see a number of problems: (1) use `NVARCHAR2` instead of `NVARCHAR2(10)` for the parameter, (2) you have to query `FNAME` and `LNAME` into variables before you can show them, (3) the line of code that starts with `DBMS_OUTPUT.PUT_LINE` needs a semicolon at the end. If you create a proc and get "created with errors", type "show errors" to see the errors. – Ed Gibbs May 06 '13 at 19:44
  • If i have this correctly, even when oracle says procedure created it might not compile when called?? – Daniel o Keeffe May 06 '13 at 20:17
  • Correct - if it was created with errors Oracle will still let you try to call it; it will recompile but the recompilation in your case will fail. Please see the @DaveCosta answer below. Your proc still has issues, but you can save yourself a ton of grief by not using the double quotes - they're already sent you on a costly side-trip. – Ed Gibbs May 06 '13 at 20:34
  • possible duplicate of [ORA-00904: invalid identifier](http://stackoverflow.com/questions/6027961/ora-00904-invalid-identifier) – APC May 06 '13 at 20:47
  • If you use double quotes when creating database objects and you're careless enough to use mixed case then you are doomed to using double-quotes and the *exact same case* whenever you subsequently refer to that object. Life is just so much easier without the quote marks. – APC May 06 '13 at 20:49

2 Answers2

8

The reason for the "identifier must be declared" error is case sensitivity.

Oracle identifiers are normally automatically converted to all upper-case, but when you enclose them in double quotes, case is preserved. So this:

create or replace procedure "GETCUSTNAMEZZz"

creates a procedure with the exact name "GETCUSTNAMEZZz", while this:

create or replace procedure GETCUSTNAMEZZz

would create a procedure with the exact name "GETCUSTNAMEZZZ" (all upper case).

You are not using quotes when you're calling the procedure. So it is looking for "GETCUSTNAMEZZZ", which doesn't exist, because you created it as "GETCUSTNAMEZZz".

As a rule, I would suggest avoiding double quotes entirely as they just cause this kind of confusion without providing much benefit. Sadly, many tools for generating code like to stick them all over the place.

(Note that this is about identifiers not string values. When you are comparing string values (e.g. 'This is a String') and/or variables (e.g. a variable of type VARCHAR2), case always matters.)

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
2

This procedure will not compile. So you cannot call it. Also you must add SET SERVEROUTPUT ON otherwise you will not see the DBMS_OUTPUT.PUT_LINE.

You would SELECT what you want to display INTO another variable. Then you might still fail if you have more than one result. So better to use a cursor.

create or replace procedure GETCUSTNAMEZZz
(cust_identifier1 IN NVARCHAR2(10))
is
begin
  for c in (SELECT FNAME || ' ' || LNAME as flname FROM customer WHERE  cust_id=cust_identifier1) 
  loop 
    DBMS_OUTPUT.PUT_LINE (c.flname);
  end loop;
end getcustnamezzz;

Then you can run it with

set serverouput on
BEGIN
  GETCUSTNAMEZZz('C2')  ;  
END;

Edit: It is worth to mention that Dave Costa pointed out that the quotes actually make the procedure name case sensitive but Oracle normally automatically converts everything to uppercase and this was why it also did not work.

hol
  • 8,255
  • 5
  • 33
  • 59
  • Oracle 11g. Creating procedures has been an absolute nightmare.Functions,triggers, joins etc all ok but I've been here for 6 hours trying to get a basic procedure to work. "Error at line 5: PLS-00103: Encountered the symbol ")" when expecting one of the following: . ( * @ % & - + ; / at mod remainder rem return returning and or || multiset 0.08 second" comes up when entering that code. – Daniel o Keeffe May 06 '13 at 20:12
  • Think you forgot he `select` in the cursor 8-) Hopefully a customer ID would be unique, but if it might not be then a cursor might be useful, amd good to know anyway. Also the parameter shouldn't have the `(10)`, you can't restrict the size of a passed string like that. – Alex Poole May 06 '13 at 20:51
  • Thanks a million it actually worked!! Mistakenly believe that as the cust_identifiier had to have the same type nvarchar2(10) as the cust_id. Cheers. – Daniel o Keeffe May 06 '13 at 21:01