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?

asked 03 Jun '11, 12:13

Darrenes's gravatar image

Darrenes
6113
accept rate: 0%


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
permanent link

answered 03 Jun '11, 13:16

Mark%20Culp's gravatar image

Mark Culp
24.8k9139296
accept rate: 41%

edited 03 Jun '11, 13:37

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
permanent link

answered 03 Jun '11, 13:34

Mark%20Culp's gravatar image

Mark Culp
24.8k9139296
accept rate: 41%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×9

question asked: 03 Jun '11, 12:13

question was seen: 1,789 times

last updated: 03 Jun '11, 15:54