How to use excute immediate to execute a procedure?

Posted on 2006-03-28
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
    LVL 9

    Expert Comment

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

    Author Comment

    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

    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

    cheers :)

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now