CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
Daniel o Keeffe
Daniel o Keeffe

Reputation: 578

how to call a plsql procedure

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.

Upvotes: 2

Views: 495

Answers (2)

hol
hol

Reputation: 8423

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.

Upvotes: 2

Dave Costa
Dave Costa

Reputation: 48121

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.)

Upvotes: 8

Related Questions