-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
hai ther
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
execute immediate
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
it works
fantastic this works, now find a way of returning values from the inner procedure.
solution is :
execute immediate 'begin ' || v_broutin || '( _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
Last edited by fjwagemakers; 02-23-2009 at 09:46 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|