|
I'm trying to set up a stored procedure that will handle calling another stored procedure that is passed in as a long varchar variable. I've been experimenting with EXECUTE IMMEDIATE, but so far I'm not getting anywhere with it. I feel like I'm missing something simple, but haven't stumbled across it yet. Is there a way to do this or do I need to change the call to use the second stored proc normally? |
|
Here is an example: 1) first create the procedure that is going to be called: create or replace procedure callme( in @v long varchar ) begin message 'callme has been called with length(v) = ' || length(@v); end; 2) now call the "callme" procedure using execute immediate: begin declare @bigv long varchar; set @bigv = repeat( 'x', 10000 ); execute immediate 'call callme( @bigv )'; end; You should see in your database server console the message: callme has been called with length(v) = 10000 You nailed exactly what I was doing wrong. I had forgotten to put the CALL in the string before I tried to execute it. I knew it was something simple I was just not seeing. Thanks for the assist! Working great now!
(03 Jun '11, 15:09)
Darrenes
|
|
If my first sample solution doesn't answer your question, here is a more elaborate example: 1) first create a few procedures that can be called: create or replace procedure callme1( in @v long varchar ) begin message 'callme1 has been called with length(v) = ' || length(@v); end; create or replace procedure callme2( in @v long varchar ) begin message 'callme2 has been called with length(v) = ' || length(@v); end; create or replace procedure callme3( in @v long varchar ) begin message 'callme3 has been called with length(v) = ' || length(@v); end; 2) Create a procedure that is given an integer that selects which procedure is to be called and the value that is to be passed: create or replace procedure call_some_proc( in @i int, in @v long varchar ) begin declare @stmt long varchar; set @stmt = 'call "callme' || @i || '"( @v )'; -- compose call statement execute immediate @stmt; exception when others then message 'Attempt to call proc #' || @i || ' failed: ' || errormsg(); end; 3) now try calling each of the procedures: begin declare @bigv long varchar; set @bigv = repeat( 'x', 10000 ); call call_some_proc( 1, @bigv ); call call_some_proc( 2, @bigv ); call call_some_proc( 3, @bigv ); call call_some_proc( 4, @bigv ); -- this will generate an error end; You should see in your database server console these messages: callme1 has been called with length(v) = 10000 callme2 has been called with length(v) = 10000 callme3 has been called with length(v) = 10000 Attempt to call proc #4 failed: Procedure 'callme4' not found |