Re: Creating a Stored Procedure in Oracle

From: <markp7832_at_my-dejanews.com>
Date: Thu, 29 Apr 1999 13:38:53 GMT
Message-ID: <7g9ndd$d66$1@nnrp1.dejanews.com>

In article <3727BB4A.22AA2044_at_earthlink.net>,   Sussette Blasa <stblasa_at_earthlink.net> wrote:
> Hello! I've been looking through the newsgroups on how to create a
> stored procedure within Oracle. I just started looking into this
> today...:-) Does anyone know how that works? I know it starts off with
> (?):
>
> CREATE PROCEDURE proc_name (some sort of statement) IS
> BEGIN
> some statments
>
> END;
>
> Thanks--
>
> --Sussette
>

Susan, there are numerous examples in the manuals but here is a database stored procedure that I wrote based on a newsgroup post from around 1995.

This procedure allows users granted the execute priviledge to truncate the owner's tables using dynamic SQL created via the dbms_sql package. I always felt I should add a check against a list of authorized tables to the routine to prevent misuse.

create
procedure TRUNCATE_TABLE
  (
   table_name varchar2,
   storage_type varchar2
  )
as

--
--  procedure to allow truncation of tables owned by this procedure's
--  owner by authorized users of this procedure, ie, everyone granted
--  execute priviledge on this procedure has truncate authority on all
--  of the procedure owner's tables.
--
--  This procedure accepts two parameters: the name of the table to be
--  truncated and instructions on how to handle storage allowcated to
--  the table.
--
--  Example: execute xxx.truncate_table('table_name','storage_option') ;
--    where storage_option = 'drop storage' or 'reuse storage'
--
--                                                       mdp
--
--        DO NOT CREATE A PUBLIC SYNONYM FOR THIS PROCEDURE!

crsor integer;
rval integer;
begin
/* dbms_output.put_line('Truncating table : '|| table_name ||

                     ' Storage : '|| storage_type ) ;  */

crsor := dbms_sql.open_cursor ;
dbms_sql.parse(crsor,'truncate table '|| table_name ||

               ' '|| storage_type , dbms_sql.v7) ; rval := dbms_sql.execute(crsor) ;
dbms_sql.close_cursor(crsor) ;
end ;
/

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 29 1999 - 08:38:53 CDT