Re: dbms_sql and stored procedures

"execute" is an SQL*Plus command, not a PL/SQL command. To do this properly, your v_proc variable should be built like this:

  v_proc := 'begin; ' || c1rec.proc_name || '; end';

RichFord wrote in message <35DB4AF2.AD555C00_at_netcom.com>...
>Anyone try to execute multiple stored procedure within a cursor loop
>using dbms_sql.
>
>i.e.
>
>v_cursor_name NUMBER :=0;
>v_proc VARCHAR2(50);
>
>CURSOR c1 IS
> SELECT proc_name FROM stored_proc
> ORDER BY proc_name;
>
>BEGIN
>
> FOR c1rec IN c1 LOOP
>
> v_proc := 'execute '||c1rec.proc_name;
> dbms_output.put_line ('v_proc ' ||v_proc);
> v_cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(v_cursor_name, v_proc, DBMS_SQL.V7);
> dbms_sql.close_cursor(v_cursor_name);
>
> END LOOP;
>
>I keep getting ORA-00900: invalid SQL statement.
>
>Thanks.
>
>Rich Ford RFDesign.
>
Received on Wed Aug 19 1998 - 20:08:37 CDT