The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
22.3k9129262
accept rate: 40%

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
22.3k9129262
accept rate: 40%

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:

×4

question asked: 03 Jun '11, 12:13

question was seen: 631 times

last updated: 03 Jun '11, 15:54