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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brgdotnetcontractorAuthor Commented:
Thank you so much !!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.