Re: Calling PL/SQL procedures via Dynamic SQL

From: Tomm Carr <tommcatt_at_geocities.com>
Date: 1997/07/30
Message-ID: <33DF8C56.7DE7@geocities.com>#1/1

Simon Morley wrote:
>
> Am I missing something obvious? A simple test program to illustrate what
> I am trying to acheive is below.
>
> function do_sql return varchar2 is
> the_result varchar2 (2000) := '';
> c_cursor integer := 0;
> status integer;
>
> begin
> c_cursor := dbms_sql.open_cursor;
> dbms_sql.parse(c_cursor,
> 'begin
> sol2b.display_product_name(''FREE_120'',:the_result);
> end;',
> dbms_sql.v7);
> dbms_sql.bind_variable(c_cursor, 'the_result', the_result);
> status := dbms_sql.execute(c_cursor);
> dbms_sql.close_cursor(c_cursor);
> return the_result;
> end;

Yes, you are missing something and it is fairly obvious when you have done this a time or two. You are missing a couple of steps. To be brief, I will only refer you to the DEFINE_COLUMN and COLUMN_VALUE procedures of the DBMS_SQL package.

However, let's look at this from a different angle. The question that accurs to me is why you are using dynamic SQL to call the stored procedure in the first place. Why wouldn't this work?

  function do_sql return varchar2 is

        the_result varchar2 (2000) := '';   begin

        sol2b.display_product_name('FREE_120', the_result);
        return the_result;

  end;

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"

---- Coming soon to this spot: "I keep hitting the escape key but I'm
still here!"

Received on Wed Jul 30 1997 - 00:00:00 CDT