Error PLS-00222: no function with name 'Generator' exists in this scope

I am calling a stored from within another stored procedure. When I execute the calling stored procedure I get the error message:

Error PLS-00222: no function with name 'Generator' exists in this scope

I don't have a function. My stored procedure is named "Generator". Why does it think I am executing a function?


EXECUTE IMMEDIATE Generator ('tTable',inParam) INTO newVal;
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
"LVL 78" is my EE Level not my screen name.  

If you look at the comments here you see:  Expert Comment  by:slightwv (䄆 Netminder)

slightwv is my screen name.

For me to help further I need to see your actual code.

From what I get from your question is you have one function and one procedure.

Here is my setup:
create or replace function myGenerator(param1 in varchar2, param2 in varchar2) return varchar2 is
begin
	return param2;
end;
/

show errors


create or replace procedure myProcedure(inParam in varchar2) as
	newVal varchar2(100);
begin
	newVal := myGenerator('tTable',inParam);
	dbms_output.put_line('I get: ' || newVal);
end;
/

show errors

exec myProcedure('It works for me');

Open in new window


Here is my output:
SQL> @q

Function created.

No errors.

Procedure created.

No errors.
I get: It works for me

PL/SQL procedure successfully completed.

Open in new window




The question states you have two "procedures".  If you do, a procedure doesn't return a value.  It has OUT parameters.

Here is a similar example using a procedure instead of a function.

create or replace procedure myGenerator(param1 in varchar2, param2 in varchar2, some_out_param out varchar2) as
begin
	some_out_param := param2;
end;
/

show errors


create or replace procedure myProcedure(inParam in varchar2) as
	newVal varchar2(100);
begin
	myGenerator('tTable',inParam,newVal);
	dbms_output.put_line('I get: ' || newVal);
end;
/

show errors

exec myProcedure('It works for me');

Open in new window


The results
SQL> @q

Procedure created.

No errors.

Procedure created.

No errors.
I get: It works for me

PL/SQL procedure successfully completed.

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
execute immediate is used to execute a STRING.  In Oracle you do not execute functions that way.

Also, like in your other questions:  No need for execute immediate.

Try this:
select Generator ('tTable',inParam) INTO newVal from dual;

Since you are inside PL/SQL code it is even easier:
newVal := Generator ('tTable',inParam);

That way you don't switch contexts.
0
 
brgdotnetcontractorAuthor Commented:
Hi LVL 78, thanks for the help. I tried all of the methods you suggested and I am still getting the same error message that "no function with name Generator exists in this scope. Very frustrating.
0
 
brgdotnetcontractorAuthor Commented:
Thank you so much !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.