calling varying stored proc

i have to make a call from a stored proc to a stored proc that may or may not exist

like this:

------------------

if exists

(select bla from dba_objects where name like 'spcstored' )

then

exec or call ' spcstored'

end if

-----------------------------------

now this is not so complex but if spcstored

does not exist you cannot compile this stored proc

so you must put the name of the spcstored i want to call

in some kind of variable to exclude the compile error. and then execute it

can anoone solve this problem?

Frans-jan Wagemakers

Why would you compile an inexisting storeproc?
You already know it doesn't exist ; your pseudo-code is pretty clear.
Not sure what's the problem or which one is the question.

the calling procedure does exist , the procedure that is being called may not exist.
it could be a customer specific adapted stored procedure.
some customers have them , some haven.t . that's why this is necessary to create code to automatically redirect the code to this client specific stored proc.
{
proc spd_common

begin
if dba-object 'spc_common' then
exec 'spc_common';
--client spec section
else
--common section

It looks like a text book case for dynamic sql.

If your storeproc is able to figure it out the name of the desired "custom" storedproc you can build a dynamic sql statement on the fly, then execute it resorting to "execute immediate" statement.

thanx for your quick asnwer , what i mean is this:
---------
IF v_spcExist =1 then
--client spefication section
EXECUTE IMMEDIATE 'spc_getsettlements( p_reltype,
p_debid
p_firstchar ,
p_filter,
rct1 IN OUT globalpkg.rct1 )' ;
-----------
execute immediate cannot call to a stored proc as far as i now, only DML
or can it call a function instead?

You can select from a function therefore you can "execute immediate" a function ;)

fantastic this works, now find a way of returning values from the inner procedure.
solution is :
execute immediate 'begin ' || v_broutin || '( :p_credit, :v_employee ,:v_ret); end;'
using in p_creditor, IN p_employee, in out v_sql;

where v_broutin is the name of the stored proc