Re: Executing stored procedures in dynamic SQL

>Hello,
>I want to execute procedures in dynamic SQL. The name of the procedures are
>stored in my application database. Depending on the situation a want to
>execute one of them. It would be nice if I could execute the procedure in
>dynamic SQL and get the results back in my program. Is this possible? Does
>somebody have a sample?
>DJ
>
>

I have not done this, but you might try one of two things... Option 1: (Preferred)
Use the DBMS_SQL package ( as I assume you are). When you build the 'SQL' statement, enclose your procedure call within a begin-ends, like this sql_cmd := 'begin;my_proc;end;'

Obviously, you cannot return any values back.

Option 2: Build your procedures so that they can be called directly in a SQL statement;

select myfunct from dual;

use this for the SQl statement to run. (You will probably need some PRAGMAs in your package declaration). You will probably need to directly place the values in the call; bind variables go with the WHERE clause.

Hope this gets you started on the right track. Dan Hekimian-Williams Received on Mon Jul 06 1998 - 04:18:05 CEST