start procedure

[mike778]

I want to write the name of my procedure into a varchar, and then start it.
the problem ist that i dont know the name of the procedure before runtime.

something like this

procname := "newitem";
--new item is name of a possibel procedure.

callprocedure (procname); --call procedure doesnt exist

does anybody have an idea?
thank you,
michael

 

[sem]

You should read about DYNAMIC SQL, namely about EXECUTE IMMEDIATE and DBMS_SQL

Regards, Dima

 

[jad]

you could use dynamic SQL to create 'anonymous PL/SQL blocks'

procedure runplsql (proc in varchar2, val1 in integer, val2 in varchar2) is
  v_cursor_id integer;
  v_Cursor_String varchar2(5000);
  v_ignore integer;
begin
  v_Cursor_id := dbms_sql.open_cursor;
  v_Cursor_String := 'begin
' || proc || '(:val1, :val2);
end;';

  dbms_sql.parse(v_cursor_id, v_cursor_string, dbms_sql.v7);
  dbms_sql.bind_variable(v_cursor_id, ':val1', val1);
  dbms_sql.bind_variable(v_cursor_id, ':val2', val2, length(val2));

  v_Ignore := dbms_sql.dbms_sql.execute(v_Cursor_id);
  dbms_sql.close_cursor(v_cursor_id);
end runplsql;

 

[mike778]

thanks for the fast answers!
I use
execute immediate 'begin ' || proc || '; end;';

greetings,
michael