How to use excute immediate to execute a procedure?

Posted on 2006-03-28
Medium Priority
Last Modified: 2012-06-21
I have a procedure which check whether another procedure is exist, if exists, then use execute immediate to run the procedure.

select count(distinct name) into l_proCount from user_source where type = 'PROCEDURE' and name like '%LC_DELETEMETADATAFORPROCESS%';
      if(l_proCount > 0)then
         execute immediate 'LC_DELETEMETADATAFORPROCESS(2111,1);';
         end if;

But this not works, it complains error in the execute immediate line.

How to solve this? Any suggestions are welcome, thank you.
Question by:c_law
  • 2

Expert Comment

ID: 16313942
Try this line instead:
execute immediate 'LC_DELETEMETADATAFORPROCESS(2111,1)';

Author Comment

ID: 16315533
It's not working as well , but I found the solution from Google, need to wrap the begin end to the procedure when using execute immediate to execute procedure.
LVL 16

Accepted Solution

RCorfman earned 1500 total points
ID: 16318368
Why isn't it working well?  Google is correct as you have to have a good executable sql statement in execute immediate. A stand-alone procedure is not an executable statement all alone, a block is.

Author Comment

ID: 16330688
cheers :)

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question