calling varying stored proc
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: calling varying stored proc

  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Lightbulb 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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Feb 2009
    Posts
    4

    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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  5. #5
    Join Date
    Feb 2009
    Posts
    4

    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?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  7. #7
    Join Date
    Feb 2009
    Posts
    4

    Talking 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 08: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
  •  



Click Here to Expand Forum to Full Width