Re: Oracle WebServer

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/09/06
Message-ID: <322f72f6.1833816_at_dcsun4>#1/1

Here's a stored procedure (at the bottom) that will help you out.

Basically you will code:

create or replace procedure my_web_agent_procedure( p_proc_name in varchar2 ) as

        l_n number;
begin

	htp.p( 'stuff' );
	....
	l_n := execute_immediate( 'begin ' || p_proc_name || '; end;' );
 	....
	htp.p( 'end of stuff' );

end;
/

Execute_immediate will take any insert/update/create/delete/alter/anonymous block/etc type of statement and execute it, returning the number of rows affected if applicable.

Beware: In order to perform operations in a stored procedure, access needs to be granted directly to the owner of the procedure, not to some role the owner of the procedure has. If you get 'unknown procedure' type of errors, make sure you have execute granted directly to you OR you own the procedure.

On Thu, 05 Sep 1996 14:51:40 -0400, Leslie Mittelkamp <llm39_at_prc.erinet.com> wrote:

>I am currently using WebServer2.0 and I want to call a stored procedure that is not
>known at compile time. In other words, the name of the procedure to be executed is
>stored in a procedure. The procedure itself contains all the html stuff to display
>the results, we just need to be able to execute different procedures based on the
>user's selection. Anyone have any ideas?
>
>
>--
>Leslie L. Mittelkamp
>PRC Inc.
>3170 Presidential Drive
>Fairborn, OH 45324-2039
>Phone: 513-429-6209
>Fax: 513-429-1893
>E-Mail: llm39_at_prc.erinet.com, lym29_at_naic.wpafb.af.mil
create or replace
function execute_immediate( stmt in varchar2 ) return number
as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

    return rows_processed;
exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Sep 06 1996 - 00:00:00 CEST