Re: Dynamic SQL

For the same reason you can't just type a procedure name at a SQL*Plus prompt; you have to supply a complete anonymous PL/SQL block. Change your string to:

'begin PRC_REGISTER_SAVE; end;'

and you should be all set.

markhath_at_my-dejanews.com wrote:
>
> Does anyone know why the following script errors? If I store a "select"
> statement in the variable l_text it works just fine. Why can't I call a
> procedure here?
>
> Test MED> Declare
> 2 l_text Varchar2(60);
> 3 cur_name INTEGER;
> 4 l_cnt NUMBER;
> 5 l_rc NUMBER;
> 6 BEGIN
> 7 l_text := 'PRC_REGISTER_SAVE';
> 8
> 8 cur_name := dbms_sql.OPEN_CURSOR;
> 9
> 9 dbms_sql.PARSE( cur_name, l_text, dbms_sql.NATIVE );
> 10
> 10 l_cnt := dbms_sql.EXECUTE( cur_name );
> 11
> 11 dbms_sql.CLOSE_CURSOR( cur_name );
> 12
> 12 END;
> 13 /
> Declare
> *
> ERROR at line 1:
> ORA-00900: invalid SQL statement
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at line 9
>
> Test MED>
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Fri May 22 1998 - 14:53:17 CDT